Office &amp; productivity software forum: Automatically carrying over totals on worksheets

Want each sheet to have a running total

by -DJ7- - 3/25/12 1:18 PM

In Reply to: Sure by MarkFlax

Basically I'm trying to set it up so there is a running total without having to manually adjust the sheet name in the formula -- I thought this might be accomplished using a macro however, to my knowledge, Excel '08 for Mac does not have this feature.

I've been copying a template for each month but that still requires me to manually change the sheet name in the formula -- whch is the step I was hoping to avoid.

Was this reply helpful? (0) (0)

Staff pick

Ahh well.

by MarkFlax - 3/26/12 3:43 AM

In Reply to: Want each sheet to have a running total by -DJ7-

You mentioned macros.

Are you ready to delve a little deeper, into VBA? All Excel applications have the option to open VBA and code your own macros, and to change a sheet name you can do that in VBA. Here's an example;

http://vbamacros.blogspot.co.uk/2007/09/change-worksheet-name.html

You may find better examples/tutorials in Google with Excel VBA change sheet name.

I hope that helps.

Mark

Was this reply helpful? (1) (0)

Staff pick

Re: macro

by Kees_B - 3/26/12 3:53 AM

In Reply to: Want each sheet to have a running total by -DJ7-

Changing it manually is one change, and a copy by dragging the changed formula to the other columns. Easy and fast.

Running the macro you write doesn't save any time, I think. So all you do is invest time to learn VBA. That might be useful for other purposes, but not for this particular one.

Kees

Was this reply helpful? (1) (0)

Staff pick

Thanks for the insight

by -DJ7- - 3/26/12 5:43 AM

In Reply to: Re: macro by Kees_B

Thanks so much for your insight -- I haven't done much with Macros and was advised (by the Microsoft help desk) that Macro's are not possible in the early version ('08) of Excel for Mac so I'm glad to learn that they would not likely help save me time in the long run.

For now, I guess I'll continue manually changing the formula in each worksheet and save the VBA learning for another (more suitable) time.

Thanks again -- have a great week.

DJ

Was this reply helpful? (0) (0)

Staff pick

Answer Best answer as chosen by user -DJ7-

I think you mean ...

by Kees_B - 3/25/12 10:59 AM

In Reply to: Automatically carrying over totals on worksheets by -DJ7-

to copy the February worksheet to the March worksheet while automatically changing January!F20 in February!F20.

Then the answer is no. Changing ranges in formulas when copying only does rows and columns (unless you make them absolute), but not for worksheet names. Those are always absolute, so to speak. And one of the reasons is that worksheets don't have a sequence. You can move them around in your workbook and the formulas will keep working without change. There's no such thing as 'the previous workbook' while there is a thing like 'the row above'.

Which means you have to do some manual editing of the formulas.

Kees

Was this reply helpful? (1) (0)

Staff pick

Ahh, I missed that.

by MarkFlax - 3/25/12 12:02 PM

In Reply to: I think you mean ... by Kees_B

And I agree.

Mark

Was this reply helpful? (1) (0)

Staff pick