Best way to represent relationships in excel

**therealjag** · 04-20-2010, 06:57 AM

Hi there,

I have an excel sheet where I have a sequenced list of tasks. I then also have a list of people associated with each task.

The problem I have is what is the best way to associate each task with each person, as sometimes there is a one to many relationship, a many to many relationship and also sometimes a many to one relationship.

Any idea's would be greatly appreciated as it would make my spreadsheet look a lot more readable, clearer and efficient. I've attached an example spreadsheet of the datasets that I have.

p.s. I did have the idea of using hyperlinks but discovered that I cant link one cell to multiple cells with hyperlinks, unless those cells are next to each other - which doesnt apply to me.

Thanks,

Jag

**Andy Pope** · 04-20-2010, 07:17 AM

Maybe you could use a pivot table.

You source data would be a record for each person and their task.

**teylyn** · 04-20-2010, 07:18 AM

Hi,

Without looking at your file: Excel is many things, but a relational database it is not. If you need 1 to 1 relationships, Excel works fine. One to many can be achieved with some difficulty, depending on the circumstances, but for many to many you are using the wrong tool.

It may be a good idea to look into Access instead.

**deemo** · 04-20-2010, 07:21 AM

Depends on what you want to do with the data, but perhaps something something like the attached would help? You can then turn it in a pivot table.

**therealjag** · 10-16-2010, 05:52 PM

test...sorry there, just had problems uploading files onto this forum...

**SDruley** · 10-16-2010, 06:00 PM

The new PowerPivot (available only in Excel 2010) has the ability to mimic the relational database toolsets found in Access.
The immediate solution, for now, as teylyn indicated, is found in Access.

**outofthehat** · 10-17-2010, 05:20 PM

The real question is: What do you want to do with this information once you've linked them to these tasks...?

**Norbert.Limbersky** · 10-21-2013, 04:10 PM

What about using MultiLevelExporter?
It is an add-in for Microsoft Access that exports data from relation database (1:N related data).
And the result in Excel should be as you describe you need it.