Dynamischer Jahres/Montas und Einzelplan mit statistischen Funktionen

Ich würde das anders lösen.

Mit einer Pivot-Tabelle ist die Analyse der Daten viel einfacher.

Siehe zum Beispiel im Reiter Oeldere.

Ich habe diese Tabelle mit Power Query (Oeldere PQ) bearbeitet und dann eine Pivot-Tabelle erstellt.

So können Sie die Daten Z.B. auch wöchentlich Analysieren.

Es ist wahrscheinlich nicht die Antwort, auf die Sie gehofft haben, aber schauen Sie sich diese Option ernsthaft an. Wenn Sie eine Pivot-Tabelle beherrschen, hat die echte Datenanalyse begonnen.

Wenn Sie Fragen haben, lassen Sie es einfach wissen.

Dear Heiko,

I made the sheet Oeldere since the data you use is not Pivot Table proof.

To use Pivot Table we need the same informatie in rows instead of in columns.



So the sheet with the name Oeldere is a (copy of) your sheet Jahresplan with the month januar.

That is why I made an sheet Oeldere PQ, to get the data in the right format.

The result is the sheet Pivot Tabelle.



Of course you can add all year in this format.

After that you can analyse the data the way you like (day / workday / week / month / quarter / year / other period.

I made this to give you just an idea wath is possible with Pivot Table.

You ask if it is dynamic => yes it is.

I used just 1 month for testing proposal.


If you like the idea of Pivot Table I will help you.


Just another Pivot Table with the same data (in this case I did not refresh the data).

I changed the format of the sheet Jahresplan.

There are no empty rows in the sheet Oeldere.

We need the data from row 3. This will be the headers of a table.

Since it was just Januari f3 has the value 01.

If you want to select on a year / month / day you have to add this data in a cell.

So cell F3 becomes 01-01-2022, G3 becomes 02-01-2022 etc.

So you can also add the data of februar etc in the sheet Oeldere (the sheet with the data).

Sheet follows.

"What I definitely need help with is how to generate the pivot tables that you have dynamically from the JAHRESPLAN."

See the attached file.

The original data is in Sheet Oeldere.

Change / Add a value in Sheet Oeldere.

After that go to sheet Oeldere PQ new.

Data => refresh => refresh all => the new data in the sheet Oeldere are also in the sheet PQ new.

After that go to sheet Oeldere PT new.

Data => refresh => refresh all => the new data in the sheet Oeldere are also in the sheet PT new.

I also need the pivot table to be able to display every shift (F, M and S)

1) Already available (see sheet Oeldere PT new).


as well as the weekends (Sa and So)

2) Added in the PQ, and available in the PT new (see sheet Oeldere PT new).


plus the sick days (K), the holidays (U) and the days off (blank field)


3) Add the information in the "normal" data with the K, U (Krank and Urlaub).
Also add the normal hours in this data.
(see sheet Oeldere PT new).


"Is there a way for "Oeldere" to automatically get the data people enter in "Jahresplan" as this is supposed to be the main plan people are working with or do I need to focus on "Oeldere" now?"

4) I made the sheet Oeldere since the data you use is not Pivot Table proof. In the end the data needs to be Pivot Table Proof.
Off course you can add color etc for viewing proposal.
But for now just focus on a pivot table solutions.
After that we can instruct what can be done on the sheet Jahresplan.




See the attached file for the add with workday.

Please explain what the use is off the row 47 till 75.


1) "It would be great if the plan was laid out for the entire year. I added a "year" component in the upper left corner so that the plan can easily be switched to another year."

I don't think that will be a good idea.

Probably we use a sheet for each year and add all data together for analysing porposal.



2 "I would like to display any missing people as a big red number."
That people are in the table, so that is not a problem.

Or did it use a standard employeelist and fill the sheet Oeldere?

For the first part of the question, you can implement the original formula in Sheet Oeldere.

But it is only a planning tool, or not?
Is there a need to get those data in the sheet Oeldere PQ new?


There is a tab "Daten" which lists all of the employees. Is there a way that this data gets put into the oeldere plan automatically depending on the changes you make in the tab?

all employees are in the sheet Oeldere (which is OK for me), so I don't understand the question.


