Is it possible to create a grid based on dynamic arrays?

Question

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.&nbsp;Starting state:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Anna&nbsp;&nbsp;&nbsp;=TRANSPOSE(tblResource)Tom&nbsp;&nbsp;&nbsp;JetBikeTrainJohn&nbsp;=tblPerson[Person]AnnaX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tom&nbsp;X&nbsp;&nbsp;&nbsp;&nbsp;John&nbsp;&nbsp;X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Jet&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Bike&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Train&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Adding 'Boat' in between Jet and Bike gives:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Anna&nbsp;&nbsp;&nbsp;=TRANSPOSE(tblResource)&nbsp;Tom&nbsp;&nbsp;&nbsp;JetBoatBikeTrainJohn&nbsp;=tblPerson[Person]AnnaX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tom&nbsp;X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;John&nbsp;&nbsp;X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Jet&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Boat&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Bike&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Train&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;What I want:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Anna&nbsp;&nbsp;&nbsp;=TRANSPOSE(tblResource)&nbsp;Tom&nbsp;&nbsp;&nbsp;JetBoatBikeTrainJohn&nbsp;=tblPerson[Person]AnnaX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tom&nbsp;&nbsp;X&nbsp;&nbsp;&nbsp;&nbsp;John&nbsp;&nbsp;&nbsp;X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Jet&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Boat&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Bike&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Train&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Is this possible to achieve using Excel?&nbsp;BR Mats

riny_van_eekelen · Answer

MHulten&nbsp;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.

mhulten · Answer

Riny_van_Eekelen&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;&nbsp;BR Mats

riny_van_eekelen · Answer

MHulten&nbsp;Sorry, don't really care about Halloween and don't understand what you want to achieve. Perhaps someone else.

Forum Discussion

Is it possible to create a grid based on dynamic arrays?

3 Replies

Resources


Anna			=TRANSPOSE(tblResource)
Tom			Jet	Bike	Train
John	=tblPerson[Person]	Anna	X
		Tom		X
		John			X


Jet
Bike
Train


Anna			=TRANSPOSE(tblResource)
Tom			Jet	Boat	Bike	Train
John	=tblPerson[Person]	Anna	X
		Tom		X
		John			X


Jet
Boat
Bike
Train