Dragging formulas that refer across sheets

Hi Everyone,

First off I want to say thank you for reading this and this looks like a great site.

I have attached a dummy sheet for aid. On "Sheet" 1 cell C4, I would like it to refer to D1 on another sheet called "Jan 2003". Doing this is simply ='Jan 2003'!D1 . But I want to drag the formula across so it refers to the other worksheets. For instance, cell C5 would have 'Feb 2003'!D1 information, and C6 will have 'March 2003'!D1 . I am taking this all the way out to 2008. Any help is appreciated. Thanks!

Dan

I would also like to add that the names of the sheets may very within the years, for instance Jan 2003 then Jan. 2004 and January 2005. However, the data I want to pull run through the same "cell name" on consecutive sheets. I'm not skipping sheets here.

Lastly, If the formula changes significantly if the sheets are all on a different work book. Please let me know.

Thank you so much!

amazing, thanks tons rwgrietveld. but I've been given a spreadsheet with inconsistent naming. Nothing absurd, first three letters seem always consistent. how would one cope with this. I've attached an example.

got it thanks. If I'm given a workbook with tons of sheets that even has the year changing such as January 2005, and January 06 and Jan07 what would you change in your macro below?

Sub makeMonth()

For x = 1 To Worksheets.Count
If Worksheets(x).Name <> "Sheet1" Then
Worksheets("Sheet1").Cells(1, 1 + x).Value = Right(Worksheets(x).Name, 4)
Worksheets("Sheet1").Cells(2, 1 + x).Value = Left(Worksheets(x).Name, Len(Worksheets(x).Name) - 5)
End If
Next x
End Sub

This is great stuff. Really appreciate it.

Hi,

nice post!

Does the same works when you use =indirect() when you want to refer across files? I tried but every time i reopen the file i get an "reference" error. When i reopen the files where the cels ar in, the reference error disappears.