You can now use the sheet Oeldere PT new (Pivot tabelle) for analysing the data, you earlier did.


If you expect more help from me, you have to be more specific.

"all employees are in the sheet Oeldere (which is OK for me"

You really need all data (also for the employees who left the organisation) in the sheet Oeldere.

If you delete those data or not add this data in the analyse => the data becomes invalid.
The results will not be usefull.


e.g. you delete a employee in April and May.
If you make a pivot table the employee is not in the data, and therefor not in the pivot table.
You will notice you have a shift with e.g. 6 people (which was original 7 people).



A worksheet with the employees can be usefull for analysing that data (e.g. leave / grow / age)

"You really need all data (also for the employees who left the organisation) in the sheet Oeldere."

You can hide the employees (in the sheet Oeldere) who left the organisation.

That will not have a negative influence on the pivot table (I just tested it).


See the new sheet with the expand of the data to a whole year.



Maybe we have to look at an easy way to select the current week (for input the shifts).
This needs to be solved / no solution for that on this moment.

Hey there,

i am attaching the new version of the plan....a version I have been working or playing with for a bit ;-)....if you wanna make any changes, please take that one or I would have to do all the graphical formatting again ;-)))).

Since you drew my attention to pivot tables, I went ahead and made a new tab "DASHBOARD NEW" to give me an overview of the individual shifts plus the employees of the Pool shift.

Is there a way for me to tell Excel to count the holidays (marked in oeldere down below with purple) an employee has worked and have Excel display it in the pivot table?

I also added a new tab "PLAN_MITARBEITER" which is supposed to be automatically filled out by Excel depending on what employee number you choose in the upper right corner. I don't quite know how to do it tho.

One tab that is very important is "LEISTUNGSNACHWEIS" which is supposed to take the information of the shift you choose in the drop down and displays the shifts of the individual group in the plan. If you could help me with that one, that be great.

I think I can delete "Einzelnachweis" since I can get the individual information of people from the Pool shift with the help of pivot tables.

If I wanna go ahead and already prepare the plan for next year, what exactly is it that I have to do? Make a copy of the plan and simply change the first day in January to the next year?

Is there a way for me to tell Excel to count the holidays (marked in oeldere down below with purple) an employee has worked and have Excel display it in the pivot table?


See my comment in #10
3) Add the information in the "normal" data with the K, U (Krank and Urlaub).


If I wanna go ahead and already prepare the plan for next year, what exactly is it that I have to do? Make a copy of the plan and simply change the first day in January to the next year?

Make a new sheet with all data.

Or

Add the data after NF for the year 2023.

#17 "Maybe we have to look at an easy way to select the current week (for input the shifts)."

That can be solved showing the data from left ot right (now there are showing right to left)

Add each week the current week from G3.
The earlier data are from left to right.

Advantage
You have the actual data in front of you.


Disadvantage
You have to add each week / day new columns to the table.

post quick reply (forum issues)

"Are you referring to the issue with the "PLAN_MITARBEITER"? I d would like to add a drop down to either PERSONALNR or NAME so that Excel then pulls the necessary information from the JAHRESPLAN."

No I refer to the sheet Oeldere.


"3) Add the information in the "normal" data with the K, U (Krank and Urlaub)."

Add the data with H8 (H = Holiday) and hours in the data sheet. Then they also be part of the pivot table.

"If I used another abrreviation like H8 I would get rid of a shift and it will not get listed or counted properly further down below. I basically wanna tell Excel "Hey Excel, take a look at all the days of a month an employee has worked and check if one of those days was a holiday (which are listed in the tab FEIERTAGE ) and then count them days."


M2=IFERROR(VLOOKUP(F2,tbFeiertage[[Feiertag]:[Gesetzlich]],2,0),"")


Now you can count the Holiday in the Pivot Table.


#17 etc
See the sheet New Format Of Data for an example of what I meant.


See the attached file.

Ahh ok I think I got it now. All i gotta do is add a new column to the table in OELDERE PQ, call it Feiertage and use the formula you provided.

M2=IFERROR(VLOOKUP(F2,tbFeiertage[[Feiertag]:[Gesetzlich]],2,0),"")

I think he meant put

