Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting more than 7 IF statements (Excel 2003)
I have an inventory worksheet with many rows, where column D is a manual
entry of item length, column E is a manual entry of item width, and column G is the weight of that size item. The weights for each inventory item are stored in a table on a separate worksheet, with column A listing lenghts and row 3 listing the widths. Since IF statements are limited to 7, how else can I have column G automatically populated with the correct weight, based on what is manually entered in columns D & E? ie: If length (col D) is 8, and width (col E) is 2, then go look at the table and return the value at the intersection of length 8 and width 2. I would then need to copy that formula all the way down my inventory sheet. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting more than 7 IF statements (Excel 2003)
Dee wrote:
I have an inventory worksheet with many rows, where column D is a manual entry of item length, column E is a manual entry of item width, and column G is the weight of that size item. The weights for each inventory item are stored in a table on a separate worksheet, with column A listing lenghts and row 3 listing the widths. Since IF statements are limited to 7, how else can I have column G automatically populated with the correct weight, based on what is manually entered in columns D & E? ie: If length (col D) is 8, and width (col E) is 2, then go look at the table and return the value at the intersection of length 8 and width 2. I would then need to copy that formula all the way down my inventory sheet. http://www.contextures.com/xlFunctio...ml#IndexMatch2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting more than 7 IF statements (Excel 2003)
Use an INDEX(table,MATCH(..row..),MATCH(..column..)) construct. Give
some further details and I could complete the formula for you. Hope this helps. Pete On Aug 14, 2:55*pm, Dee wrote: I have an inventory worksheet with many rows, where column D is a manual entry of item length, column E is a manual entry of item width, and column G is the weight of that size item. *The weights for each inventory item are stored in a table on a separate worksheet, with column A listing lenghts and row 3 listing the widths. *Since IF statements are limited to 7, how else can I have column G automatically populated with the correct weight, based on what is manually entered in columns D & E? *ie: *If length (col D) is 8, and width (col E) is 2, then go look at the table and return the value at the intersection of length 8 and width 2. *I would then need to copy that formula all the way down my inventory sheet. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting more than 7 IF statements (Excel 2003)
Pete,
Thanks for the offer of help- I'm so frustrated with this now. I've got the formula exactly as it should be, but my result continues to be #N/A, even after I convert any text into numbers. To be more specific: My table looks like this - Column A is lengths, Row 1 is widths A B C D 1 3 1/2 3 5/8 3 3/4 2 1.6 3.34 3.37 3.41 3 1.7 3.52 3.56 3.60 4 1.8 3.71 3.75 3.78 My inventory sheet will list the width and length as follows: Item Width Length Weight 100abc 3 5/8 1.8 ??? should be 3.75 200def 3 3/4 1.6 ??? should be 3.41 I'm looking for a formula for the weight column on my inventory sheet that populates from the table based on the width and length entered on the inventory sheet. Thanks for any help you can provide. Excel Help is really good for someone like myself who is a beginner at this. Dee "Pete_UK" wrote: Use an INDEX(table,MATCH(..row..),MATCH(..column..)) construct. Give some further details and I could complete the formula for you. Hope this helps. Pete On Aug 14, 2:55 pm, Dee wrote: I have an inventory worksheet with many rows, where column D is a manual entry of item length, column E is a manual entry of item width, and column G is the weight of that size item. The weights for each inventory item are stored in a table on a separate worksheet, with column A listing lenghts and row 3 listing the widths. Since IF statements are limited to 7, how else can I have column G automatically populated with the correct weight, based on what is manually entered in columns D & E? ie: If length (col D) is 8, and width (col E) is 2, then go look at the table and return the value at the intersection of length 8 and width 2. I would then need to copy that formula all the way down my inventory sheet. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting more than 7 IF statements (Excel 2003)
What formula do you have right now that is "exactly as it should be" but is
giving the wrong result? Dee wrote: Pete, Thanks for the offer of help- I'm so frustrated with this now. I've got the formula exactly as it should be, but my result continues to be #N/A, even after I convert any text into numbers. To be more specific: My table looks like this - Column A is lengths, Row 1 is widths A B C D 1 3 1/2 3 5/8 3 3/4 2 1.6 3.34 3.37 3.41 3 1.7 3.52 3.56 3.60 4 1.8 3.71 3.75 3.78 My inventory sheet will list the width and length as follows: Item Width Length Weight 100abc 3 5/8 1.8 ??? should be 3.75 200def 3 3/4 1.6 ??? should be 3.41 I'm looking for a formula for the weight column on my inventory sheet that populates from the table based on the width and length entered on the inventory sheet. Thanks for any help you can provide. Excel Help is really good for someone like myself who is a beginner at this. Dee "Pete_UK" wrote: Use an INDEX(table,MATCH(..row..),MATCH(..column..)) construct. Give some further details and I could complete the formula for you. Hope this helps. Pete On Aug 14, 2:55 pm, Dee wrote: I have an inventory worksheet with many rows, where column D is a manual entry of item length, column E is a manual entry of item width, and column G is the weight of that size item. The weights for each inventory item are stored in a table on a separate worksheet, with column A listing lenghts and row 3 listing the widths. Since IF statements are limited to 7, how else can I have column G automatically populated with the correct weight, based on what is manually entered in columns D & E? ie: If length (col D) is 8, and width (col E) is 2, then go look at the table and return the value at the intersection of length 8 and width 2. I would then need to copy that formula all the way down my inventory sheet. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting more than 7 IF statements (Excel 2003)
Okay, assuming that your table is in Sheet1 laid out as shown, and
that your width is in B2 and Length in C2 of the Inventory sheet, then put this in D2: =INDEX(Sheet1!$B$2:$D$4,MATCH(C2,Sheet1!$A$2:$A$4, 0),MATCH(B2,Sheet1!$B $1:$D$1,0)) (All one formula - be wary of spurious line breaks that the newsreader might introduce). This suits the data you have shown, but if you have a larger table in Sheet1 you will need to adjust the cell references to suit. You can copy the formula down column D of the Inventory sheet if you need to. Hope this helps. Pete On Aug 14, 8:16*pm, Dee wrote: Pete, Thanks for the offer of help- I'm so frustrated with this now. *I've got the formula exactly as it should be, but my result continues to be #N/A, even after I convert any text into numbers. To be more specific: My table looks like this - Column A is lengths, Row 1 is widths * * * A * * * * * B * * * * * * *C * * * * * * *D 1 * * * 3 1/2 * 3 5/8 * 3 3/4 2 * 1.6 3.34 * *3.37 * *3.41 3 * 1.7 3.52 * *3.56 * *3.60 4 *1.8 *3.71 * *3.75 * *3.78 My inventory sheet will list the width and length as follows: Item * * * * * * * * * Width * * * * * * *Length * * * * * * * *Weight 100abc * * * * * * * * 3 5/8 * * * * * * * 1.8 * * * * * * * * * * *??? * * * * * * *should be 3.75 200def * * * * * * * * *3 3/4 * * * * * * * 1.6 * * * * * * * * * * *??? * * * * * * should be 3.41 I'm looking for a formula for the weight column on my inventory sheet that populates from the table based on the width and length entered on the inventory sheet. Thanks for any help you can provide. *Excel Help is really good for someone like myself who is a beginner at this. Dee "Pete_UK" wrote: Use an INDEX(table,MATCH(..row..),MATCH(..column..)) construct. Give some further details and I could complete the formula for you. Hope this helps. Pete On Aug 14, 2:55 pm, Dee wrote: I have an inventory worksheet with many rows, where column D is a manual entry of item length, column E is a manual entry of item width, and column G is the weight of that size item. *The weights for each inventory item are stored in a table on a separate worksheet, with column A listing lenghts and row 3 listing the widths. *Since IF statements are limited to 7, how else can I have column G automatically populated with the correct weight, based on what is manually entered in columns D & E? *ie: *If length (col D) is 8, and width (col E) is 2, then go look at the table and return the value at the intersection of length 8 and width 2. *I would then need to copy that formula all the way down my inventory sheet.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting more than 7 IF statements (Excel 2003)
Pete & Glenn,
Thank you both very much for your help. The formula is now working, and is saving me a whole lot of time..!! Dee "Pete_UK" wrote: Okay, assuming that your table is in Sheet1 laid out as shown, and that your width is in B2 and Length in C2 of the Inventory sheet, then put this in D2: =INDEX(Sheet1!$B$2:$D$4,MATCH(C2,Sheet1!$A$2:$A$4, 0),MATCH(B2,Sheet1!$B $1:$D$1,0)) (All one formula - be wary of spurious line breaks that the newsreader might introduce). This suits the data you have shown, but if you have a larger table in Sheet1 you will need to adjust the cell references to suit. You can copy the formula down column D of the Inventory sheet if you need to. Hope this helps. Pete On Aug 14, 8:16 pm, Dee wrote: Pete, Thanks for the offer of help- I'm so frustrated with this now. I've got the formula exactly as it should be, but my result continues to be #N/A, even after I convert any text into numbers. To be more specific: My table looks like this - Column A is lengths, Row 1 is widths A B C D 1 3 1/2 3 5/8 3 3/4 2 1.6 3.34 3.37 3.41 3 1.7 3.52 3.56 3.60 4 1.8 3.71 3.75 3.78 My inventory sheet will list the width and length as follows: Item Width Length Weight 100abc 3 5/8 1.8 ??? should be 3.75 200def 3 3/4 1.6 ??? should be 3.41 I'm looking for a formula for the weight column on my inventory sheet that populates from the table based on the width and length entered on the inventory sheet. Thanks for any help you can provide. Excel Help is really good for someone like myself who is a beginner at this. Dee "Pete_UK" wrote: Use an INDEX(table,MATCH(..row..),MATCH(..column..)) construct. Give some further details and I could complete the formula for you. Hope this helps. Pete On Aug 14, 2:55 pm, Dee wrote: I have an inventory worksheet with many rows, where column D is a manual entry of item length, column E is a manual entry of item width, and column G is the weight of that size item. The weights for each inventory item are stored in a table on a separate worksheet, with column A listing lenghts and row 3 listing the widths. Since IF statements are limited to 7, how else can I have column G automatically populated with the correct weight, based on what is manually entered in columns D & E? ie: If length (col D) is 8, and width (col E) is 2, then go look at the table and return the value at the intersection of length 8 and width 2. I would then need to copy that formula all the way down my inventory sheet.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting more than 7 IF statements (Excel 2003)
Glad to hear that, Dee - thanks for feeding back.
Pete On Aug 20, 1:58*pm, Dee wrote: Pete & Glenn, Thank you both very much for your help. *The formula is now working, and is saving me a whole lot of time..!! * Dee "Pete_UK" wrote: Okay, assuming that your table is in Sheet1 laid out as shown, and that your width is in B2 and Length in C2 of the Inventory sheet, then put this in D2: =INDEX(Sheet1!$B$2:$D$4,MATCH(C2,Sheet1!$A$2:$A$4, 0),MATCH(B2,Sheet1!$B $1:$D$1,0)) (All one formula - be wary of spurious line breaks that the newsreader might introduce). This suits the data you have shown, but if you have a larger table in Sheet1 you will need to adjust the cell references to suit. You can copy the formula down column D of the Inventory sheet if you need to. Hope this helps. Pete On Aug 14, 8:16 pm, Dee wrote: Pete, Thanks for the offer of help- I'm so frustrated with this now. *I've got the formula exactly as it should be, but my result continues to be #N/A, even after I convert any text into numbers. To be more specific: My table looks like this - Column A is lengths, Row 1 is widths * * * A * * * * * B * * * * * * *C * * * * * * *D 1 * * * 3 1/2 * 3 5/8 * 3 3/4 2 * 1.6 3.34 * *3.37 * *3.41 3 * 1.7 3.52 * *3.56 * *3.60 4 *1.8 *3.71 * *3.75 * *3.78 My inventory sheet will list the width and length as follows: Item * * * * * * * * * Width * * * * * * *Length * * * * * * * *Weight 100abc * * * * * * * * 3 5/8 * * * * * * * 1.8 * * * * * * * * * * *??? * * * * * * *should be 3.75 200def * * * * * * * * *3 3/4 * * * * * * * 1.6 * * * * * * * * * * *??? * * * * * * should be 3.41 I'm looking for a formula for the weight column on my inventory sheet that populates from the table based on the width and length entered on the inventory sheet. Thanks for any help you can provide. *Excel Help is really good for someone like myself who is a beginner at this. Dee "Pete_UK" wrote: Use an INDEX(table,MATCH(..row..),MATCH(..column..)) construct. Give some further details and I could complete the formula for you. Hope this helps. Pete On Aug 14, 2:55 pm, Dee wrote: I have an inventory worksheet with many rows, where column D is a manual entry of item length, column E is a manual entry of item width, and column G is the weight of that size item. *The weights for each inventory item are stored in a table on a separate worksheet, with column A listing lenghts and row 3 listing the widths. *Since IF statements are limited to 7, how else can I have column G automatically populated with the correct weight, based on what is manually entered in columns D & E? *ie: *If length (col D) is 8, and width (col E) is 2, then go look at the table and return the value at the intersection of length 8 and width 2. *I would then need to copy that formula all the way down my inventory sheet.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting conditional statements | Excel Discussion (Misc queries) | |||
Nesting 7 statements | Excel Discussion (Misc queries) | |||
Nesting IF statements | Excel Discussion (Misc queries) | |||
nesting if statements | Excel Worksheet Functions | |||
Nesting more than nine IF statements in Excel | Excel Worksheet Functions |