Moving formulas from month to month

**kurmugon** · 02-04-2007, 12:32 PM

Newbie, here.
I do a simple spreadsheet, wherein I post our daily sales, by categories, with totals, day, week, and month, for twelve months. Kind brother-in-law set up first year sheets, and left town for the islands.
I stumbled through setting up the formulas for January for '07, and they worked.
How do I format the remaining months with the same formulas that I use each month without copy/paste each month?
Thanks,
kurmugon

**oldchippy** · 02-04-2007, 12:38 PM

Not being able to see what's in front of us, like you can it's difficult to give you an answer that probably the best. Is it possible you could post a sample of your spreadsheet?

**kurmugon** · 02-04-2007, 01:06 PM

Will do my best, but it may take a while. The spreadsheet is on the laptop via a floppy. The home pc does not do floppies, and the laptop is on dialup. And how would I post a sample? Just copy and paste to the message board?
Thanks,
kurmugon

**oldchippy** · 02-04-2007, 01:16 PM

OK, to post a sample, go to My Documents or wherever you keep your file, right-click and Send to > Compressed folder. You then attached this zippped folder to your post.

**kurmugon** · 02-04-2007, 01:50 PM

Sure is snowing pretty here!
Here goes. I will attempt to attached the zipped spreadsheet. It should be for January sales (we're a little slow this time of year!).
Weel, this would have happened sooner but laptop elected to shut down in the midst of work.
Thanks again,
kurmugon
I may do this twice, well, three times,as program did not recognize me last effort.

**VBA Noob** · 02-04-2007, 02:15 PM

Hi,

Does this help

VBA Noob

**kurmugon** · 02-04-2007, 02:26 PM

Yes. It appears that everything has been set up in February, columns, formulas, etc. Great! Now, how can I do that? (I don't want to be cranking up this thread every month!)
kurmugon

**VBA Noob** · 02-04-2007, 02:39 PM

Right click sheet. Select copy or move. Tick create a copy and ok. Then change the date in C2 to say 1/3/07.

You will need to add a few rows for 29/3 to 31/3

VBA Noob

**kurmugon** · 02-04-2007, 02:58 PM

I am not doing something right. Instead of Sheet, I have February (nothing else). I right click on February and I get the little window that gives me the opportunity to Move or copy, then to select where it should go, and then to check Make a copy. When I do that, a sheet appears called February2, and it is blank. No formulas, not nothing. Somewhere I am missing a step?
thanks for your patience.

**oldchippy** · 02-04-2007, 03:00 PM

Hope the snow's not to deep, it's been 10°C here today!

This could be done a better way, but I think the easiest way forward with your spreadsheet is to go to the "JANUARY" sheet, then go to Edit > Go To > Click Special, select Formula's - this will highlight all the cell with formula's in them - these you need to keep. So that you can see which one's they are, with then still selected click on the Fill Colour icon (Tin of Paint).

Now you will see all the coloured cells you need to keep.
Click on the tab "JANUARY", hold the Ctrl key and drag the tab to the right, this will make a copy called "JANUARY (2)".

Now delete tabs Feb and March for now.

Go back to "JANUARY (2)", select the range C3:I17 and C19: I19 and C21:I21.
Press the delete key. These cells are now clear. Repeat this across the page for the other weeks.

Click on the tab again, and make a further 10 copies of the sheet as you did before.

Go back to "JANUARY (2)", double click the tab and rename to "FEBRUARY" repeat for other sheets so that you have 12 months.

You will have to adjust the dates and weekdays on each month to suit.

Now go to your summary sheet, select B1, in the formula bar where it say's = JANUARY!AM20 , you need to put this cell reference for each month, so B2 would be =FEBRUARY!AM20 etc.

Let me know how you get on - Good luck

**VBA Noob** · 02-04-2007, 03:06 PM

Try Edit > move or copy sheet > select Feb then tick the copy box and ok

VBA Noob

**kurmugon** · 02-04-2007, 03:12 PM

Thanks, Chippy!
I have printed your instructions and will start work on them. It may be tomorrow before you hear of my success, as it is now time for Duke Un. basketball and, of course, this evening, we have the Super Bowl. I am cheering for Chicago Bears, but no one else I know is!
Snow not deep, about a half inch, but very pretty, as we live on the side of a mountain.
thanks again,
kurmugon

**kurmugon** · 02-04-2007, 03:14 PM

VBA Noob: Thanks. Will also try this method!
k

**kurmugon** · 02-04-2007, 04:53 PM

Chippy:
Well, that certainly worked!! I thank you sincerely. Wondering, can I go through now and "delete" the January figures that were copied for the months, without harm?
Takes a little while, but not nearly as long as redoing each month and reentering each formula!

**oldchippy** · 02-05-2007, 07:09 AM

Originally Posted by kurmugon

Chippy:
Well, that certainly worked!! I thank you sincerely. Wondering, can I go through now and "delete" the January figures that were copied for the months, without harm?
Takes a little while, but not nearly as long as redoing each month and reentering each formula!

Hi kurmugon,

How did the Chicago Bears do, did they win?

Well, I'm glad you got your spreadsheet nearly sorted out. I've looked at VBA Noob's spreadsheet and he's more or less done the same as my post , but also sorted out the dates on the FEBRUARY sheet so that you put in the date 01/02/2007 into cell C2 (yellow) and all of the other dates are filled in automatically, including the days. If you click on any of the cells you will see the formula in the formula bar.

So if you use VBA's spreadsheet, you can make 10 copies of FEBRUARY, then rename each sheet and then put the 1st of the month into each sheet, into cell C2.

Don't forget the INDEX sheet formula's

**kurmugon** · 02-05-2007, 07:36 PM

Old Chippy, Sir:
No, the Bears did not win. The Indianapolis Colts won, 29-17. It was a rainy, soggy Super Bowl, and the Bears looked like they forgot it was the Super Bowl!

I will try VBA Noob's spreadsheet formula tonight on my spare copy-after I make another copy!

Thank you again for de-mystifying xl for me!

10 degrees C is a temp we will not see here for a while. Tonight, Monday, it may be -11.66 degrees Celsius. It is minus 3.8 C now, at 6:30 p.m.

Thanks again for your help. I will let you know how Mr. Noob's formula works for me.
kurmugon

**oldchippy** · 02-08-2007, 05:24 AM

Hi kurmugon,

Sorry to hear the Bears lost, better luck next time.

Your snow has now come over the pond and is about 4" deep here, haven't seen it like that for sometime.

Have you had chance to try out your spreadsheets? Did you get it sorted?

Moving formulas from month to month

LinkBack

Thread Tools

Rate This Thread

Display