Incrementing formulas each month

[zookeepertx]

We have several workbooks - 1 for each employee - and a summary workbook that consolidates all of their data onto 1 sheet. Each workbook is for a 1 month period; naturally, the workbooks' names include the month. So, at the start of the month, each workbook is "saved as" (new month Daily Audit) and started over. Is there a way to get the summary formulas to increment to the new month? Otherwise, someone has to go in and relink each cell on each worksheet in the summary to the new month's individual sheets. Clear as mud, right?

Here's the formula in cell C3 of the summary workbook:

=SUM('G:\LP\A - Investigator file\John Doe\[Oct Daily Audit.xls]Monthly Totals'!$C$3+'G:\LP\A - Investigator file\Jane Doe\[Oct Daily Audit.xls]Monthly Totals'!$C$3+'G:\LP\A - Investigator file\Mike Smith\[Oct Daily Audit.xls]Monthly Totals'!$C$3+'G:\LP\A - Investigator file\Mary Smith\[Oct Daily Audit.xls]Monthly Totals'!$C$3)

I'm hoping to make the Oct reference look for Nov when the workbook is saved for use in Nov & continue each month. I feel sure it's possible, just can't think how. I thought INDIRECT might fit somehow, but I don't really understand how that works.

Thanks for your help!

Jenny

 

[Smitty]

There are a few ways:

First you can try Edit-->Links and point the new workbook back to itself with the Change option.

Second is to use Find & Replace (CTRL+H)-->Find-->"[Oct"-->Replace with-->"[Nov" (no quotes). You use the bracket to make sure you don't inadvertently replace any other text.

HTH,

 

[zookeepertx]

Oooh, that's cool! I use Find & Replace a lot every time month end rolls around, but have never heard of Edit-->Links before. That one's FUN! This is going to benefit several of us!

Thanks!!

Jenny

 

[zookeepertx]

Ruh roh- there's a hitch. When using the Edit Links & you change the Source to the new month, it seems that the links on ALL tabs change.
So, the tabs are Aug, Sep, Oct... etc. Each tab was created by copying Aug & changing the name to the correct month & having the links refer to the appropriate month in the individual sheets. But when the guy doing this went to change the source for Nov, it also changed the source for Aug to Nov (but not Sep or Oct, which I thought was strange). Then he changed Dec links & that changed Aug links to Dec, too!

On the practice sheet I'd made, changing the sources on 1 tab also changed the source on the other tabs. But I didn't think about each of his tabs being for different months. Somehow that seems to be messing us up.

Am I being the least bit clear? I don't explain well with words AT ALL, so I usually use wayyyy too many of them!

Jenny

 

[Smitty]

That sounds like odd behavior, and hard to diagnose without seeing everything involved. If changing one specific set of links also changes others, then it sounds like not all of the formulas had the correct (updated) links to begin with. It might be a good idea to check and make sure that all of the formulas in question really are pointing to the right place to begin with.


Regardless, Find & Replace might be a better option, because you can't limit Edit Links to one worksheet. It's going to replace all links to a specific workbook.

 

[zookeepertx]

It turns out, the guy doing it had not explained to me completely exactly what his formulas were doing. I went to HIS desk this morning & dug a little further. He had about 6 columns & maybe 20 rows filled with formulas linking each cell with data from the 4 individual workbooks. When he copied his sheet from Aug to create Sep, he never changed the formulas to point to the Sep individual sheets. Then did the same for the rest of the months. So when he did the Edit Links, it just got ALL messed up!

Ultimately, we used Find-Replace to fix the formulas on each sheet to look at the correct month's data & now it's all good. So, next time he needs to recreate the book, he can just use the Edit Links.

However, *I* will be making very good use of the Edit Links next week, when working on my month-end reporting! So, thanks again!

Jenny

 

[Smitty]

It turns out, the guy doing it had not explained to me completely exactly what his formulas were doing. I went to HIS desk this morning & dug a little further.

Ain't it always the way.

Good luck!

BTW, what zoo do you work for?

 

[zookeepertx]

BTW, what zoo do you work for?

LOL! Just my own! When I picked that name a long time ago I had 2 horses, 2 cats & 8 dogs; it was pretty much a zoo around here, so it seemed appropriate. Since then, I've lost 1 cat & had got down to 3 dogs - for a while. Then, one of my neighbor's half starved female dogs had 9 puppies under my shed! I couldn't send them back over to starve, so, all of a sudden I had 13 dogs! Back down to 7 now; you want one? I've got extras!

Jenny

 

[Smitty]

I was just wondering since I graduated from TCU Ranch Management, and my wife and daughter love the Ft. Worth Zoo.

We've had our own menagerie here too with disposable cats & chickens (racoons, lions, nasty neighbor dogs, and possums take care of those), so we're down to 2 good cats and a dog now. But I hear from the "girlvine" that we're supposed to be getting two pygmy goats soon for 4H...(oh yes, and the grand idea that the ladies will be willing to help make goat milk cheese for the girl scout troup...That'll happen).

You want 2 goats?

Have a great weekend!