=IFERROR(VLOOKUP(F2,tbFeiertage[[Feiertag]:[Gesetzlich]],2,0),"")

in M2...

OK. I'll look at the file as well in a moment or two. Coffee helps!!

Ahhh... Pivot Tables... I haven't used them in years. Oeldere will sort you out when he gets back, assuming I mess up.

But... a guess.

Add the formula in M2. Go to the new Pivot Table sheet. Data/Refresh and the new column will be in the Pivot Table field list to use whatever way you want.

For your 2nd question in #33

"#17" "You really need all data (also for the employees who left the organisation) in the sheet Oeldere."

You can hide the employees (in the sheet Oeldere) who left the organisation.

That will not have a negative influence on the pivot table (I just tested it)."

Add the last version of your file, so we can test it (question 1)

The sheetname Oeldere is just for the test.
Of course it can be renamed in your version.

you formatted the cells "Standaard;Standaard;;@"

I changed the format to "standaard"

After that I changed the formula

E9=INDEX(Tabel3_3[#All],MATCH($E$9,Tabel3_3[[#All],[Date]],0),7)

See the attached file.

Show the expected results manualy in the excel file.

"I noticed tho that something is wrong with the month dropdown. I had it set up watching a youtube tutorial ;-) and hide it in AQ. When I click on the drop-down menu now I get Januar, Februar and March and then all the months again which is kinda weird."


1) Please do not hide data. At least during the test period.
More in general I should not hide the data (since it makes it a lot diffecult to understand the file).

2) More in general do not work with merged cells (e.g. AF6). You get in trouble with it sooner or later.

3) I work normaly with colors, so I can see, based on the color what action is expected (blue is input / yellow is formule)

4) Leistungsnwchweis new
AW12 = 01-01-2022.

After that the drop down will work.

I deleted the input in AQ8 : AQ11.


See the attached file.

here the changed file.

I did some tutorials on INDEX and MATCH and was able to use it for the LEISTUNGSNACHWEIS NEW to pull the shift information of each employee listed from the annual plan (oeldere).

I m still trying to fix the columns PERSONAL NR. and X-SCHICHT as they are still using formulas that are not compatible with the Excel version I got running on my work station.

I also want Excel to count the sick days (K) and the vacation days/extra vacation days (U and SU) for the month in question.

Another issue that is still bugging me is the conditional formatting part on OELDERE where cells (rows 60 - 63) are being compared with the rows 55-58. Any discrepancies are to be color coded. Whenever the cell value is the same, I want the white background/black font (no shift holes); when the cell values are less then the ones above (employees missing, shift holes), I want them to be color coded red background/white font. And whenever there is a surplus (more employees than actually needed) I want the cell to be green background with white font.

I also got rid of the tabs we no longer need.

1) "I m still trying to fix the columns PERSONAL NR. and X-SCHICHT as they are still using formulas that are not compatible with the Excel version I got running on my work station."

Why not update to the new version?

I looks a good alternative, instead of using old formula.


2) "I also want Excel to count the sick days (K) and the vacation days/extra vacation days (U and SU) for the month in question."

Add an value after K and U. See Oeldere!V31


After that refresh Oeldere PQ

After that refresh Oeldere PT new



See the attached file.

A13=IFERROR(VLOOKUP(ROW()-12&":"&$B$12,$AV$13:$AZ$52,COLUMN()+3,0),"")

German => A13=WENNFEHLER(SVERWEIS(ZEILE()-12&":"&$B$12,$AV$13:$AZ$52,SPALTE()+3,0),"")

And drag to column B.

After that drag A13:b13 til A24:B24

See the attached file.

Note:

I would add those data (AV13:AZ52) in a separate sheet.

That would it make much easier to change the file.

A13 => A13=ROW()-12 => 13 -/- 12 = 1

B12 = D

&":"&$B$12

So the result is 1:D

We use 1:D in the Vlookup formula.



A13 => COLUMN()+3 = column(A)=1 => 1 + 3 = 4

So in the VLookup formula use the 4th column.


We use that so we can drag the formula to the right.
Column B =2 => 2+3 =5 => is the 5th column.

Hope I explain well enough.

So far so good...well I guess the plan is almost finished :-) thanx to you oeldere :-).

