Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Inventory with items on order
I am trying to total numbers from two different cells. Cells A1:B10 are a
part number and quantity. This gives me inventory totals. Right now I have the B1:B10 cells setup to pull the numbers from 'Sheet2'!B2 - B10, which works fine, but I have a list of items that I have on order and their quantities in cells E1:F10. What I want to do is to say if A1 appears in the array E1:F10 then to add F1 and 'Sheet2'!B1, but when I do my formula I get back a "#n/a" answer in my cell if the value or part number in my a column does not appear in my E column. This is my current equation which works if I have the part number in the E column, but gives "#n/a" if I don't: =VLOOKUP(A1,$E$1:$F$10,2)+'Sheet2'!B1 If I don't have the number listed on my order list I want the cell to equal 'Sheet2'!B1 and if that equals zero, then I want the cell to be blank. I'm not sure if I need to do a Search function for A1 and use a boolean or if the vlookup is the proper function. Any advice would be much appreciated. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Inventory with items on order
=if(isna(VLOOKUP(A1,$E$1:$F$10,2)),0,VLOOKUP(A1,$E $1:$F$10,2))+'Sheet2'!B1 "Soulscream" wrote: I am trying to total numbers from two different cells. Cells A1:B10 are a part number and quantity. This gives me inventory totals. Right now I have the B1:B10 cells setup to pull the numbers from 'Sheet2'!B2 - B10, which works fine, but I have a list of items that I have on order and their quantities in cells E1:F10. What I want to do is to say if A1 appears in the array E1:F10 then to add F1 and 'Sheet2'!B1, but when I do my formula I get back a "#n/a" answer in my cell if the value or part number in my a column does not appear in my E column. This is my current equation which works if I have the part number in the E column, but gives "#n/a" if I don't: =VLOOKUP(A1,$E$1:$F$10,2)+'Sheet2'!B1 If I don't have the number listed on my order list I want the cell to equal 'Sheet2'!B1 and if that equals zero, then I want the cell to be blank. I'm not sure if I need to do a Search function for A1 and use a boolean or if the vlookup is the proper function. Any advice would be much appreciated. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Inventory with items on order
One guess
In Sheet1, Try in G1: =IF(E1="","",IF(COUNTIF(A:A,E1),SUM(F1,Sheet2!B1), IF(Sheet2!B1="","",Sheet2!B1))) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Soulscream" wrote: I am trying to total numbers from two different cells. Cells A1:B10 are a part number and quantity. This gives me inventory totals. Right now I have the B1:B10 cells setup to pull the numbers from 'Sheet2'!B2 - B10, which works fine, but I have a list of items that I have on order and their quantities in cells E1:F10. What I want to do is to say if A1 appears in the array E1:F10 then to add F1 and 'Sheet2'!B1, but when I do my formula I get back a "#n/a" answer in my cell if the value or part number in my a column does not appear in my E column. This is my current equation which works if I have the part number in the E column, but gives "#n/a" if I don't: =VLOOKUP(A1,$E$1:$F$10,2)+'Sheet2'!B1 If I don't have the number listed on my order list I want the cell to equal 'Sheet2'!B1 and if that equals zero, then I want the cell to be blank. I'm not sure if I need to do a Search function for A1 and use a boolean or if the vlookup is the proper function. Any advice would be much appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Order form linked to inventory | Excel Discussion (Misc queries) | |||
How do I set up an inventory control worksheet to show items left | Setting up and Configuration of Excel | |||
Tracking inventory order history | Excel Discussion (Misc queries) | |||
order form with multiple items and sizes for items | Excel Discussion (Misc queries) | |||
where can I find a sample home inventory that list all items i ma. | Excel Discussion (Misc queries) |