| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ref Formula
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ref Formula
=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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Neil Pearce" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ref Formula
In B1
=INDIRECT(A1&"!A1") If this post helps click Yes --------------- Jacob Skaria "Neil Pearce" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ref Formula
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. Bernard Liengme wrote: =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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Neil Pearce" wrote in message ... 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ref Formula
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") -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Dave Peterson" wrote in message ... 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. Bernard Liengme wrote: =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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Neil Pearce" wrote in message ... 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
