Solved

Excel dropbox based on table

Posted on 2013-11-20
2
230 Views
Last Modified: 2013-11-20
I am trying to figure out how I can create a dropdown box in the Account Column of the Bank_Template worksheet (see file attached) based on the information in the ChartofAccounts worksheet, the dropdown box should display the Name (Column C) in the Chart of Account but return the actual Code (column A) in the Bank_Template worksheet...

Also, when the user selects the account I need for Column F of the Bank_Template to automatically populate with the correspondent Tax Code (Column E) of the ChartofAccounts worksheet.

I am not sure how I can accomplish this... attached is a template with a sample data of what I am trying to accomplish.
Template.xlsx
0
Comment
Question by:joeserrone
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39663787
You can't do that with Data Validation, you would require VBA...

I would suggest the easiest way would be to insert a new column after the Account Column that populates with the correct code after selection made in Account.

So first, in the Chart Of Accounts, select all the names in column C and name that range by typing a name in the Name Box to the left of the Formula Bar., say, Names

Then in the Template sheet, C2, go to Data Validation, choose List and enter =Names

You can copy that down.

Now in new column, in D2, enter formula:

=IF(C2="","",INDEX(ChartOFAccounts!$A$2:$A$3,MATCH(C2,Names,0)))

copied down

and in the GST class, similar formula

=IF(C2="","",INDEX(ChartOFAccounts!$E$2:$E$3,MATCH(C2,Names,0)))

copied down

adjust ranges to suit.
Copy-of-Template.xlsx
0
 

Author Comment

by:joeserrone
ID: 39664651
Great advice! I really like this approach...
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question