There is one little thing I noticed. I do have statistical data on DASHBOARD NEW which gives me information regarding shift hours, sick days, holidays and all that kinda stuff.

The employees of the POOL group have flexible working times which means they do cover regular shifts (F8, S8, M8, F12 and S12) but also regular Pool times (P1, P2 .....P10). If you check out DASHBOARD NEW and take a look at the statistics on the very right "Pool Gesamt" you can see that regular shifts with 8 and 12 hours are counted together.

Pool shifts are not counted together. So while shift hours get a SUM treatment, the regualr pool times get a COUNT treatment. Is there any way I can tell Excel to regard all the P1, P2 ... P10 as 8 hour shifts without changing their acronym in the plan?

I m attaching the latest version of the plan. It is no longer a .xlsm coz I got rid of all unnecessary macros that we are not using anyway.

VLookup table added in sheet "Daten"

column I added Uhr (def)

I2=IF($G2="P",VLOOKUP($G2,Daten!$L$19:$M$30,2,0),$H2)

You have to make the analyse of column Uhr (def).

See the attached file.

For some reason my formula is not implemented well.

Come back with the changed one.



See the Pivot Table on sheet Dashboard new.

I get the correct data in the graph.

So I think you need to set up your graph again.

Please try it an comment back.


See the attached file.

1) Change F8 into F6 (8 hours / 6 hours)


2) O2 (jahresplan PQ) =IF(OR($C2="A",$C2="B",$C2="C",$C2="D"),0.33,"")

P2 =$I2-$O2



See the attached file.

"I would like to add BR (for Betriebsrat) to the plan"

Add B8 in Jahresplan and refresh the data.

I tested it with B16 for Maier, Bernd on 03-01-2022.

See the attached file.

The first question (3,50) can be solved.


The second question

Conclusion

1) A B C and D don't get payed.

2) Poolworkers will get payed

If so, we need to implement a Vlookup formule (on the names of the Poolworkers)
Other option would be on WorkersID but there is no logic sence in that data so that can't be used.

We can use the table on sheet Daten (for the poolworkers?)

Or could we add another vlook table to the "DATEN" tab...or add to the existing one...and then add another column on JAHRESPLAN PQ ...when G2 is S, or M, or F and H2 is 8 or 12, then add 0,33 to that column? Could that work?

Basically whenever people work regular shifts such as F8, F12, S8, S12 or M8 they always get the 0,33 briefing time extra on top. However that only applies to those shifts and not the P1, P2 and so forth.

Will study that in the evening.

1) The schicht (column C in the Jahresplan) is prefilled, so for the Pool workers we need to define, under which conditions they get also briefing time.

So for this one, I don't have a solution yet.


2) JahresplanPQ (cell P2=$I2-$O2).

So the briefing time is abstracted of the working time and not added to the working time.

Should it be added or abstracted of the working time.


3) Sheet Daten cells L19:N30
I added in column N the information of the abbreviation


4) The last code in the PowerQuery is:

Please Login or Register  to view this content.

I changed the red text so you can work also with 3,50 etc.


See the attached file (nr 14).

Please number the questions / suggestions. It makes it a lot easier to refer to.

1) Jahresplan PQ (PowerQuery) P2 =$I2+$O2


2) already there, if not reply back.


3)

Workers of the "Pool" category do not get briefing time added to their regular working hours if they work all the others shifts (P1, P2 and so on).

Will the Pool members also fill in F8 / M8 / S8 / F12 / S12 for this kind of jobs?
If so we can use that criteria to determine the calculation (briefing time)


4) Yes, Power Query is used to get the data re-arange (of the sheet Jahresplan).

I don't know if there is a translater of the data for PQ.

But maybe you can open it on your own computer.

Below a video how to get to the editor.

How to paste Power Query code ( by Olly)
https://excel.solutions/2017/11/powe...te-code-video/


5/6 show in an excel file how the result should look like.

1) Yes, I already put that in the plan we are using right now. I already updated that version with the proper personal numbers, names, shifts and so on.

2) I think that is the formula we are using right now to put some sort of restriction on when the "briefing time" will get paid.

=WENN(ODER($C2="A";$C2="B";$C2="C";$C2="D");0,33;"")

Here is a thought...How about we rewrite the formula to look like that:

