Indirect formula not working if I give custom name for Sheets

N

Narnimar

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

JLatham

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()))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup and count in the same formula 1
letters for numbers 1
make formula look at diff sheet 5
Name Assigned Formula Not Working 3
alter code to apply to range (links cells w/ row insertions) 1
INDIRECT Function 9
Combine SUMPRODUCT with INDIRECT function 2
Vlookup and return sheet name also 2

Top