Copy Paste Formula - Increase page link

[johnrobgray]

Hi

I want to copy and paste the below formula into different pages.

='[Dan Blakemore Aug 2023.xlsx]2'!$Y$34

Basically I have 1 - 31 tabs along the bottom and want each tab to relate to the relevant tab in the linked file. Is there a way to do this quickly without having to edit the number in the formula on every page?

so tab 3 becomes ='[Dan Blakemore Aug 2023.xlsx]3'!$Y$34 & so on & so on

 

[offthelip]

You can do it with VBA very easily, something like this:

Sub test()
For i = 1 To Worksheets.Count
With Worksheets(i)
.Range(.Cells(3, 6), .Cells(3, 6)).Formula = "='[Dan Blakemore Aug 2023.xlsx]" & i & "'!$Y$34"
End With
Next i
End Sub

Note I didn't know where you wanted it pasted so I put in in F3