| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell w/ drop down then reference
K, I have a drop down box in "Tracking" workbook B3 with "Part Numbers".
When I choose one of the part numbers from that drop down box I want C3 ("Current Balance") to update with a value from my "Inventory" workbook. "Inventory" workbook only contains column A: "Part Number" & Column B:"Qty in Stock" |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
cell w/ drop down then reference
Hi,
Try something like =VLOOKUP(B3,Invertory!A1:B1000,2,False) This example assumes we are refering to the worksheet Inventory not the workbook, but it will work with a workbook also. In that case open both files and with your cursor in C3 of Tracking type =VLOOKUP(B3, and then click in the other workbook, and on the correct sheet, and then highlight the necessary range and complete the formula as above. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Jackie" wrote: K, I have a drop down box in "Tracking" workbook B3 with "Part Numbers". When I choose one of the part numbers from that drop down box I want C3 ("Current Balance") to update with a value from my "Inventory" workbook. "Inventory" workbook only contains column A: "Part Number" & Column B:"Qty in Stock" |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
cell w/ drop down then reference
In "Tracking" workbook C3 try the below formula
=index(Inventory!B:B,match(B3,Inventory!A:A,0)) If this post helps click Yes --------------- Jacob Skaria "Jackie" wrote: K, I have a drop down box in "Tracking" workbook B3 with "Part Numbers". When I choose one of the part numbers from that drop down box I want C3 ("Current Balance") to update with a value from my "Inventory" workbook. "Inventory" workbook only contains column A: "Part Number" & Column B:"Qty in Stock" |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
cell w/ drop down then reference
k, that works but when there's nothing in B3 ("Part Number") then C3
("Current Balance") is giving me a #N/A error which I understand why but can i make that be blank when "part number" is blank? "Shane Devenshire" wrote: Hi, Try something like =VLOOKUP(B3,Invertory!A1:B1000,2,False) This example assumes we are refering to the worksheet Inventory not the workbook, but it will work with a workbook also. In that case open both files and with your cursor in C3 of Tracking type =VLOOKUP(B3, and then click in the other workbook, and on the correct sheet, and then highlight the necessary range and complete the formula as above. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Jackie" wrote: K, I have a drop down box in "Tracking" workbook B3 with "Part Numbers". When I choose one of the part numbers from that drop down box I want C3 ("Current Balance") to update with a value from my "Inventory" workbook. "Inventory" workbook only contains column A: "Part Number" & Column B:"Qty in Stock" |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
cell w/ drop down then reference
Error handled version
=IF(ISERROR(match(B3,Inventory!A:A,0)),"",index(In ventory!B:B,match(B3,Inventory!A:A,0))) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: In "Tracking" workbook C3 try the below formula =index(Inventory!B:B,match(B3,Inventory!A:A,0)) If this post helps click Yes --------------- Jacob Skaria "Jackie" wrote: K, I have a drop down box in "Tracking" workbook B3 with "Part Numbers". When I choose one of the part numbers from that drop down box I want C3 ("Current Balance") to update with a value from my "Inventory" workbook. "Inventory" workbook only contains column A: "Part Number" & Column B:"Qty in Stock" |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
cell w/ drop down then reference
Try one of these:
=IF(B3="","",VLOOKUP(B3,Invertory!A1:B1000,2,0)) Or, this more generic version: =IF(ISNA(VLOOKUP(B3,Invertory!A1:B1000,2,0)),"",VL OOKUP(B3,Invertory!A1:B1000,2,0)) -- Biff Microsoft Excel MVP "Jackie" wrote in message ... k, that works but when there's nothing in B3 ("Part Number") then C3 ("Current Balance") is giving me a #N/A error which I understand why but can i make that be blank when "part number" is blank? "Shane Devenshire" wrote: Hi, Try something like =VLOOKUP(B3,Invertory!A1:B1000,2,False) This example assumes we are refering to the worksheet Inventory not the workbook, but it will work with a workbook also. In that case open both files and with your cursor in C3 of Tracking type =VLOOKUP(B3, and then click in the other workbook, and on the correct sheet, and then highlight the necessary range and complete the formula as above. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Jackie" wrote: K, I have a drop down box in "Tracking" workbook B3 with "Part Numbers". When I choose one of the part numbers from that drop down box I want C3 ("Current Balance") to update with a value from my "Inventory" workbook. "Inventory" workbook only contains column A: "Part Number" & Column B:"Qty in Stock" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return the cell reference of the drop down list selection. | Excel Worksheet Functions | |||
| When using a drop down to get it to reference another cell | Excel Discussion (Misc queries) | |||
| Drop down list - need to reference and populate another cell with | Excel Discussion (Misc queries) | |||
| How to reference a cell in a different workbook using a drop down | Excel Discussion (Misc queries) | |||
| Place a set value in a cell from a drop down list name reference | Excel Worksheet Functions | |||
Linear Mode
