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:

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 -

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 -

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 -