Multiple Sheets into Summary Sheet 2

[Topcat04]

Hi there

I have been asked to help a friend with a solution for recording hours worked and pay calculations.

They get sent a sheet each week with data corresponding to Employee number, Hours worked, holiday pay, sick pay, Total hours, Total Pay and Date.

What they want to achieve is to each week save the data to a new worksheet and name that work sheet the week of the tax year. Nothing too complicated so far and easy to do - although having 52 worksheets I can not see the point but they are insisting this is what they want to do.

The real question is they want a summary sheet of the same information that accumulates each week (each time a new sheet is added)

What is the best way of being able to accumulate information from each weekly sheet? The sheets would be named wk01 wk02 wk03 and could be referenced to an actual date?

Hope this is explained well enough in text, I was looking for a way of embedding and example but was unable to find it.

Thanks for the help and guidance,

 

[JPaules]

I wouldn't want 53 tabs either but...

Assuming you want total hours for each week on a summary page:

Go to the cell on your summary page where you want the inform from wk01. Type = then go to wk01 and click on the cell (pretend its in cell A6) with the number you want on the summary page.

That will yield something like =wk01!A6




--
JP

 

[SkipVought]

Check out something like
[tt]
=SUM(Sheet1:Sheet52!A1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Thanks for the posts.

Is there a way of being able to do it if the Tabs are added one by one, or is the only real way to pre create the 52 tabs?

Thanks again,

 

[xlbo]

Slap 'em over the back of the head with a wet fish and tell them that the restriction they are placing on having 52 / 53 seperate sheets is ridiculous

Each week, the data from that wee should be pasted into a consolidation worksheet with a week identifier

that will make the summary sheet a breeze

If they want to have the 50 odd seperate sheets that's fine (I'm guessing for reference / data integrity) but there's no reason you can;t copy the data from those sheets into a consolidation sheet and aggegate from there

trust me - you will be making your and your friends life a LOT easier ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

I have to agree with all that xlbo says.

However, you say:

Is there a way of being able to do it if the Tabs are added one by one, or is the only real way to pre create the 52 tabs?

... and using Skip's example as a starting point:

Check out something like=SUM(Sheet1:Sheet52!A1)

... have a sheet at the right called EndOfYear, being a blank dummy sheet, that you insert the new tabs before, as they are needed. And then do like Skip says, but pointing at the EndOfYear sheet as the end of the 3d reference, like this:

=SUM(wk01:EndOfYear!A1)

So, no need to precreate the 52 sheets.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Scott24x7]

Glenn,
That's clever. I was just reading this thread out of interest, but never realized you could do it that way... sweet. Gave you a star.
-S

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."