Auto fill

[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.

 

[Frank Kabel]

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

 

[Tom]

Thanks frank but this didn't work any other suggestions?

Thanks again.

 

[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