Excel Formulas

[Shane]

Hi Guys, got a problem here that hope you can solve


Currently, i have to update my database formulas monthly

What is currently happening is that i entered a formula
to read data from a monthly source file.

So, at every month end, i have to change the formula
manually so that the new month's source data could be
read.

Is there anyway to create a formula or a Macros so that i
do not have to update all the formulas manually, but
rather, by pressing the Macros icon,or by some other
means, the formulas can be updated?

Pls help
Thanks

 

[Don Guillett]

Might help if you copy/paste an example of your formula. Do NOT send
attachment.

 

[Edwin Tam (MS MVP)]

Of course, it's possible. But you didn't give examples. Therefore, no one can give you a sample macro.

But for the type of task you described, it can usually be solved by using Excel's Find/Replace function.

For example, if your old formulas look like :
=A1+G5
You want to change them to something like:
=B1+H5
You can find "=A" replace with "=B". Then find "+G" replace with "+H"

Similarly you can replace worksheet names or whatever.

If you decided that FIND/REPLACE cannot serve your requirements, tell us more and I'm sure someone can help.


----- Shane wrote: -----

Hi Guys, got a problem here that hope you can solve


Currently, i have to update my database formulas monthly

What is currently happening is that i entered a formula
to read data from a monthly source file.

So, at every month end, i have to change the formula
manually so that the new month's source data could be
read.

Is there anyway to create a formula or a Macros so that i
do not have to update all the formulas manually, but
rather, by pressing the Macros icon,or by some other
means, the formulas can be updated?

Pls help
Thanks

 

[Don Guillett]

As I said best to see what you have but using the INDIRECT function would
do it for you.