Auto fill

T

Tom

How can I create a formula that will point to different
sheets as I autofill down. Example a1 will be sheet1a1
a2 will be sheet2a1. The only issue is that the sheets
actually have a real name.

Thanks in advance.
 
F

Frank Kabel

Hi
in A1 enter the formula
=INDIRECT("'sheet" & ROW(1:1) & "'!A1")
and copy this down
 
G

Gord Dibben

Tom

If the sheets are not named Sheet1, Sheet2 etc. you must get them into a list
on a sheet then use the INDIRECT with that list.

You could type them into a column or use VBA macro to place them there.

''list of sheet names in a workbook - placed on a new worksheet in column A.
Sub ShowNames()
Set wkbkToCount = ActiveWorkbook
iRow = 1
With Sheets.Add
For Each ws In wkbkToCount.Worksheets
.Rows(iRow).Cells(1).Value = ws.Name
iRow = iRow + 1
Next
End With
End Sub

Your formula could now be =INDIRECT('new sheet'!A2 & "!A1")

Drag/copy down.

Gord Dibben Excel MVP
 
Top