=WENN(ODER($G2="F";$G2="M";$G2="S");0,33;"")

This way whenever anybody is working a F8, F12, M8, S8, or S12 shift, they get the briefing time. I think the first letter of the shift abbreviation is all we need since
we already set up the Pool shifts with P1, P2 and so forth. Do you think that could work?

3) See number 2 (I hope number 2 was referring to the briefing time as well. I m confused.

And yes, I will number everything from now on :-))))

4) Ok, I followed the instructions in the tutorial video you linked.

- I clicked in the JAHRESPLAN PQ table....or am I SUPPOSED to click in the JAHRESPLAN? (Never done any PowerQuery thing before)
- Data - Get Data - From other sources - Blank query
- In the Power Query Editior i hit the advanced editor button
- I pasted the code in and changed the ?bertrag thing to ?bertrag
- Now I have the JAHRESPLAN PQ tab plus a new one with the query I did. Do I just copy the missing stuff over from the JAHRESPLAN PQ to the new tab? We added Feiertag,
Briefing and Uhr Netto.

Screenshot_1.jpg
Screenshot_2.jpg

You said you changed the text so I could use 3,5....only 3,5 or also 4,5...6,5 and so forth?

5/6 I ll set up an example Excel file to show you what I mean.

2 / 3

=WENN(ODER($G2="F";$G2="M";$G2="S");0,33;"")

And yes, I will number everything from now on :-))))

This one will work if you use that format also for the Pool workers (on the days that briefing time needs to be added).

4) I showed you the link so you can see where you could find the used code (just for your information).

5/6 I wil take a look later.



See the updated file (except 5/6).

4) yes it mathers.

You need to choose the table in the Jahresplan. That is the source table. The sheet Jahresplan PT is the destination table.



7) I you use my example in #70, you will see the input of S3,50 will work (see date 02-01-2022)

You need to stick to a certain letter since you will use this letter for other data analyse.

4) Ok, so once I use it in the JAHRESPLAN tab, I do get a new tab called Abfrage1. Am I supposed to combine the 2 queries somehow? I always created the Pivot tables for the DASHBOARD NEW tab using the table in the JAHRESPLAN PQ tab, I never touched the JAHRESPLAN PT NEW.

7) So far we are using the following abbreviations:

F8, F12, M8, S8, S12, U, K, SU, P1, P2, P3, P4, P5, P6, P7, P8, P9 and P10.

Any other shifts I would like to label with a T.....so T3,5 or T4,5 so we will know it is out of the ordinary.

I noticed, when I put 2 times S3,5 or T3,5 in the October plan that Excel does not count them together properly in the DASHBOARD. In one Pivot table it gives me back "?bergabe" 1 and amount of hours 8...In the other Pivot table it counts those 2 shifts as 9.

sh_plan_1.jpg
sh_plan_2.jpg

If I type in two times F8, it gives me back "?bergabe" 1 and 16 as the amount of hours. In the other Pivot table it counts those two shifts togehter as 17. I am bit confused.

What I always do before I work in the DASHBOARD tab, is to update the data in the JAHRESPLAN PQ and JAHRESPLAN PT new.

What makes it even more confusing is that I need to somehow move the changes you make to "your" version of the plan to the one we are using without compromising the integrity of the personal data which I already entered in the plan.

How can I tell Excel to count certain abbreviations together in a certain way.....U, SU (special vacation) and K -> I just need the count...so 2 vacation days or 5 sick days. I do not need the amount of hours.

F8, F12, M8, S8, S12 -> Excel needs to count them together as either 8 or 12 hours
P1, P2, P3, P4, P5, P6, P7, P8, P9 and P10 -> Excel needs to count them together as 8 hours...always

And when we have the extraordinary shifts T3,5 or T4,5 i want Excel to count them togehter regularly so 3,5 plus 4,5 for example.

8) Can I use PowerQuery to change the column "?bertrag" into UA (Urlaubsanspruch) since Excel obviously does have some difficulty with the German Ue (?)?

refering to #73

4) See in the sheet (vs 16) in #70

It seems to me you are using an "old" version of the data.

There is no sheet "Abfrage" in it.

There is no sheet "JAHRESPLAN PT NEW" in it.


