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?

Hi,

The mistake you are making is that you not providing a sheet name in the
formula.

there are ways of solving this. for example the formula below and a list of
sheet names in c1 down woould work

=INDIRECT(C1 & "!A6")

Mike

"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?

Many thanks Mike. My next problem is to drag this formula to the rightward.
Now my formula is =INDIRECT($A3 & "!A6"). A6 does not chage in to B6! if I
drag to the right. Is there any method?
Thanks.

"Mike H" wrote:

Hi,

The mistake you are making is that you not providing a sheet name in the
formula.

there are ways of solving this. for example the formula below and a list of
sheet names in c1 down woould work

=INDIRECT(C1 & "!A6")

Mike

"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?

What cell are you entering this formula into? Is it in row 1? Your formula
is pulling the Sheet name from the row number that the formula is placed in.
So, if your formula is placed in cell A6, then you'd need to adjust the
formula to:

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

If that's not it, then perhaps some more explanation of how your data is set
up would help.


"Narnimar" wrote:

No Elkar. I still get #ref! I removed the space and applied the 2nd formula.
But no luck!

"Elkar" wrote:

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?

I get #ref! still here Elkar. I think something missing.

"Elkar" wrote:

What cell are you entering this formula into? Is it in row 1? Your formula
is pulling the Sheet name from the row number that the formula is placed in.
So, if your formula is placed in cell A6, then you'd need to adjust the
formula to:

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

If that's not it, then perhaps some more explanation of how your data is set
up would help.


"Narnimar" wrote:

No Elkar. I still get #ref! I removed the space and applied the 2nd formula.
But no luck!

"Elkar" wrote:

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?

Try changing the
& "!A6"
portion of your formula to
& "!" & ADDRESS(6,Column())
and see how that works for you.

So I believe your formula should look like:
=INDIRECT($A3 & "!" & ADDRESS(6,COLUMN()))


"Narnimar" wrote:

Many thanks Mike. My next problem is to drag this formula to the rightward.
Now my formula is =INDIRECT($A3 & "!A6"). A6 does not chage in to B6! if I
drag to the right. Is there any method?
Thanks.

"Mike H" wrote:

Hi,

The mistake you are making is that you not providing a sheet name in the
formula.

there are ways of solving this. for example the formula below and a list of
sheet names in c1 down woould work

=INDIRECT(C1 & "!A6")

Mike

"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?

This should work just fine. I've recreated your example, based on the info
you've provided, in both Excel 2003 and 2007 without problem. You still
haven't stated what cell you are entering the formula into however.

A couple other possibilities to check. Are there any spaces in your sheet
names? Like 1<space? Does cell A6 on Sheet 1 return the error #REF!?


"Narnimar" wrote:

I get #ref! still here Elkar. I think something missing.

"Elkar" wrote:

What cell are you entering this formula into? Is it in row 1? Your formula
is pulling the Sheet name from the row number that the formula is placed in.
So, if your formula is placed in cell A6, then you'd need to adjust the
formula to:

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

If that's not it, then perhaps some more explanation of how your data is set
up would help.


"Narnimar" wrote:

No Elkar. I still get #ref! I removed the space and applied the 2nd formula.
But no luck!

"Elkar" wrote:

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?