Drag formula where sheet name (number) is variable

[duszek00]

Hi

First of all sorry for my english.

I have a problem with Excel.

In cell B2 I have formula:
=ABC_2007_1!$H$11
Where:
ABC_2007_1 - name of sheet
$H$11 - (blocked) cell in this sheet

I'd like to drag formula down:
=ABC_2008_1!$H$11
=ABC_2009_1!$H$11
=ABC_2010_1!$H$11

But the problem is that I don't know how to do that excell will change numbers ( 2007 --> 2008, 2008-->2009 etc +1) in sheet name.

Any idea how to do that?

[mikeTRON]

You are wanting to use the indirect function and some logic. Can you post a sample workbook and I can show you an easy way to do it (probably using the rows formula within indirect.

[duszek00]

Sample on my DropBox:

https://www.dropbox.com/s/bebvy8hz640s6xg/sample.xlsx

[Tony Valko]

Try this...

Entered in B2 and copied down as needed:

=INDIRECT("ABC_"&2006+ROWS(B$2:B2)&"_1!H11")

[mikeTRON]

Tony laid out exactly what I would have done.