I have a workbook with multiple sheets. One sheet is a summary sheet pulling
info from the other sheets. Is there a way to autofill a column with a
formula and have Excel increase the sheet name but keep the cell reference
constant? For example, I have the formula "=LOOKUP('Sheet (2)'!$G$1,'Sheet
(2)'!$G$1)" Down the column I want Excel to simply change the reference
sheet name to Sheet (3), Sheet (4), etc without changing the reference to
cell G1 so that I'm pulling the info from the same cell in each sheet. Any
thoughts? Thanks in advance!!!

Try
=LOOKUP(INDIRECT("'Sheet ("&ROW()&")'!$G$1"),INDIRECT("'Sheet
("&ROW()&")'!$G$1"))

I don't understand what you are doing by Looking up the value in a single
element array but the above will do the trick... to refer to Sheet (1) the
formula has to be in row 1... when you copy down row() will change to 2,3,...

"Julie" wrote:

I have a workbook with multiple sheets. One sheet is a summary sheet pulling
info from the other sheets. Is there a way to autofill a column with a
formula and have Excel increase the sheet name but keep the cell reference
constant? For example, I have the formula "=LOOKUP('Sheet (2)'!$G$1,'Sheet
(2)'!$G$1)" Down the column I want Excel to simply change the reference
sheet name to Sheet (3), Sheet (4), etc without changing the reference to
cell G1 so that I'm pulling the info from the same cell in each sheet. Any
thoughts? Thanks in advance!!!

Thank you so much! My eyes were definitely tricking me - I had swapped a
paren and double quote when I tried to use the ROW function! Oh, and the
Indirect does use an array, I just kept the cell reference the same to make
my example as simple as possible. Thanks again!!!!

"Sheeloo" wrote:

Try
=LOOKUP(INDIRECT("'Sheet ("&ROW()&")'!$G$1"),INDIRECT("'Sheet
("&ROW()&")'!$G$1"))

I don't understand what you are doing by Looking up the value in a single
element array but the above will do the trick... to refer to Sheet (1) the
formula has to be in row 1... when you copy down row() will change to 2,3,...

"Julie" wrote:

I have a workbook with multiple sheets. One sheet is a summary sheet pulling
info from the other sheets. Is there a way to autofill a column with a
formula and have Excel increase the sheet name but keep the cell reference
constant? For example, I have the formula "=LOOKUP('Sheet (2)'!$G$1,'Sheet
(2)'!$G$1)" Down the column I want Excel to simply change the reference
sheet name to Sheet (3), Sheet (4), etc without changing the reference to
cell G1 so that I'm pulling the info from the same cell in each sheet. Any
thoughts? Thanks in advance!!!

You are most welcome...

As a best practice I always build the string first and put INDIRECT around
it only after making sure that I am getting it right...

"Julie" wrote:

Thank you so much! My eyes were definitely tricking me - I had swapped a
paren and double quote when I tried to use the ROW function! Oh, and the
Indirect does use an array, I just kept the cell reference the same to make
my example as simple as possible. Thanks again!!!!

"Sheeloo" wrote:

Try
=LOOKUP(INDIRECT("'Sheet ("&ROW()&")'!$G$1"),INDIRECT("'Sheet
("&ROW()&")'!$G$1"))

I don't understand what you are doing by Looking up the value in a single
element array but the above will do the trick... to refer to Sheet (1) the
formula has to be in row 1... when you copy down row() will change to 2,3,...

"Julie" wrote:

I have a workbook with multiple sheets. One sheet is a summary sheet pulling
info from the other sheets. Is there a way to autofill a column with a
formula and have Excel increase the sheet name but keep the cell reference
constant? For example, I have the formula "=LOOKUP('Sheet (2)'!$G$1,'Sheet
(2)'!$G$1)" Down the column I want Excel to simply change the reference
sheet name to Sheet (3), Sheet (4), etc without changing the reference to
cell G1 so that I'm pulling the info from the same cell in each sheet. Any
thoughts? Thanks in advance!!!

Try this:

INDIRECT("'Sheet ("&ROWS(A$1:A2)&")'!G1")

--
Biff
Microsoft Excel MVP


"Julie" wrote in message
...
I have a workbook with multiple sheets. One sheet is a summary sheet
pulling
info from the other sheets. Is there a way to autofill a column with a
formula and have Excel increase the sheet name but keep the cell reference
constant? For example, I have the formula "=LOOKUP('Sheet
(2)'!$G$1,'Sheet
(2)'!$G$1)" Down the column I want Excel to simply change the reference
sheet name to Sheet (3), Sheet (4), etc without changing the reference to
cell G1 so that I'm pulling the info from the same cell in each sheet.
Any
thoughts? Thanks in advance!!!

Hi,

As a completely separate issue, naming sheets Sheet (2) is not very
informative although it works for you in this example.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Julie" wrote:

I have a workbook with multiple sheets. One sheet is a summary sheet pulling
info from the other sheets. Is there a way to autofill a column with a
formula and have Excel increase the sheet name but keep the cell reference
constant? For example, I have the formula "=LOOKUP('Sheet (2)'!$G$1,'Sheet
(2)'!$G$1)" Down the column I want Excel to simply change the reference
sheet name to Sheet (3), Sheet (4), etc without changing the reference to
cell G1 so that I'm pulling the info from the same cell in each sheet. Any
thoughts? Thanks in advance!!!