If OB AHT = "" or 0, the value of Full AHT is IB AHT.
If IB Tasks = "" or 0, the value of Full AHT is blank
Yes I thought so, too. I'm just not sure how to go about transferring the data from the Pivot Table to a table most especially if there are missing hours in between. Like for example I ran 9AM and was not able to run 2 consecutive hours then I ran 12PM, PT will look like this:
Hourly Table IB Tasks ........
9AM
12PM
But then the table will look like this:
Hourly Table IB Tasks ........
9AM
10AM
11AM
12PM
Do I create a Pivot first then the loop to create sheets and tables?
Give me some time… I'm not full-time on this.
What I've done in the past was to use VBA to create a temporary PT on a temporary WS to do all the heavy lifting and then by selecting the appropriate page fields to 'filter' the data I could create the final reports
Along the way, I created any needed 'derived' fields for parameters that were not in the source data
Delete the temporary WS and I was done
That's just one way
Sometimes I created a temporary WS with a copy of the source data and any auxiliary values to make the PT easier to use
While trying to implement the above (Full AHT2 in the pic) I got this as opposed to the original Full AHT in the pic.
Hopefully Full AHT2 is correct; can you confirm?
Attachment 25796
edit post posting:
Now I'm getting the feeling you meant to say:
If OB AHT = "" or 0, the value of Full AHT is IB AHT/IB Tasks
?
1. While creating the 'Overall' table at the top of each Lob sheet I note that the Start time (SOO column on the Database sheet) is the same for each LOB - will that ALWAYS be the case? (I'm trying to avoid looking for the latest time in a given LOB.)
2. The remaining tables are those listed in the Sub LOB for each LOB on the Database sheet; will there ever be additional Sub LOBs in the Consolidated sheet that aren't listed in the Database sheet?
3. While thinking about that, will there ever be LOBs on the Consolidated sheet that aren't in the Database sheet?
(Hopefully, you'll tell me that the Database sheet is created from the data on the Consolidated sheet and that ALL the data on the Consolidated sheet is used in creating the data in the Database sheet)
First of all I would like to apologize, I didn't mean to make you feel like I'm hurrying you.. I was just trying to analyze as well :(
That formula is correct.
1. No, the overall table's SOO is always the SOO of the first sub LOB indicated per main LOB. Or maybe we can just add another Sub LOB saying Overall so it will be easier as we will still use the code that you wrote above. Anyway I will also put a column for headcount for each LOB so I think I'll just put another Sub LOB saying Overall
2. Yes
3. No
I'm aiming to produce two solutions, one with pivot tables and one with just formulae. This will allow comparison and if they're the same all well and good, you'll have a choice. If they're different then some detective work will be needed.
If OB AHT = "" or 0, the value of Full AHT is IB AHT/IB Tasks
Otherwise, the formula is IB AHT + (OB AHT *(OB TASKS/IB TASKS))
I noticed a difference between some pivot table values and plain formula values and drilled it down to the Consolidated sheet cells C377:C458. These cells are different in that they contain date information as well as time information, while the other cells contain only time information.
In the working model which will it be?
Full AHT is the one that's correct.
My column A is Date and Time concatenated. I can remove that I think there's no actual use for that.
My column B has to be just Date.
And my column C has to be just Time.
Maybe I accidentally changed something.
Attached
You are amazing works like a charm :bow::bow:
How does this site work, to acknowledge your help?
Also, is there a website that you can recommend me to learn VBA? I want to be like you :bow:
I need to add a few more tables that have different headers but I will study your code first then I will post back if I can't get something to work :)
How do I add a table at the end of the very last table? This table has different headers than the ones for Sub LOB. I tried to do the same as the Overall Table but it's either:
1) being added after every table per sheet
2) being added just after the last table of the very last sheet
Also, is there a way to merge two cells of a table?