Oct 27 2021 06:19 AM
I'm trying to create a simple grid that holds referential information between two tables (Person and Resource).
The pasted grid below is to give an understanding of what I wish to accomplish.
Starting state:
Anna | =TRANSPOSE(tblResource) | |||||
Tom | Jet | Bike | Train | |||
John | =tblPerson[Person] | Anna | X | |||
Tom | X | |||||
John | X | |||||
Jet | ||||||
Bike | ||||||
Train |
Adding 'Boat' in between Jet and Bike gives:
Anna | =TRANSPOSE(tblResource) | ||||||
Tom | Jet | Boat | Bike | Train | |||
John | =tblPerson[Person] | Anna | X | ||||
Tom | X | ||||||
John | X | ||||||
Jet | |||||||
Boat | |||||||
Bike | |||||||
Train |
What I want:
Anna | =TRANSPOSE(tblResource) | ||||||
Tom | Jet | Boat | Bike | Train | |||
John | =tblPerson[Person] | Anna | X | ||||
Tom | X | ||||||
John | X | ||||||
Jet | |||||||
Boat | |||||||
Bike | |||||||
Train |
Is this possible to achieve using Excel?
BR Mats
Oct 27 2021 09:00 AM
@MHulten I can recreate a dynamic grid, but son't understand the logic for placing the X's on it. Perhaps you can explain. See attached.
Nov 01 2021 09:27 AM
@Riny_van_Eekelen sorry for ghosting you (but it is halloween after all... ;) ).
My intention is to have the user enter the X'es manually based on the analysis at hand. In other words, there will be no formula that takes care of this work.
Using a non dynamic grid (i.e. static text in cells) this is entirely possible, but that would mean having the user perform manual copy and paste, or using a macro when the source ranges change. I want to avoid both of these options.
Another option would be to have a table with two columns side by side. Then having listboxes with the ranges as source, where the user can set the relation. But when the ranges grow this option looses in usability.
BR Mats
Nov 01 2021 09:57 AM
@MHulten Sorry, don't really care about Halloween and don't understand what you want to achieve. Perhaps someone else.