Autofill incrementally within formulae

[BarryMart]

I have a formula in A536 that reads:
='536'!$D$7

I want the formula in A537 to read:
='537'!$D$7

and so on...

Any ideas welcomed and appreciated!

Thanks,
Barry

 

[Charlie]

I don't know if it can be done with drag-n-drop editing but you can do it in
VBA like this

Sub Test()

Dim Cell As Range

For Each Cell In Range("A536:A540")
Cell.Formula = "='" & Cell.Row & "'!$D$7"
Next Cell

End Sub

(You didn't say how far down you wanted to go so I just went to row 540 as
an example. I presume the workbooks 536.xls, 537.xls, etc., already exist,
otherwise you'll get the file dialog for every row when you run the macro.)

 

[Gord Dibben]

=INDIRECT(ROW(536:536) & "!$D$7") entered in A536 then copied down.


Gord Dibben MS Excel MVP

 

[BarryMart]

That gives me an idea I will try, but it doesn't really work. I need the
formula to refer to a worksheet specifically named after the row in which the
formulae are to appear (i.e. ='536'!$d$7, ='537'!$d$7, ='538'!$d$7, etc)

But it has to be a formula so that the cells' data on that particular
worksheet dislays and is then calculated in the "collection" worksheet.

I hope that's clear, and please accept my gratitude for your suggestion!

Barry

 

[BarryMart]

I may have missed something in your reply, but I think I tried a similar
approach in the following folder that only returned the data (that is, text0
not the real formula I need:

="="&"'"&ROW()&"'!$d$7"

That doesn't work because I need the formula to refer to a worksheet
specifically named after the row in which the formulae are to appear (i.e.
='536'!$d$7, ='537'!$d$7, ='538'!$d$7, etc)

But it has to be a formula so that the cells' data on that particular
worksheet dislays and is then calculated in the "collection" worksheet.

I hope that's clear, and please accept my gratitude for your suggestion!

Barry

 

[BarryMart]

Charlie,

FANTASTIC!

Sorry about the earlier hasty reply. I reentered the suggested code as a
MACRO and it worked perfectly!

Thanks so much for your help!

Barry