Try taking out the leading apostrophe.

=INDIRECT(ROW()&"!A6")

You only need to enclose the sheet name in apostrophes when there is a space
in the name. And if you do, you would also need to include a second
apostrophe at the end of the sheet name. Like this:

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

I included added spaces above for emphasis, but they should not be used.

HTH
Elkar


"Narnimar" wrote:

My sheets names are 1, 2, 3,... 100. I am trying to make a "summery" sheet in
the same work book from cells a6 to j6 from 1 to more than 100 numeric named
sheets.The formula =INDIRECT("Sheet" & (ROW()) & "!A6") is working fine if
the sheet names are from system default Sheet1, sheet2 sheet3 etc.

Now I modified the formula into =INDIRECT("'" & (ROW()) & "!A6") but it
returns #REF!. I dont know what is the mistake I am doing! Can any one solve
this problem?