Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula using INDIRECT function?
Hi,
Can someone help me translate the following formula? =VALUE(IF(ISERROR(INDIRECT(U61&$V$61&MATCH($B$60,I NDIRECT(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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula using INDIRECT function?
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) "Louise" wrote in message ... Hi, Can someone help me translate the following formula? =VALUE(IF(ISERROR(INDIRECT(U61&$V$61&MATCH($B$60,I NDIRECT(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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula using INDIRECT function?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a formula containing the INDIRECT function | Excel Worksheet Functions | |||
Name of named formula used in an INDIRECT function | Excel Worksheet Functions | |||
Can INDIRECT function reference a cell that contains a formula | Excel Worksheet Functions | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Cell value in a formula -- Indirect function? Help please... | Excel Programming |