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

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


"BarryMart" wrote:

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

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


Gord Dibben MS Excel MVP


On Mon, 14 Jul 2008 12:56:07 -0700, BarryMart
<(E-Mail Removed)> wrote:

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

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

"Gord Dibben" wrote:

> =INDIRECT(ROW(536:536) & "!$D$7") entered in A536 then copied down.
>
>
> Gord Dibben MS Excel MVP
>
>
> On Mon, 14 Jul 2008 12:56:07 -0700, BarryMart
> <(E-Mail Removed)> wrote:
>
> >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
> >
>
>

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

"Charlie" wrote:

> 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.)
>
>
> "BarryMart" wrote:
>
> > 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,

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

"Charlie" wrote:

> 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.)
>
>
> "BarryMart" wrote:
>
> > 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
> >
> >