PDA

View Full Version : consolidating the difference in counts on hourly basis



sindhuja
10-29-2009, 12:21 PM
Hello…

I have a sheet named "Default sheet" and another sheet named "Consol".
Now I will be updating hourly wise counts for all the persons in Default sheet.

On entering the next hour data say suppose I will enter 15 in A (at 6:30) then my count in consol sheet at 6:30 should be A value at 6:30 - A value of 5:30

For example : For activity one for a person A, A value at 6:30 is 15 and A value at 5:30 is 12.

Then the value of A for a person A in 6:30 is (15-12) is 3. Similarly I need the hourly basis counts for A and B for each activity for all the persons....

From the total I need to put the chart automatically….

Hope I made it clear…

This can be done maually but the data varies often… no of rows and column may increase or decrease daily….

Can this be done using macro…
Have attached sample sheet for the reference….

-Sindhuja

RolfJ
10-29-2009, 09:40 PM
Even after looking at your sample workbook I am not entirely clear on what you are trying to do (BTW: there is no 5:30 time slot in your workbook...). Could it be as simple as entering the following formulae in the 'Consol' worksheet:

Cell E3: ='Default Sheet'!E3-'Default Sheet'!C3
Cell F3: ='Default Sheet'!F3-'Default Sheet'!D3

and subsequently copying Cells E3 and F3 to the right and down?

Just wondering,
Rolf

sindhuja
10-30-2009, 08:24 AM
Thanks for the assistance !!

Since its an hour basis and as we start our work at 5:30 I have not included. Hence I start to take the counts from 6:30.

If it is between two sheets the below will work out.

Cell E3: ='Default Sheet'!E3-'Default Sheet'!C3
Cell F3: ='Default Sheet'!F3-'Default Sheet'!D3

But I will insert a new sheet daily for calculating daily counts in the same workbook.

Default sheet is like the template.

Now my basic need is I need the hourly wise counts for all the persons for all the activity…

Hope am clear now…

As I have to insert new sheets daily link between the sheets willnot workout I guess..
Hence I seek help in doing via macro.

Any immediate assistance will be highly appreciated.

-Sindhuja

sindhuja
11-02-2009, 09:48 AM
Any idea on this pls...

Bob Phillips
11-02-2009, 10:03 AM
Probably because, like me, we still have no idea what you are trying to do.

You need to work an example for us, at the very least.

What is Default Sheet as against Consol?

sindhuja
11-02-2009, 01:58 PM
Hi xld...

Default sheet is the sheet which I have created to get the hourly counts of the persons in different activity.

Default sheet contains total counts on hourly basis. For example on Activity A from 5:30 to 6:30 I have done 30 counts. I will enter 30 in 6:30 column of Activity A.

Then from 6:30 to 7:30 I will have done another 20 counts. But when I pull the report I will be give as 50. Hence As per report the counts will be 50 in 7:30 column and hence I will update the 50 in the Default sheet.

Now what I want is I need the exact count for each hour in the Consol sheet.

As stated above at 6:30 I have done 30 and at 7:30 I have done 20 (50 from Deafult sheet at 7:30 - 30 from Default sheet at 6:30) in the consol sheet..

I have tried using the Cell E3: ='Default Sheet'!E3-'Default Sheet'!C3 and Cell F3: ='Default Sheet'!F3-'Default Sheet'!D3. The challenge is I will be creating new worksheet daily say for 1st Nov, the next day 2 Nov and so on..on the same workbook…

If I use the same formula in the sheets which I add daily the existing sheet value also changes..so I need a macro to do this activity.

Default sheet is the sheet where in I feed the values from my report. I will enter the values only in the Default sheet for every one hour.... Consol sheet should calculate the counts from the Default sheet...

Hope I made it clear…..

-Sindhuja

sindhuja
11-04-2009, 11:20 AM
Any Clue on this....