creating consecutive dated (weekly) work sheets within a workbook

**bite_tony** · 04-18-2012, 05:42 AM

Hi, I am currently creating a spreadsheet for a weekly stock take at my work.
I want to know if Excel can automatically create new sheets within a workbook that will be dated as the monday of each week.
Also, can I make it so that the final totals in the last colum of one sheet(one week) become the starting totals for the first colum in next sheet (next week)

I can work excel, but i'm not computer genius..thanks for your help

**Mallycat** · 04-18-2012, 05:46 AM

Yes you can do this, but first consider if it is what you think you need, or what you actually need. Once you separate data on separate sheets, you can't analyse that data over time. If you put your data in a table (ie a table that contains date, product number, product description, quantity) you can execute time series analysis.

How about sharing what the end state is that you are after, and see if someone can recommend an approach.

If what you want is just a sheet that you can print out to collect data, then what you suggest may work. If you want to analyse the data later, then maybe a different approach is better

Matt

**Richard Buttrey** · 04-18-2012, 05:48 AM

Hi,

Yes, with macros Excel could be configured to behave like you describe, however why do you want a sheet for each week?

Tell us what your ultimate aim is, not what you think is the best solution since presumably you're here to discover that.

I'd urge you to consider a single sheet which contains all your data. Once you have this it is is much simpler to analyse and extract information, and you may find the standard pivot table functionality gives you all you need.

**bite_tony** · 04-18-2012, 05:52 AM

Having had a stern word with the boss, he's seen the light.
I'll be back soon to ask how to work pivot tables
Cheers for your help...

**Richard Buttrey** · 04-18-2012, 07:09 AM

Well done!

That's the sort of boss many would like

When you're ready post back. Don't forget to ensure there is a value in all key columns. i.e. columns that contain values like dates, product codes, account numbers - all the sort of stuff that you want to report against.

Also remember that you can use data validation to help with entering data where you want to ensure only certain values may be chosen - typically product codes, staff names etc.