ROW() in formula Excel for Mac 2011

Question

I have an XLS file with several Sheets named "1", "2", "3", "4", etc.

Now I need a formula that will result in e.g. "='1'!M16" where the name of the Sheet I am referring to is based on current row. So if the current row is 16, the formula would be "='16'!M16".

I created a formula =INDIRECT("'" & ROW() & "'!" & "M16") but Excel for Mac 2011 states "#VALUE" in the cell... What is wrong with my formula, please? Or any other suggestion?

Thanks very much for any help.

Tom

Answer 1

Try: =INDIRECT(CONCATENATE(ROW(),"!","M",ROW())

I have a Mac only, it works. Thanks by the way for your question. I learnt thereby

Answer 2

=INDIRECT("'"&ROW()&"'!M16")