4A) So back to basic.

Use the same file as I use and ask the questions, so it can be solved.
After that implement that in your original file.


7) using the T is a good solution. We are implemented that one.
Only the SU (Sonderurlaub) we need to test.
I prefer to use 1 letter for this task.
Maybe you can use X for that.


8) You mean "Umlaut"

You need to make sure you use always the same name.

4) The new version is 17

4a) There is no sheet "Abfrage" in it because I was testing it on a test file just to see what it would do.

first the basic, after that we can expand the questions.


4b) I m even doing an Excel PowerQuery online course right now to better understand the system behind it.

Great to read that.


7) shifts on sheet Daten

B 1 Work council
F 1 Fr?h
K 1 Krank
M 1 Mittag
P 8 Pool
S 1 Sp?t
T 1 Training
U 1 Urlaub
W (instead of SU) 1 Sonder Urlaub
X 1 other shifts


8) I see "?bertrag" correct in my file.

The forum has issues with that when we type it in our threats.

But in your file it also has to be "?bertrag".
What are your local setting (German or English); maybe it has to do with the local setting.

9) No, i did not, see also 4a).

I will take a look at it, after I'm sure the basic is correct and accepted.


See the attached file.

4a) "When I tested it on my example file, Excel generated a new tab called "Abfrage". Like I said, just for testing purposes to see what is going on in the background."

Did this happen when you tested on file 17?


7)

Instead of W, could we maybe use U+. My co-workers and my boss will be a bit confused to use W :-). Is it difficult to use SU?

We choose all 1 letter and use that in splitting the data (letter / value).

It would be easier to have one Letter (W can also be Z or another not used letter).


8)

My settings are all in German. I noticed it when I opened up the query you did. Maybe because your settings are set to English.

Mine are set up in Dutch, but I expect the German version should be able to work with "Umlaut".

8a) you have problems using "umlaut" during normal Excel work.

if so, than it has to be a problem during Power Query.

9) -

10) Yes it is just to test

We can use VLookup to get the hours of the Pool employee.

7 / 8 Invalid Attachment specified. If you followed a valid link, please notify the administrator

Can't read the (both) attachment.


We use that information, with vLookup to fill column I in Jahresplan PQ.

I2 =if($G11="P",VLookup($G11,Daten!$L$19:$M$30,2,0),$H11)




8a) Strange, then it looks a Pivot Query issue.



9) We have to make sure, we get all data right in Jahresplan PQ.

In that case we can use that table to analyse the data (with e.g. a pivot table).

Don't know what the forum did with the attachments lol. Let me try again

7)

I2 =if($G11="P",VLookup($G11,Daten!$L$19:$M$30,2,0),$H11)

So Excel basically looks if there is P in G11. If there is, it looks for P in the data table on tab DATEN in the second column and gives back 8. Correct? ;-)

So that is why we should never use P7,5 because Excel will always turn it into an 8. Instead we should use X7,5, right?

So can we use U and U8 for regular and special holidays?

8) see attachment down below

No, same failure.

Probably you don't add the attachement like you do with the excel files.

Try it that way, i guess that will work.

Already did :-)

11) Regarding question #11 and the tabs that are all necessary for the plan to work properly. Do I need all the other tabs as well (Jahresplan PT new, Pivot Tabelle and Oeldere PQ old)? I wanna do some clean-up.

12) New question: Could I change the Workday column in the PowerQuery window to Montag, Dienstag, Mittwoch....so all the days of the week in German. You know my Dutch is pretty bad ;-)

7)

I2 =if($G11="P",VLookup($G11,Daten!$L$19:$M$30,2,0),$H11)

So Excel basically looks if there is P in G11. If there is, it looks for P in the data table on tab DATEN in the second column and gives back 8. Correct? ;-)

So that is why we should never use P7,5 because Excel will always turn it into an 8. Instead we should use X7,5, right?

Above statement is correct.

But maybe we sometime want to use the correct hours => in that case we use column H (uhr) instead of column I (urh def).

So can we use U and U8 for regular and special holidays?

I would not do it that way, work constructive and use always the same patron.
That is why I suggest to use W (or another letter for that).



11)

