how to increment sheets in an excel formula with auto fill

[RC2902]

Hi there,
Hoping someone may be able to assist me with my issue, I just can'
workout what to do here.

I have a Workbook with approximately 30 sheets all with different names
for example:
Sheet1 is named "2013", Sheet2 named "DAY 1", Sheet3 "DAY2"...

On the sheet named "2013" in cell B4, I wish to reference a cell F27 o
the sheet named "DAY 1". Then I wish to be able to auto fill across th
page so that the referenced cell remains the same, however the shee
increments to "DAY2", then "DAY3" and so on.

So far I have used
='DAY 1'!$F$27

However the issue is, that the sheet does not increment from 'DAY 1' t
DAY 2' then 'DAY 3' respectively with auto fill. I am able to manuall
change the entry from 'DAY 1' to 'DAY 2', however I may as well ju
manually enter value from cell F27 on each sheet. Surely there is
formula to automate this?

Does some one have a solution to my problem? I have never used INDIREC
function, and have tried to apply different examples, however alway
results in a #REF error.

Assistance is greatly appreciate

 

[Gord Dibben]

Use INDIRECT function

=INDIRECT("DAY" & (COLUMN()-1) & "!F27")

entered in B4 then dragged across

Gord