Display Calendar from one row into one sheet

[myexcelstar]

Hi all,
I have a calendar file where I have all employees listed in one column and their leaves shown behind from E:NE. I would Need now a summary page (Sheet1) where I can enter an employee's ID number and it will bring up his/her Name (vlookup) and then list all the data from the calendar row of this employee (E:NE) and list it by month, one row for each month. I have attached my file so it might be clearer. There will be of Course more Sheets and more rows on each sheet.

Any help would be highly appreciated.

[Special-K]

I won't be answering this as I don't have time but...

1. You're gonna get problems because there are a varying number of columns per month, ie Jan 31 days Feb only 28.
2. People will say don't use merged cells and in this case they are right due to the varying numbers of columns however if every month starts on a mutiple of 31 columns then you can simplify the formula drastically as below.

Here's what I would do.

1. Give every month 31 columns (you haven't got to fill data in, leave the columns blank that don't have the extra days. Just make sure every month starts 31 columns along starting at column 5. That way you can use INDEX MATCH without bothering to adjust the column number for each month, e.g. Jan begins in column 5, Feb in Column 31+5, March in Column 62+5 (multiples of 31, see?).

2. The department name in Dep1 and Dep2 is a waste of time, Dep1 is (presumably) always Sales so why waste memory displaying the same data over and over again when it's never gonna change. Just have one sheet of employees naming their department as you already have and extend the list. That way you can do a simple INDEX MATCH as mentioned above. At the moment you're gonna have to find what sheet that employee is on using IFERROR(INDEX MATCH...,IFERROR(INDEX MATCH.., etc for each sheet. You'll then have to extend the formula as you add a new department. Placing all the employees on one sheet with departments listed means you only have to create the formula once and it never needs changing again.

[myexcelstar]

Hi Special-K,

thanks for your Feedback. I appreciate your comments on the file. I have changed the columns to 31 every month, that is not a Problem. I also removed the column "Department" in each sheet, however I cannot put everyone on one page due to confidentiality reasons. Managers are only allowed to see their own employees, hence the separate Sheets for each Departments.

Unfortunately, I am not good with the match and index Formula. Any assistance in how to write this Formula would be greatly appreciated? I attached the revised sheet again.

Thanks so much.

[Special-K]

1. Turn the formula calculations from manual to Automatic or the formulas won't update as you enter the ID number.

2. Am not sure what purpose the Date column serves in Tabelle1!D2. You have months already listed in column E and they will be filled when you enter an ID.

3.

in Tabelle1!B2
=IFERROR(VLOOKUP(A2,'Dep1'!A$3:B$1000,2,0),IFERROR(VLOOKUP(A2,'Dep2'!A$3:B$1000,2,0),"NOT FOUND"))

in Tabelle1!C2
=IF(NOT(ISNA(MATCH(A2,'Dep1'!A$1:A$1000,0))),"Dep1",IF(NOT(ISNA(MATCH(A2,'Dep2'!A$1:A$1000,0))),"Dep2","NOT FOUND"))

in Tabelle!F2
=IFERROR(INDEX(INDIRECT($C$2&"!$D$3:$NK$1000"),MATCH($A$2,INDIRECT($C$2&"!$A$3:$A$1000"),0),MATCH($E2,INDIRECT($C$2&"!$D$1:$MG$1"),0)+COLUMN()-6),"")
copy this formula across and down as far as AJ13



NOTE: Because of the employees not being on one sheet, as you add new departments you will have to add the sheet to the first two formulas.

So first formula format is (for two departments)
in B2
=
IFERROR(VLOOKUP(A2,'Dep1'!A$3:B$1000,2,0),
IFERROR(VLOOKUP(A2,'Dep2'!A$3:B$1000,2,0),
"NOT FOUND"))

and second formula is
in C2
=
IF(NOT(ISNA(MATCH(A2,'Dep1'!A$1:A$1000,0))),"Dep1",
IF(NOT(ISNA(MATCH(A2,'Dep2'!A$1:A$1000,0))),"Dep2",
"NOT FOUND"))

[Pete_UK]

I assumed that you would enter the Employee ID in A2 AND the Department in C2 on the Tabelle1 sheet - you could have a drop-down for the Department, as you will know all of these in advance, but the value in C2 must match the name of a sheet.

With that in mind, you could have this formula in B2:

=IF(OR(A2="",C2=""),"",IFERROR(VLOOKUP(A2,INDIRECT("'"&C2&"'!A:B"),2,0),""))

and I've also used this formula in E1:

=IF(OR(A2="",C2=""),"",IFERROR(MATCH(A2,INDIRECT("'"&C2&"'!A:A"),0),""))

Then you can put this formula in F2:

=IF($E$1="","",INDEX(INDIRECT("'"&$C$2&"'!"&$E$1&":"&$E$1),MATCH(DATEVALUE(F$1&" "&$E2&" 2019"),INDIRECT("'"&$C$2&"'!2:2"),0)))

and copy this across and down as required.

Hope this helps.

Pete

[myexcelstar]

Hi Pete
I tried your file, but in the table all cells have #Value. There seems to be a Problem with the datevalue part of the function!? The cell E1 can actually be ignored, I don't Need this anymore.

Hi Special_K
thanks, your formulas work fine!!! I really appreciate your help. I will test them now in the real file, but it Looks good so far.