Formula using INDIRECT function?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Louise - 25 Jan 2007 16:19 GMT

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,

Reply to this Message

Bob Phillips - 25 Jan 2007 16:43 GMT

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.

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi,
>
[quoted text clipped - 11 lines]
>
> Thanks,

Reply to this Message

Phillip - 25 Jan 2007 17:31 GMT

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

Reply to this Message