Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need second answer to question posted yesterday (niek Otten)
Ok that worked sweet. thanks u r the daddy.
what would happen if i had 6 different price tables in the same format as the one shown. In sht one i want another col to state whate price table to look at like table A,B,C....... so i put in width and lenght + price group this might be different for each row. can this be done or am i just a pain in the B..T cheers jimE "daddylonglegs" wrote: Hello Jim, Looks like you want to round up to the next width or length shown, try this If your table is in sheet2 with widths in B1:J1 and lengths in A2:A10 and prices in B2:J10 then in sheet1 with a specific width in A1 (e.g. 26 in your example) and a specific length in B1 then use this formula in C1 =INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$1 0)+(LOOKUP(B1,Sheet2!$A$2:$A$10)<B1),MATCH(A1,She et2!$B$1:$J$1)+(LOOKUP(A1,Sheet2!$B$1:$J$1)<A1)) "jimE" wrote: I have a work sheet with prices on it. Across the top are widths down the side is length. 24 30 36 ............................120 24 $1.00 $2.00 $3.33 30 2.20 3.20 4.20 . . 120 On another work sheet i have a width and length cells when i enter sizes i want it to return price. EG width might be 26 and length maybe 27 i want to return price of 3.20. does anyone know how to do this. i new to excel so keep it simply please if possible "Niek Otten" wrote: |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need second answer to question posted yesterday (niek Otten)
This was the formula that Daddylonglegs gave you:
=INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$1 0)+(LOOKUP(B1,Sheet2! $A$2*:$A$10)<B1),MATCH(A1,Sheet2!$B$1:$J$1)+(LOOK UP(A1,Sheet2!$B$1:$J $1)<A1)) This assumes that your width is in A1 and your length is in B1 of Sheet1. If you now want to put the sheet name in C1, you can modify the formula like this and put it in D1: =INDEX(INDIRECT("'"&C1&"'!$B$2:$J$10"),MATCH(B1,IN DIRECT("'"&C1&"'!$A $2:$A$10"))+(LOOKUP(B1,INDIRECT("'"&C1&"'!$A$2*:$A $10"))<B1),MATCH(A1,INDIRECT("'"&C1&"'!$B$1:$J$1" ))+ (LOOKUP(A1,INDIRECT("'"&C1&"'!$B$1:$J$1"))<A1)) Note that after each INDIRECT( there is a <quotes<apostrophe<quotes and before each exclamation mark there is a <quotes<apostrophe - this will handle sheetnames in C1 which have spaces in them. Hope this helps. Pete On Oct 23, 11:36 pm, jimE wrote: Ok that worked sweet. thanks u r the daddy. what would happen if i had 6 different price tables in the same format as the one shown. In sht one i want another col to state whate price table to look at like table A,B,C....... so i put in width and lenght + price group this might be different for each row. can this be done or am i just a pain in the B..T cheers jimE "daddylonglegs" wrote: Hello Jim, Looks like you want to round up to the next width or length shown, try this If your table is in sheet2 with widths in B1:J1 and lengths in A2:A10 and prices in B2:J10 then in sheet1 with a specific width in A1 (e.g. 26 in your example) and a specific length in B1 then use this formula in C1 =INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$1 0)+(LOOKUP(B1,Sheet2!$A$2*:$A$10)<B1),MATCH(A1,Sh eet2!$B$1:$J$1)+(LOOKUP(A1,Sheet2!$B$1:$J$1)<A1)) "jimE" wrote: I have a work sheet with prices on it. Across the top are widths down the side is length. 24 30 36 ............................120 24 $1.00 $2.00 $3.33 30 2.20 3.20 4.20 . . 120 On another work sheet i have a width and length cells when i enter sizes i want it to return price. EG width might be 26 and length maybe 27 i want to return price of 3.20. does anyone know how to do this. i new to excel so keep it simply please if possible "Niek Otten" wrote:- Hide quoted text - - Show quoted text - |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need second answer to question posted yesterday (niek Otten)
=INDEX(INDIRECT("'"&C1&"'!$B$2:$J$10"),MATCH(B1,IN DIRECT("'"&C1&"'!$A
$2:$A$10"))+(LOOKUP(B1,INDIRECT("'"&C1&"'!$A$2-:$A $10"))<B1),MATCH(A1,INDIRECT("'"&C1&"'!$B$1:$J$1" ))+ (LOOKUP(A1,INDIRECT("'"&C1&"'!$B$1:$J$1"))<A1)) Hi pete thanks for the reply. I pretty new to excel so bear with me please. So if my 6 worksheets were 50mm, 35mm, 25mm, 25mm, aliwood, solidwood How do i put those into formula? can you show me how please. if i copy and paste this down col D will it work all the way to where i paste it. so if i enter width in A1 Length in B1 and type either of the above named worksheets in C1 it will return that price in D1. sorry for being so stupid "Pete_UK" wrote: This was the formula that Daddylonglegs gave you: =INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$1 0)+(LOOKUP(B1,Sheet2! $A$2-:$A$10)<B1),MATCH(A1,Sheet2!$B$1:$J$1)+(LOOKUP(A1 ,Sheet2!$B$1:$J $1)<A1)) This assumes that your width is in A1 and your length is in B1 of Sheet1. If you now want to put the sheet name in C1, you can modify the formula like this and put it in D1: =INDEX(INDIRECT("'"&C1&"'!$B$2:$J$10"),MATCH(B1,IN DIRECT("'"&C1&"'!$A $2:$A$10"))+(LOOKUP(B1,INDIRECT("'"&C1&"'!$A$2-:$A $10"))<B1),MATCH(A1,INDIRECT("'"&C1&"'!$B$1:$J$1" ))+ (LOOKUP(A1,INDIRECT("'"&C1&"'!$B$1:$J$1"))<A1)) Note that after each INDIRECT( there is a <quotes<apostrophe<quotes and before each exclamation mark there is a <quotes<apostrophe - this will handle sheetnames in C1 which have spaces in them. Hope this helps. Pete On Oct 23, 11:36 pm, jimE wrote: Ok that worked sweet. thanks u r the daddy. what would happen if i had 6 different price tables in the same format as the one shown. In sht one i want another col to state whate price table to look at like table A,B,C....... so i put in width and lenght + price group this might be different for each row. can this be done or am i just a pain in the B..T cheers jimE "daddylonglegs" wrote: Hello Jim, Looks like you want to round up to the next width or length shown, try this If your table is in sheet2 with widths in B1:J1 and lengths in A2:A10 and prices in B2:J10 then in sheet1 with a specific width in A1 (e.g. 26 in your example) and a specific length in B1 then use this formula in C1 =INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$1 0)+(LOOKUP(B1,Sheet2!$A$2-:$A$10)<B1),MATCH(A1,Sheet2!$B$1:$J$1)+(LOOKUP(A1 ,Sheet2!$B$1:$J$1)<A1)) "jimE" wrote: I have a work sheet with prices on it. Across the top are widths down the side is length. 24 30 36 ............................120 24 $1.00 $2.00 $3.33 30 2.20 3.20 4.20 . . 120 On another work sheet i have a width and length cells when i enter sizes i want it to return price. EG width might be 26 and length maybe 27 i want to return price of 3.20. does anyone know how to do this. i new to excel so keep it simply please if possible "Niek Otten" wrote:- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need second answer to question posted yesterday (niek Otten)
Yes, you enter 26 in A1 (for the width), 32 in B1 (for the length) and
25mm in C1 (for the sheet name) and you should get the price in D1 if that is where you have pasted the formula. Change any of the values in A1, B1 or C1, and you should get a different result in D1. If you want to copy the formula down column D, then you can enter appropriate values in A, B and C cells to give you the corresponding result in D. This relies on all 6 sheets having the same format. Hope this helps. Pete On Oct 24, 12:46 am, jimE wrote: =INDEX(INDIRECT("'"&C1&"'!$B$2:$J$10"),MATCH(B1,IN DIRECT("'"&C1&"'!$A $2:$A$10"))+(LOOKUP(B1,INDIRECT("'"&C1&"'!$A$2-:$A $10"))<B1),MATCH(A1,INDIRECT("'"&C1&"'!$B$1:$J$1" ))+ (LOOKUP(A1,INDIRECT("'"&C1&"'!$B$1:$J$1"))<A1)) Hi pete thanks for the reply. I pretty new to excel so bear with me please. So if my 6 worksheets were 50mm, 35mm, 25mm, 25mm, aliwood, solidwood How do i put those into formula? can you show me how please. if i copy and paste this down col D will it work all the way to where i paste it. so if i enter width in A1 Length in B1 and type either of the above named worksheets in C1 it will return that price in D1. sorry for being so stupid "Pete_UK" wrote: This was the formula that Daddylonglegs gave you: =INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$1 0)+(LOOKUP(B1,Sheet2! $A$2-:$A$10)<B1),MATCH(A1,Sheet2!$B$1:$J$1)+(LOOKUP(A1 ,Sheet2!$B$1:$J $1)<A1)) This assumes that your width is in A1 and your length is in B1 of Sheet1. If you now want to put the sheet name in C1, you can modify the formula like this and put it in D1: =INDEX(INDIRECT("'"&C1&"'!$B$2:$J$10"),MATCH(B1,IN DIRECT("'"&C1&"'!$A $2:$A$10"))+(LOOKUP(B1,INDIRECT("'"&C1&"'!$A$2-:$A $10"))<B1),MATCH(A1,INDIRECT("'"&C1&"'!$B$1:$J$1" ))+ (LOOKUP(A1,INDIRECT("'"&C1&"'!$B$1:$J$1"))<A1)) Note that after each INDIRECT( there is a <quotes<apostrophe<quotes and before each exclamation mark there is a <quotes<apostrophe - this will handle sheetnames in C1 which have spaces in them. Hope this helps. Pete On Oct 23, 11:36 pm, jimE wrote: Ok that worked sweet. thanks u r the daddy. what would happen if i had 6 different price tables in the same format as the one shown. In sht one i want another col to state whate price table to look at like table A,B,C....... so i put in width and lenght + price group this might be different for each row. can this be done or am i just a pain in the B...T cheers jimE "daddylonglegs" wrote: Hello Jim, Looks like you want to round up to the next width or length shown, try this If your table is in sheet2 with widths in B1:J1 and lengths in A2:A10 and prices in B2:J10 then in sheet1 with a specific width in A1 (e.g. 26 in your example) and a specific length in B1 then use this formula in C1 =INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$1 0)+(LOOKUP(B1,Sheet2!$A$2*-:$A$10)<B1),MATCH(A1,Sheet2!$B$1:$J$1)+(LOOKUP(A1 ,Sheet2!$B$1:$J$1)<A1)) "jimE" wrote: I have a work sheet with prices on it. Across the top are widths down the side is length. 24 30 36 ............................120 24 $1.00 $2.00 $3.33 30 2.20 3.20 4.20 . . 120 On another work sheet i have a width and length cells when i enter sizes i want it to return price. EG width might be 26 and length maybe 27 i want to return price of 3.20. does anyone know how to do this. i new to excel so keep it simply please if possible "Niek Otten" wrote:- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need second answer new question niek otten | Excel Worksheet Functions | |||
Please can someone look at question i posted yesterday | Excel Worksheet Functions | |||
How do I get back to a question I've posted? | Excel Discussion (Misc queries) | |||
Thanks to Mike and Niek Otten | Excel Discussion (Misc queries) | |||
For Niek Otten and Toppers custom function calculating time | Excel Worksheet Functions |