Use values as coordinates based on another sheet, formula?

**cristian.ene** · 05-27-2009, 11:53 AM

Hello,

For the excel file in the attachment i am trying to do the following, but i don't seem to find the answer:

Based on choosing a product type, (p1, p2, p3, ... pn) data will be taken from that sheet only, doing the following.

In the first sheet i can input W and H, and using those W and H as coordinates, a value will be automatically given.

Example: P1 values: if i imput in the first list H of 2250 and W of 1830, i want to see the value coresponding to those coordinates, that is 423, in my case.

BUT if the value is greater, i want it to choose the next line or the next column,... and based on the example above, let's say i need a H of 2252 with 1830, i want to be given the answer 433.

If i am not understood, correctly, please ask for additional info

Thank you,

Cristian

**DonkeyOte** · 05-27-2009, 12:17 PM

Not entirely sure I follow but if we assume:

C5 holds P1, P2 etc...
B10: W
C10: H

Then

D10:
=INDEX(INDIRECT("'Size "&$C$5&"'!C3:S26"),MATCH($C10,INDIRECT("'Size "&$C$5&"'!$B$3:$B$26"))+ISNA(MATCH($C10,INDIRECT("'Size "&$C$5&"'!$B$3:$B$26"),0)),MATCH($B10,INDIRECT("'Size "&$C$5&"'!$C$2:$S$2"))+ISNA(MATCH($B10,INDIRECT("'Size "&$C$5&"'!$C$2:$S$2"),0)))

Not very elegant... and Volatile given use of INDIRECT.

I've attached a working sample given locale differences.

**cristian.ene** · 05-27-2009, 05:22 PM

Thanks man,.... you're like a guru or something

**cristian.ene** · 06-05-2009, 10:04 AM

I'm either dumb or.. i don't understand

This is the formula applied from the answer above.

=INDEX(INDIRECT('P1'!B2:R18);MATCH($C9;INDIRECT('P1'!$A$2:$A$18))+ISNA(MATCH($C9;INDIRECT('P1'!$A$2:$A$18);0));MATCH($B9;INDIRECT('P1'!$B$1:$R$1))+ISNA(MATCH($B9;INDIRECT('P1'!$B$1:$R$1);0)))

Guess the result #REF!

Where am i wrong?

I HATE IT!

**DonkeyOte** · 06-08-2009, 04:54 AM

Be sure to read the help files on INDIRECT function.

Essentially what INDIRECT is looking to do for you is execute whatever appears within the parentheses as a text string and execute as though it were entered as a formula, eg:

=INDIRECT("'Sheet 1'!A10")

so the text string created is 'Sheet 1'!A10 ... the text string being encased within "... ie "textstring" ... the above is then interpreted by XL

='Sheet 1'!A10

In your formula you've not created a text string...ie you've used:

INDIRECT('P1'!B2:R18)

as opposed to

INDIRECT("'P1'!B2:R18")

... in fact in your example if P1 is constant you need not use INDIRECT at all.... if though the sheet reference (ie P1) were to be variable (as implied earlier in the thread) and say the sheet name was stored in cell A1 you would use:

INDIRECT("'"&A1&"'!B2:R18")

thereby creating a text string of 'sheetname'!B2:R18