Two related tables

**gerainta** · 05-11-2012, 10:48 AM

Hi Everyone,

I am trying to create an excel sheet that allows me to see both who is working on a project and what projects a person is working on. This dual view is causing me a nightmare! I have attached a spreadsheet to show the type of thing I am trying to create.

The dates are the week commencing and the numbers are the percentage of time a person is allocated to that project. The purpose of the spread sheet is to give an easy indication of how many people in total work on a particular project and what is an individual’s workload. By selecting from the dropdown in cell B2 the table would change to the relevant person or project.

I want to be able to only update one sheet and the other will automatically update. Ideally I would like to be able to update either, but if this is not possible then the ability to update the person sheet is most important and the project sheet can run off that.

There are about 50 people but this number is constantly changing, and about 7 projects which is also a changing number so the ability to add new people / projects without much work is vital.

I hope you can help.

Thanks in advance,

Ger

**TMS** · 05-12-2012, 05:14 PM

Excel works best with tables of data. I would suggest that you put all the raw data in one table: Project, Person, Dates ...

Then you can use a Pivot Table (or two) to analyse the data.

Regards, TMS

**FDibbins** · 05-12-2012, 05:47 PM

try the attached. i added a copy of the table in your Projects sheet, you cannot put a value into a table (2) that is looked up from a reference in a table (1) that itself is getting its values from another table (2).

see if this is what you want and let me know?

**gerainta** · 05-15-2012, 05:59 AM

Thanks for the help. I ended up doing a combination of both sugestions. The data is entered in one sheet, and is lifted out for reporting into separate tabs.

Thanks,

Ger