formula linked to tab based on a cell

[SuddenImpact]

Hi all,

I have a spreadsheet that I fill out for every day of the month (New workbook each month). I have a totals tab that currently I manually update each day. Each tab is named with the date Ex: 4-1. So currently the formula for this week would be =SUM('04-01:03-31'!H13:H14).

I'm looking for a way to make this a little more automated, so I don't have to update the formula every day I make a new tab. Any ideas? Maybe on the totals tab itself, have the dates for the week, and somehow have the formula link to that?

Thanks!

[Richard Buttrey]

Can I suggest you rethink your approach and NOT have a separate tab for each day. You should capture your data on a single sheet in a straightforward 2 dimensional table. In your case use your existing columns but just add an additional column to represent the date.

Now continue to add your data row by row in the table. You'll now find it much easier to analyse/summarise your data and with a pivot table you'll find ways of analysing the data you've not yet thought of.

Neither would you need a workbook each month. Just keep adding dates to the same table.

[SuddenImpact]

Unfortunately I can't do that. Each day is basically a worksheet with start and end times of certain things, the number made, scrap material, material that needs to be returned, keeps track of maintenance that has been done, and all sorts of other things all mashed up into a nice printer friendly sheet that the supervisor is able to look at

[FDibbins]

That table or printout can still be generated from the "data base" that Richard is suggesting

[SuddenImpact]

Hmm... would I just make like a generic sheet that has the layout, then use a bunch of VLookup formula's based on the date, or is there an easier way?

[FDibbins]

that is probably the way to go, yes. If you have all your data in 1 place, extracting it in a meaningful manner is generally easy.

Mostr experts agree that you 1st design your data-capture (entry) sheet, then design the reporting systems afterwards. A well designed data base makes all the rest of what you want to do, so much easier

[Richard Buttrey]

Hmm... would I just make like a generic sheet that has the layout, then use a bunch of VLookup formula's based on the date, or is there an easier way?

VLookups would be one way, but having a 'proper' database opens up all sorts of ways including filtering (both Autofilter and Advanced), SUMIFS()/COUNTIFS() functions, INDEX(MATCH()) combinations and of course a pivot table.

If you need further help upload an example of your daily sheet and tell us what sort of information you want to retrieve from your system.

[SuddenImpact]

My only concern is how easily I'll be able to enter everything, and make sure nothing is missed. I attached a sample of what it mostly looks like right now...