Referencing to a Sheet in Excel Formula

[mikewongbiz]

Hi All,

I have two sheets and one called Week1.

This formula works for me from the other sheet:

=INDEX(Week1!$G$3:$G$50,MATCH($C6,Week1!$B$3:$B$50,0))

However, I would like to replace Week1 in the formula with a text string I type say in cell A1. This is where I get stuck. Can someone shed some light for me, thanks in advance !

For example: I tried a few things like: =INDEX("$A1"!$G$3:$G$50,MATCH($C6,"$A1"!$B$3:$B$50,0)), I may need to use Indirect Function ? But this is where I get stuck.

 

[sheetspread]

Yes indirect (can be combined with the address function or row(), column(), etc.). Here's a recent example:

https://www.mrexcel.com/forum/excel-questions/1039074-there-formula-i-can-type-increment-sheets.html



and one from years back:

https://www.mrexcel.com/forum/excel-questions/560924-3-month-updating-sheet.html

 

[mikewongbiz]

Thanks

 

[Marcelo Branco]

Maybe...

=INDEX(INDIRECT("'"&A1&"'!$G$3:$G$50"),MATCH($C6,INDIRECT("'"&A1&"'!$B$3:$B$50"),0))

M.