11) Regarding question #11 and the tabs that are all necessary for the plan to work properly. Do I need all the other tabs as well (Jahresplan PT new, Pivot Tabelle and Oeldere PQ old)? I wanna do some clean-up.

in my version 17 the data is cleaned up.

I don't know which #11 you refer to.



12) I have to take a look if I can change the code below into a german version.

Please Login or Register  to view this content.

"I was referring to the TABS we have in the file, not the data. I was wondering if we could simply delete all unnecessary tabs. There are still Jahresplan PT new, Oeldere PQ (old) and Pivot Tabelle. I dont think we need them or do we?"
Those tabs are NOT in version 17, so they don't have tot cleaned up.


In version 17 we have Jahesplan PQ / Jahresplan / Pivot_Pool_Schicht_Gesamt / Dashboard new / Leistungsnachweis New / Plan_Mitarbeiter /Daten / Feiertage

Did you get a chance to look at the example file #69?

This is an empty file, so what needs to be done. Show the input and the expected result.

See the attached file (from #69).

Yeah because the other ones I mentioned are not visible :-).

It would be nice if you have told that, since I don't expect that.

Where do we find the table how much shifts are needed for F8 / M8 etc.

I expected this on the sheet Daten, but there is also no table with this information.

Ok, had an #Wert error before and then realized it was because I was substracting 0 from 0 :-).

This is what I got so far, which is working fine. Just wondering if there had been another work-around without having to set up two auxiliary tables.

Then I want Excel to compare the result with the table in the JAHRRESPLAN (highlighted in the screenshot) and give me back the difference, so I know which shift is needed where.

I expect you need the NOT highlighted values in the sceenschot (SOLL) instead of the highlighted values in the screenshot (IST).

You can just subtract (SOLL -/- IST).


But since you already have those data on the sheet Jahresplan, why is there a need to have them in another sheet (visual proposal)?


If so, implement this in your excelfile you posted.
You expect me to compare differant sheets received on differant dates.
I'm willing (and able) to help you, but please help me, helping you.

I know the shift difference is already in the plan, however, for data protection purposes my co-workers wanted to have it displayed on a different sheet so people can get an overview of the shifts that are still free without noticing any other information assigned to their co-workers.

Here is what I came up with, which looks pretty nice in my opinion ;-). What do you think?

I m still wondering, however, if I can simply delete all the other "unnecessary" tabs (invisible) in the excel file. I m guessing Jahresplan PQ is the only one I really need, right?

See #89 "In version 17 we have Jahesplan PQ / Jahresplan / Pivot_Pool_Schicht_Gesamt / Dashboard new / Leistungsnachweis New / Plan_Mitarbeiter /Daten / Feiertage"

I expect those sheets needs to be available. Other sheets can be deleted.


Edit

Don't hide rows and columns in your file (at least not in the testing fase).

I don't like to seek where the excepted data is available (not, since there hidden).

Here is what I came up with, which looks pretty nice in my opinion ;-). What do you think?

I should calculate the data with a formula on the Jahresplan tab.

After that I would refer to the Jahresplan tab to get the results.



Advantage: the same calculations will only calculated once.

Addiotional advantage, you don't need to hide rows.

yes, that is how I should do it.

I edited this post. I somehow managed to get an error message while trying to set up the new Dashboard. I just went back to the previous file and started over.

Could you take a look at STATISTIK, which is supposed to be the new DASHBOARD and tell me why I can't have Excel display the amount of Feiertage (holidays) in the last column.

And with the 4 pivot tables I have, is there a way to combine them into one?

@fullenchilada

You started with an ld sheet. So there could be faillures in it, which has been solved in later editions.

Please work systematic.

And don't ask another question before the earlier one is solved.

You did not used the solution in #99.



So I think it would be easier starting from the excel file in #76 (version 17)

Delete all hidden sheets in this version.

Unhide all hide row in the sheet Schichtenbeleg.

Change the formula on Schichtenbeleg (or use copy / paste if the employsee are not allowed to see the formula).



Feiertag
Feitertag in filter box and select with x.
Uhr (def) in value box and format on 2 decimals.


And with the 4 pivot tables I have, is there a way to combine them into one?
What is the use of that, since you show differant calculations?

Did you changed the file according to #101.

If so, you could add the excel file.