Data changes with different dropdown selection

[paankadu1]

I am working on a file that will have one sheet for each person to enter their monthly hours on according to project number. There will also be a project sheet where the manager will select the person's name from a drop down list. I have it to where they pick Person 1 it will pull the data from Person 1 sheet. However, I would like data to pull from correct tab based on the dropdown name selected. That way the formula doesn't have to be specific to the row.

This is what I have for the row with Person 1 chosen. However, if someone would select Person 2 it doesn't change the data in columns D-O, Is there a way that if you select Person 2 it will update the values from Person 2 tab automatically without manipulating the formula for each row?

=VLOOKUP($C$1,'person 1'!$B$5:$O$26,3,FALSE)

Project Tab

Person 1 Tab

 

[Toadstool]

Hi Paankadu1,

I'm not sure I've fully followed how your sheet works but it looks like each person may have multiple entries for the same project so I would use SUMIFS rather than a VLOOKUP retrieval. To retrieve from different sheets you can use INDIRECT, so maybe something like this:

Paankadu1.xlsx
	B	C	D	E	F	G	H
5	Project		1	2	3	4	5
6	OtherProject		5	4	3	2	1
7	Project		11	22	33	44	55
Person 1

Paankadu1.xlsx
	B	C	D	E	F	G	H
5	Project		6	7	8	9	10
6	OtherProject		33	44	55	66	77
7	Project		2	4	6	8	10
Person 2

Paankadu1.xlsx
	C	D	E	F	G	H
1	Project
2
16
17	Person 1	12	24	36	48	60
18	Person 2	8	11	14	17	20
19	Person 1	12	24	36	48	60
Sheet1

Cell Formulas
Range	Formula
D17:H19	D17	=SUMIFS(INDIRECT("'"&$C17&"'!"&CELL("address",D$5)&":"&CELL("address",D$26)),INDIRECT("'"&$C17&"'!$B$5:$B$26"),Sheet1!$C$1)

 

[paankadu1]

Thanks! I am on a work computer so can't download the add in so tried to be clear with my screen shots. Each person will have the full list of projects on their individual worksheet. Each person may have hours on only one project or on several. So their hours would transport to each corresponding project tab. I haven't worked with indirect formulas much

There will be a Project Tab for each individual project and that will have all the team members on that tab.
Then there will be a Person tab for each team member. On their respective tab each Team member will enter their hours they spend on each different project for each month.
On the Project tab there are drop downs to select the team member for each group (Controls, Engineer, etc.). Right now i have the formulas for the person selected on the project sheet pulling from their respective Person sheet. So it is a manual process to get each sheet set up with the formulas tieing the person sheet to the correct name on the project sheet. My fear is someone may actually change a dropdown on the project tab so then all the hours will be wrong for that person. I would like it to automatically populate the row on the project with the correct hours from the person tab based on the name they select.

Project 1 - has line 17 assigned to Person 1. I have the Vlookup pulling the hours from Person 1 tab. Line 18 is assigned to Person 2.
But if someone changes line 17 to person 2 then the hours would be wrong as line 17 is still pulling from person 1 tab. I could lock it down once the project tab is created but it is still time consuming to link each row to the correct person tab.

Person 1

Person 2

 

[Toadstool]

Did you try the formula I supplied?

 

[paankadu1]

Did you try the formula I supplied?

I am working with it now but not real familiar with indirects. When I enter that formula in cell D17 on the project tab and select enter it opens up a file explorer folder ...

 

[Toadstool]

The formula is using the sheet names in C17 ("Person 1") to go to that sheet and add together all numbers in D5:D26 where column B5 to B26 contains the project name from C1.

Here is an amended version so you don't get an #REF! error when there's no matching sheet name. If you get the File Explorer window open then just Esc key past it and see what the error is.

Paankadu1.xlsx
	C	D	E	F	G	H
1	Project
2
16
17	Person 1	12	24	36	48	60
18	Person 2	8	11	14	17	20
19	Person 1	12	24	36	48	60
2nd

Cell Formulas
Range	Formula
D17:H19	D17	=IF($C17="","",SUMIFS(INDIRECT("'"&$C17&"'!"&CELL("address",D$5)&":"&CELL("address",D$26)),INDIRECT("'"&$C17&"'!$B$5:$B$26"),'2nd'!$C$1))

 

[paankadu1]

The formula is using the sheet names in C17 ("Person 1") to go to that sheet and add together all numbers in D5:D26 where column B5 to B26 contains the project name from C1.

Here is an amended version so you don't get an #REF! error when there's no matching sheet name. If you get the File Explorer window open then just Esc key past it and see what the error is.

Paankadu1.xlsx
	C	D	E	F	G	H
1	Project
2
16
17	Person 1	12	24	36	48	60
18	Person 2	8	11	14	17	20
19	Person 1	12	24	36	48	60
2nd

Cell Formulas
Range	Formula
D17:H19	D17	=IF($C17="","",SUMIFS(INDIRECT("'"&$C17&"'!"&CELL("address",D$5)&":"&CELL("address",D$26)),INDIRECT("'"&$C17&"'!$B$5:$B$26"),'2nd'!$C$1))

Finally getting a chance to get back to this....

when I enter the formula in D17 on the project sheet it is still wanting to open a new file. I escape out and it doesn't give me an error but it only returns a 0 value instead of the value in D5 in Person 1 sheet that corresponds with that project. I even started a new workbook laid out like you have your examples and get the same thing. I no longer get the #REF! error with your amendment, just a 0 value

 

[Toadstool]

In your example cell C17 contained "Person 1" so if you have a sheet named Person 1 it will total all values in that sheet cells D5 to D26. If there's no numbers in those cells it will return zero.