| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate multiple cells off one equation
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Populate multiple cells off one equation
Hi,
Take a look at VLookup and you should be able to populate both Price and Cost. Thanks, "Kevin Wallace" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Populate multiple cells off one equation
Thanks a million... this did exactly as I was hoping
"Stevie_mac" wrote: 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. "Kevin Wallace" <Kevin wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Populate multiple cells off one equation
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"? "Stevie_mac" wrote: 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. "Kevin Wallace" <Kevin wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Populate multiple cells off one equation
"Kevin Wallace" wrote in message ... 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"? "Stevie_mac" wrote: 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. "Kevin Wallace" <Kevin wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Populate multiple cells off one equation
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)),VLOOK UP(A1,COST_TABLE,2,FALSE),0) But this performs the lookup twice. There is probability a better way. "Kevin Wallace" wrote in message ... 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"? "Stevie_mac" wrote: 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. "Kevin Wallace" <Kevin wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Populate multiple cells off one equation
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. "Stevie_mac" wrote: "Kevin Wallace" wrote in message ... 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"? "Stevie_mac" wrote: 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. "Kevin Wallace" <Kevin wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Populate multiple cells off one equation
Thanks Stevie... Works like a charm...
"Stevie_mac" wrote: 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)),VLOOK UP(A1,COST_TABLE,2,FALSE),0) But this performs the lookup twice. There is probability a better way. "Kevin Wallace" wrote in message ... 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"? "Stevie_mac" wrote: 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. "Kevin Wallace" <Kevin wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Populate Multiple cells when choosing one selection from drop down | Excel Discussion (Misc queries) | |||
| Search multiple cells with conditions, sum and auto populate! | Excel Worksheet Functions | |||
| Creating a formula to populate information from multiple cells in another workbook | Excel Worksheet Functions | |||
| Populate multiple cells using the same reference | Excel Worksheet Functions | |||
| need help with an equation in worksheet to populate numbers onto c | Excel Worksheet Functions | |||
Linear Mode
