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 ColD7:D75
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

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

"Sean" wrote in message
ups.com...
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 ColD7:D75
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

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


On 8 Jan 2007 14:03:46 -0800, "Sean" wrote:

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 ColD7:D75
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

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


T. Valko wrote:

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

"Sean" wrote in message
ups.com...
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 ColD7:D75
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

hi Sean,

use the Vlookup function to achieve it

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Sean" escreveu:

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 ColD7:D75
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