Sub to change formulas in a col
 

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
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----

Sub to change formulas in a col
 

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" wrote in message
...
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
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----

Sub to change formulas in a col
 

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

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
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" wrote in message
...
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
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----

Sub to change formulas in a col
 

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" wrote in message
...
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 ..

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
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" wrote in message
...
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
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----

Sub to change formulas in a col
 

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

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
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)