Ref Formula

N

Neil Pearce

Good morning,

On my summary worksheet Cells A1:A50 are the names of the 50 other worksheets.

I wish to insert Cell A1 of the repsective worksheets adjacent to the tab
names in Column B, e.g.

Cell A1 Sheet1
Cell B1 ='Sheet1'!A1

How would I change this formula to refer to column A on my Summary worksheet?


Kind regards,

Neil
 
B

Bernard Liengme

=INDIRECT(A1&"!A1")
Note that you do not need the single quotes around the sheet name when it
contains no spaces
If your value in A1 is something like Year 2009 (ie the sheet name has a
space in it)
=INDIRECT("'"&A1&"'!A1")
That is:
INDIRECT( double-quote single-quote double-quote &A1& double-quote single
quote !A1 double-quote )

best wishes
 
D

Dave Peterson

But those single quotes never hurt--even if they aren't necessary.

And there are other reasons (besides space characters) that the names would need
to be surrounded by those single quotes--names that look numbers, names that
look like cell addresses for example.
 
T

Tim Zych

One more nitpicky rule -- if the sheet name has single quotes in it, they
need to be doubled.

This modification should work for any sheet name.

=INDIRECT("'" & SUBSTITUTE(A1,"'","''") & "'!A1")
 

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

Top