Populate multiple cells off one equation

K

Kevin Wallace

I have created a drop-down list for parts in "A1". I need to populate the
cost price into "G1" and, the retail price into "J1" for whatever value is
chosen. I have created a list with the prices off to the side that I can
hide later. So I'm trying to create a formula that will populate the cells
at G1 and J1 based on the choice in A1. I have many part numbers and about
20 rows that I need to put the same formulas into.

If A1="X73495" then G1=O23 and J1=P23
If A1="C75639" then G1=O24 and J1=P24
If A1="P38427" then G1=O25 and J1=P25
 
D

David

Hi,
Take a look at VLookup and you should be able to populate both Price and Cost.
Thanks,
 
S

Stevie_mac

Make a table of 3 columnsPartNo,
Cost,
Retail



Populate the Table

Select the full table & name it COST_TABLE

Put in CELL G1...
=VLOOKUP(A1,COST_TABLE,2,FALSE)

Put in CELL G1...
=VLOOKUP(A1,COST_TABLE,3,FALSE)

What it does...
When you change A1, VLOOKUP looks for the value of A1 in COST_TABLE then returns the matching item from COL2 or COL3 of
your table.


Hope it helps - Steve.
 
K

Kevin Wallace

Stevie... The only problem I'm having now is that if the Part# field is blank
it puts "N/A" in the price fields. Is there a way to make it so if the Part
# is not yet filled in that it will just leave "0.00"?
 
S

Stevie_mac

Kevin Wallace said:
Stevie... The only problem I'm having now is that if the Part# field is blank
it puts "N/A" in the price fields. Is there a way to make it so if the Part
# is not yet filled in that it will just leave "0.00"?
 
S

Stevie_mac

Woops on the 1st reply!

You can check to see if the value is a number

E.G.
=IF(ISNUMBER(VLOOKUP(A1,COST_TABLE,2,FALSE)),VLOOKUP(A1,COST_TABLE,2,FALSE),0)

But this performs the lookup twice. There is probability a better way.
 
K

Kevin Wallace

Stevie... iIt appears your response to my last question did not take and just
gave us a space. I've been trying different things and can't seem to come up
with anything that will leave the price columns blank, or show "0.00".
Thanks in advance for your help.
 
K

Kevin Wallace

Thanks Stevie... Works like a charm...

Stevie_mac said:
Woops on the 1st reply!

You can check to see if the value is a number

E.G.
=IF(ISNUMBER(VLOOKUP(A1,COST_TABLE,2,FALSE)),VLOOKUP(A1,COST_TABLE,2,FALSE),0)

But this performs the lookup twice. There is probability a better way.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top