Lookup, Match, something like this

[Sean]

I have a list of values in 3 columns (on Sheet), Revenue, Cost and
Profit each Relates to a Location

Locations are in ColA7:A75
Revenues are in ColC7:C75
Costs are in ColD775
Profits are in ColE7:E75

What I wish to achieve is that when a user Selects a location (on
Sheet2), the appropriate values for Revenue, Cost and Profit relating
to the location selected will appear on Sheet2 in ColC, D and E

THanks for any help

 

[T. Valko]

How does a user select a location? A drop down list/combo box?

I'm assuming all locations are unique?

You should be able to do this using a simple Sumif:

=SUMIF($A$2:$A$10,$A1,C$2:C$10)

Where A1 = user selected location

Copy across a total of 3 cells.

Biff

 

[Gord Dibben]

Good candidate for VLOOKUP table A7:E75

Use Data Validation for in-cell drop-down for selecting a location and VLOOKUP
for the filling-in part.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

 

[Sean]

Thanks, yes I've setup a drop down (so that there is no typo) , and all
locations are unique