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?

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?

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?