Using dynamic sheet name in formula
 

I am using the following formula successfully but would like it to be more dynamic. I need to make the sheet name dynamic ie. _2875EC227A1522.


{=INDEX(_2875EC227A1522!$B$16:$E$16,MATCH(MIN(ABS( _2875EC227A1522!$B$7:$E$7-B15)),ABS(_2875EC227A1522!$B$7:$E$7-B15),0))}

I have tried the following but it returns a #value error. The sheet name is located cell B5.

{=INDEX(""&B5&"!"&"$B$16:$E$16",MATCH(...

Using dynamic sheet name in formula
 

I changed the approach and used INDIRECT instead to point to a named range


{=INDEX(INDIRECT($B$5),MATCH(...




On Monday, February 23, 2015 at 1:55:53 PM UTC-7, wrote:
I am using the following formula successfully but would like it to be more dynamic. I need to make the sheet name dynamic ie. _2875EC227A1522.


{=INDEX(_2875EC227A1522!$B$16:$E$16,MATCH(MIN(ABS( _2875EC227A1522!$B$7:$E$7-B15)),ABS(_2875EC227A1522!$B$7:$E$7-B15),0))}

I have tried the following but it returns a #value error. The sheet name is located cell B5.

{=INDEX(""&B5&"!"&"$B$16:$E$16",MATCH(...