Changing Data In Worksheet1 - automatically changes data in Worksheet2

[TomPowersPMP]

I have a program of projects with 5 key roles by team member name. Currently in WS1 the data is in a table with filtering. In worksheet2 I have the data sorterd subtotaled by project listing the team member names and roles. The 2 sheets are not dynamic. How can I have WS2 automatically change when I change the data in WS1?

 

[sgmpatnaik]

@Tom


HI


I THINK VLOOKUP FORMULA CAN HELP YOU, DID YOU


IF YOU THINK THE PROBLEM IS NOT SOLVE WITH VLOOKUP THEN PLEASE UPLOAD SAMPLE WORKBOOK


THANKS


sp

 

[vijay.vizzu]

Dear TOM,


Please specify "by which data changing in WS1 the data should be displayed" like If you change role name in WS1 accordingly the data should be displayed in WS2.

Plesae clear


Regards

vijay

 

[nishant859@yahoo.co.in]

You can use vlookup or index with match function or data link method to do

or pls share the sample data

 

[TomPowersPMP]

Thanks for the responses - where can i put the sample data.


The data that that is changing is the estimated hours/month each person.


on ws1 i use filtering for project and/or Role and/or Person. By using the subtotal by filter I can Identify how many (Full Time Employees) I will need by Project or Role or Person.


The goal is to move the project hours around in order to manage available resource time.

 

[bobhc]

Good day TomPowersPMP


You can open a Dropbox account, free and those who want to see your uploads do not have to sign in or register, all you have do do is post the public link in your postings. More details in the stickies top of front forum page third one down

 

[TomPowersPMP]

Thanks Ninja - file is found here

https://dl.dropbox.com/u/42531503/FTE-Summary-Report-chandoo.xlsx


The goal is to be able to move the role hours/month field by project in order to manage the resource constraints.


My thoughts are if i move the items in Summary WS the Subtotal by Project WS can change automatically. But maybe this is not the best approach based on my goal?


616-550-8490

 

[nishant859@yahoo.co.in]

Try below mention formulas in "Subtotal by Project" worksheet in cell H6 & I6 respective


=IF(RIGHT($B6,5)="Total",SUMIF($B7:$B206,LEFT($B6,LEN($B6)-6),H7:H187),INDEX(Table1[Column8],MATCH($B7&$C7&$D7,Table1[Column2]&Table1[Column3]&Table1[Column4],0),1))


=IF(RIGHT($B6,5)="Total",SUMIF($B7:$B206,LEFT($B6,LEN($B6)-6),I7:I187),INDEX(Table1[Column8],MATCH($B7&$C7&$D7,Table1[Column2]&Table1[Column3]&Table1[Column4],0),1))


pls note these are array formula so please use Ctrl+Shift+Enter, after pasting