Sub to change formulas in a col

[Max]

Hi guys,

I need help for a sub which can change existing formulas in col D
which refers to a particular sheetname, to an equivalent which
uses INDIRECT() reading the same sheetname now placed in D3 instead

For example:

Need to change existing: =SUM('1'!G33:M33)-'1'!L33

to

:=SUM(INDIRECT("'"&D$3&"'"&"!G33:M33"))-INDIRECT("'"&D$3&"'!L33")

The sheetname: "1" (without the double quotes) is entered into D3

There's quite a fair bit of cells involved in col D
which carries similar formulas to be changed,
so automating the formula change would be very useful.

(And once the entire col's formulas have been changed,
I could just simply fill col D across another 30 or so columns.
The sheetnames: "1", "2", "3", etc represent the days of the month)

Thanks for insights

 

[Bob Phillips]

Can't you just change one, then copy down?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

The existing formulas don't run in any meaningful sequence down the col.
The only commonality is the ones to be changed have the reference
to the sheetname: "1" inside the formula

Some examples of the existing formulas to be changed in col D:

In D10: =SUM('1'!G23:M23)-'1'!L23
In D11: =SUM('1'!G33:M33)-'1'!L33
In D12: =SUM('1'!G52:M52)-'1'!L52
In D13: =SUM('1'!L23,'1'!L52)
In D17: ='1'!L43
In D26: =SUM('1'!G17:M17)-'1'!L17+SUM('1'!G86:M86)-'1'!L86
In D29: ='1'!L17+'1'!L27+'1'!L47

and so on ..

 

[Bob Phillips]

Max,

Without some commonality you would struggle even with VBA. For instance, you
said that
=SUM('1'!G33:M33)-'1'!L33
should change to
=SUM(INDIRECT("'"&D$3&"'"&"!G33:M33"))-INDIRECT("'"&D$3&"'!L33")

What rule that applies to that would be applied to
='1'!L43
as it is not clear to me.

Unless there is a clear rule/algorithm, or whatever, it will not be
possible.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

Bob, thanks for your response !

The sheetname: "1" is now entered into D3
and I wanted to convert all existing formulas in col D
which presently referred to this sheetname,
via using INDIRECT() to reference D3 instead.

So ..

='1'!L43

would become: =INDIRECT("'"&D$3&"'!L43")

='1'!L17+'1'!L27+'1'!L47

would become:
=INDIRECT("'"&D$3&"'!L17")+INDIRECT("'"&D$3&"'!L27")+INDIRECT("'"&D$3&"'!L47
")

=SUM('1'!L23,'1'!L52)

would become: =SUM(INDIRECT("'"&D$3&"'!L23"),INDIRECT("'"&D$3&"'!L52"))

Hope the above clarifies a little better ..

Once the entire lot of formulas in col D is changed
to refer to D3 for the sheetname, it would then
be possible to do a simple fill of the formulas in col D
across another 30 cols (that's the ultimate objective).