0

I have a "working days" Excel file with two sheets. In the 1st sheet I had three columns with type, surname and department (of employees), the days and date of the month. Below the days-dates columns, I "check" the days that employees are working with the number "1" (it could be a tick).

Input example

Is it possible, when I check in the sheet 1 an employee at a specific date with number "1" (or tick), to automatically fill, in the 2nd sheet, the data from type, surname and department in each column (sheet 1 B,C,D to sheet 2 B,C,D and E,F,G)?

Output example

Improve this question

1 Answer 1

Reset to default
0

Since I see you only want to see "Employees" and their "Managers", I created a few formulas for them. I am not sure how would I make it work with "Head managers".

(Note: change Sheet1 with your actual 1st sheet name. Use single quotes if your sheet has spaces, e.g. '1st sheet')

B3 (Enter to enter the formula):

="Manager"

C3 (Ctrl+Shift+Enter to enter the array formula):

=IFERROR(INDEX(Sheet1!C:C,MATCH(1,(Sheet1!B:B=Sheet1!$B3)*(Sheet1!D:D=$G3),0)),"")

D3 (Enter to enter the formula):

=IFERROR(Sheet1!$G3,"")

E3 (Enter to enter the formula):

="Employee"

F3 (Ctrl+Shift+Enter to enter the array formula):

=IFERROR(
 INDEX(Sheet1!$C:$C,
       MATCH(1,(1=INDIRECT("Sheet1!"&ADDRESS(1,MATCH($A3,Sheet1!$5:$5,0))
                           &":"&ADDRESS(1048576,MATCH($A3,Sheet1!$5:$5,0))))
               *(Sheet1!$B:$B=Sheet1!$E3),0)),"")

G3 (Ctrl+Shift+Enter to enter the array formula):

=IFERROR(
 INDEX(Sheet1!$D:$D,
       MATCH(1,(1=INDIRECT("Sheet1!"&ADDRESS(1,MATCH($A3,Sheet1!$5:$5,0))
                           &":"&ADDRESS(1048576,MATCH($A3,Sheet1!$5:$5,0))))
               *(Sheet1!$B:$B=Sheet1!$E3),0)),"")

Results:
results

Improve this answer
10
  • First of all, thank you for answer! For the Head Managers, it could be HeadManager. I don't know if I helped you...
    – Marios
    Apr 1, 2019 at 14:20
  • I don't know if you understand what I want to do... When I write the number 1 (or another number, symbol, etc) to E7 of Sheet1, I want to automatically fill the B3-C3-D3 of Sheet2 with the data of B7-C7-D7 of Sheet1.
    – Marios
    Apr 1, 2019 at 14:42
  • What about E3, F3, G3 of sheet2 ? These formulas fill those too, together with B3, C3, D3.
    – ZygD
    Apr 1, 2019 at 15:29
  • The E3, F3, G3 of sheet2 have to automatically fill by B17, C17, E17 of sheet1. Could they?
    – Marios
    Apr 1, 2019 at 15:56
  • Man... It seems you have not checked the answer at all. Yes, all the cells in sheet2 are updated automatically. When you input 1 in the sheet1, then this employee appears automatically on sheet2 together with his manager. But I am too busy now to make this work for Head managers. You have the formulas. You are on a good path. Now it's time for you to do the work.
    – ZygD
    Apr 2, 2019 at 6:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .