Pull down forumlae for relative Sheet reference

[BrettE]

G'day Excel Forum,

First ever post on the forum so I hope I am not asking a question that has been answered before (have tried searching).

I am trying to write a formulae which has a relative sheet reference.

I am trying to put into one column the values from the same cell over multiple sheets (approx 168 sheets)
I was hoping to pull down the formulae so that the sheet number changes each time
eg:
Sheet1!$B$5
Sheet2!$B$5
Sheet3!$B$5 etc

Is this possible?

Greatly appreciate any assistance

[JBeaucaire]

To pull cell B5 from every sheet in a list:

1) List the actual sheet names in column A:

A5 = Sheet1
A6 = Sheet2
A7 = Sheet3
...etc.

2) Put this formula in B5 and copy downward:

=INDIRECT("'" & $A5 & "'!B5")

==================================

To pull a variety of cells listed in column from a sheet you select in a drop down in B2:

1) You still need a list of sheetnames somewhere, use that as the Data Validation > List source for B2.

2) In column A list the cells you want to retrieve information from, a "snapshot" so to speak:

A5 = B5
A6 = C12
A7 = D22 (these are examples only, edit to your desired cell addresses)
...etc.

3) In put this formula, then copy down:

=INDIRECT("'" & $B$2 & "'!" & $A5)


---------------
TIP: If your sheet names really are numerically sequential like you've indicated...

1) enter the first sheetname in the first cell where you're creating your list
2) grab the lower right corner of that cell and drag downward.

Excel should increment any number at the end of that string.

[BrettE]

G'day Jerry,

Thank you very much for solving that problem for me. The first solution posted is working well.

Cheers