Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect with index and match
Hi, I have the below formula where it flech data from sheet as per C5.. In
this case it is 'PRX'.. =INDEX(PRX!G1:G100,MATCH(1,(C7=PRX!C1:C100)*(F7=PR X!D1:D100)*(J9=PRX!E1:E100)*(C12=PRX!F1:F100),0)) How can I use indirect ref to sheet with this formula? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect with index and match
Hi,
If the sheet name is in A1: =INDEX(INDIRECT("'"&A1&"'!"&G1:G100),MATCH(1,(C7=I NDIRECT("'"&A1&"'!"&C1:C100))*(F7=INDIRECT("'"&A1& "'!"&D1:D100))*(J9=INDIRECT("'"&A1&"'!"&E1:E100))* (C12=INDIRECT("'"&A1&"'!"&F1:F100)),0)) be careful with the double and single quotes. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Kashyap" wrote: Hi, I have the below formula where it flech data from sheet as per C5.. In this case it is 'PRX'.. =INDEX(PRX!G1:G100,MATCH(1,(C7=PRX!C1:C100)*(F7=PR X!D1:D100)*(J9=PRX!E1:E100)*(C12=PRX!F1:F100),0)) How can I use indirect ref to sheet with this formula? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect with index and match
All the ranges referred to are in the *active* sheet, not the sheet being
referenced in A1. If the active sheet was named X here's how that evaluates: INDIRECT("'"&A1&"'!"&X!G1:G100) Use this syntax for each call to INDIRECT with the appropriate range: INDIRECT("'"&A1&"'!G1:G100") -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, If the sheet name is in A1: =INDEX(INDIRECT("'"&A1&"'!"&G1:G100),MATCH(1,(C7=I NDIRECT("'"&A1&"'!"&C1:C100))*(F7=INDIRECT("'"&A1& "'!"&D1:D100))*(J9=INDIRECT("'"&A1&"'!"&E1:E100))* (C12=INDIRECT("'"&A1&"'!"&F1:F100)),0)) be careful with the double and single quotes. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Kashyap" wrote: Hi, I have the below formula where it flech data from sheet as per C5.. In this case it is 'PRX'.. =INDEX(PRX!G1:G100,MATCH(1,(C7=PRX!C1:C100)*(F7=PR X!D1:D100)*(J9=PRX!E1:E100)*(C12=PRX!F1:F100),0)) How can I use indirect ref to sheet with this formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
External Reference with INDIRECT, INDEX/MATCH | Excel Worksheet Functions | |||
Indirect function in Index/Match Array | Excel Discussion (Misc queries) | |||
Match, Index, Indirect ? | Excel Worksheet Functions | |||
Index,Indirect, and Match | Excel Worksheet Functions |