Ref Formula

[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

 

[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

 

[Jacob Skaria]

In B1
=INDIRECT(A1&"!A1")

If this post helps click Yes

 

[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.

 

[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")