Formula using INDIRECT function?

[Guest]

Hi,

Can someone help me translate the following formula?

=VALUE(IF(ISERROR(INDIRECT(U61&$V$61&MATCH($B$60,INDIRECT(U61&"A:A"),0))),0,INDIRECT(U61&$V$61&MATCH($B$60,INDIRECT(U61&"A:A"),0))))

U61 = Belgium!
V61 = h
B60 = Low

If I can understand what it means I may be able to work out what is going on
on a very complicated spreadsheet i have inherited...


Thanks,

 

[Bob Phillips]

The heart of it is this part

INDIRECT(U61&$V$61&MATCH($B$60,INDIRECT(U61&"A:A"),0))

which is searching column A on the worksheet pointed to by U61
(INDIRECT(U61&"A:A")) for the value obtained from B60
(MATCH($B$60,INDIRECT(U61&"A:A"),0)), which presumabnly returna a row
number, and concatenating that with V61 to get a cell reference,
concatenating that with B61 to get a cell in a worksheet reference, and then
using INDIRECT to lookup the value pointed to by that cell.

The rest just checks if it is an error, so as to keep it neat and tidy.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Phillip]

Phillip London UK

Matches the first occurrence of "Low" in Sheet named Belgium in column
A
and returns the value in column H in the matched row

So if Belgium!A3 contains "Low" then value of H3 is returned from the
sheet
where the formula is entered

If H3 contains an error value than formula returns 0