Looking for the correct excel formula
I have a spreadsheet with the following values. Column A is the item number
and column B is the qty. TAB1 A B 1 Item Number Qty 2 500cc 0 3 1500cc 100 4 1500sp 0 5 3000cc 100 6 16000cc 100 These figures are pulled out of the database when you enter the purchase order. On another tab of the spreadsheet is the packing list. I need a formula that will reference the previous worksheet and ignore the 0 values. So I need the packing list to look like this: TAB2 A B 1 Item Number Qty 2 1500cc 100 3 3000cc 100 4 16000cc 100 5 6 Now I have been using the following formula in cell A2 that reads: =if(tab1!B20,tab1!A2,if(tab1!B30,tab1!A3,if(tab1 B40,tab1!A4,if(tab1!B50,tab1!A5,if(tab1!B60,tab 1!A6,"") Now this formula works great for cell A2 on tab 2. The problem arises for cell A3. If I do the same formula as above, I come up with the same answer as cell A2. I need to have the same formula AND somehow exclude the result from cell A2. In cell A4, I need to have the same formula and exclude the result from cell A2 and A3. And so on and so forth. Anybody have any ideas how I could do this? |
Looking for the correct excel formula
Try this...
On Sheet2... Sheet2 A1:B1 = headers = Item Number, Qty Enter this formula in C1. This will return the count of QTY from Sheet1 that is 0. =COUNTIF(Sheet1!B:B,"0") Enter this array formula** in A2: =IF(ROWS(A$2:A2)C$1,"",INDEX(Sheet1!A:A,SMALL(IF( Sheet1!B$2:B$60,ROW(Sheet1!B$2:B$6)),ROWS(A$2:A2) ))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Assuming that the items are unique... Enter this formula in B2: =IF(A2="","",SUMIF(Sheet1!A:A,A2,Sheet1!B:B)) Select both A2 and B2 and drag copy down until you get blanks. -- Biff Microsoft Excel MVP " .com wrote in message ... I have a spreadsheet with the following values. Column A is the item number and column B is the qty. TAB1 A B 1 Item Number Qty 2 500cc 0 3 1500cc 100 4 1500sp 0 5 3000cc 100 6 16000cc 100 These figures are pulled out of the database when you enter the purchase order. On another tab of the spreadsheet is the packing list. I need a formula that will reference the previous worksheet and ignore the 0 values. So I need the packing list to look like this: TAB2 A B 1 Item Number Qty 2 1500cc 100 3 3000cc 100 4 16000cc 100 5 6 Now I have been using the following formula in cell A2 that reads: =if(tab1!B20,tab1!A2,if(tab1!B30,tab1!A3,if(tab1 B40,tab1!A4,if(tab1!B50,tab1!A5,if(tab1!B60,tab 1!A6,"") Now this formula works great for cell A2 on tab 2. The problem arises for cell A3. If I do the same formula as above, I come up with the same answer as cell A2. I need to have the same formula AND somehow exclude the result from cell A2. In cell A4, I need to have the same formula and exclude the result from cell A2 and A3. And so on and so forth. Anybody have any ideas how I could do this? |
Looking for the correct excel formula
In Sheet 2 cell A1 enter the below formul and copy/drag to cell B1. Then copy
down as required...Please note that this is an array formula.You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =IF(COUNTIF(Sheet1!$B$1:$B$1000,"0")+1<ROW(Sheet1 !A1),"", INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$10 00<0, ROW($B$1:$B$1000)),ROW(Sheet1!A1)))) If this post helps click Yes --------------- Jacob Skaria " wrote: I have a spreadsheet with the following values. Column A is the item number and column B is the qty. TAB1 A B 1 Item Number Qty 2 500cc 0 3 1500cc 100 4 1500sp 0 5 3000cc 100 6 16000cc 100 These figures are pulled out of the database when you enter the purchase order. On another tab of the spreadsheet is the packing list. I need a formula that will reference the previous worksheet and ignore the 0 values. So I need the packing list to look like this: TAB2 A B 1 Item Number Qty 2 1500cc 100 3 3000cc 100 4 16000cc 100 5 6 Now I have been using the following formula in cell A2 that reads: =if(tab1!B20,tab1!A2,if(tab1!B30,tab1!A3,if(tab1 B40,tab1!A4,if(tab1!B50,tab1!A5,if(tab1!B60,tab 1!A6,"") Now this formula works great for cell A2 on tab 2. The problem arises for cell A3. If I do the same formula as above, I come up with the same answer as cell A2. I need to have the same formula AND somehow exclude the result from cell A2. In cell A4, I need to have the same formula and exclude the result from cell A2 and A3. And so on and so forth. Anybody have any ideas how I could do this? |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com