Using Excel dropdown to determine which values to display

Hi,

See attached,
I need to produce identical financial statements for 3 different locations of a particualar company.

I want to use a dropdown which will display the 3 locations (London, Paris , New York).

How do I change the values displayed - as a result of changing the location chosen.

See attached.
DropDownEE1.xlsx
Patrick O'DeaAsked:
Who is Participating?
 
Steven HarrisConnect With a Mentor PresidentCommented:
As long as it setup correctly, the VLOOKUP robhenson suggested will be just as 'robust' as any other.  If you wanted to move away from the formula, you would need to go with VBA.
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
In C3 put this formula and then copy down for the other two:

=VLOOKUP($B3,MasterSheet!$A$1:$D$4,MATCH($C$2,MasterSheet!$A$1:$D$1,0),FALSE)

Thanks
Rob H
0
 
Patrick O'DeaAuthor Commented:
Thanks Rob,

I am not a great fan of Vlookup (which may or may not be a sensible opinion).

In the real world my requirement will involve data sheets that may have about 3,000 rows and I need to feel 100% confident of a robust solution.

Any alternatives to VLookup ?
Perhaps I should just stick with it?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Patrick O'DeaAuthor Commented:
Thanks!
0
 
Rob HensonFinance AnalystCommented:
The only other option I can think of would be use of the DFunctions, the syntax for those is, for example DGET:

=DGET(Database,Field,Criteria)

The Database would be the whole dataset, in example had 4 columns with headers and 3 rows of data.

Field would be city dropdown choice, works in this scenario as you have the City as a column header.

Criteria, this would need a little bit of work as your column with Sales etc didn't have a header.

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
Example of dsum attached
Copy-of-DropDownEE1.xlsx
0
All Courses

From novice to tech pro — start learning today.