need second answer to question posted yesterday (niek Otten)

[Guest]

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

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$10)+(LOOKUP(B1,Sheet2!$A$2:$A$10)<>B1),MATCH(A1,Sheet2!$B$1:$J$1)+(LOOKUP(A1,Sheet2!$B$1:$J$1)<>A1))

:

 

[Pete_UK]

This was the formula that Daddylonglegs gave you:

=INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$10)+(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,INDIRECT("'"&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

 

[Guest]

=INDEX(INDIRECT("'"&C1&"'!$B$2:$J$10"),MATCH(B1,INDIRECT("'"&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]

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