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

Occasional Contributor

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   JetBikeTrain
John =tblPerson[Person]AnnaX  
   Tom X 
   John  X
       
      
Jet      
Bike      
Train      

 

Adding 'Boat' in between Jet and Bike gives:

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

 

What I want:

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

 

Is this possible to achieve using Excel?

 

BR Mats

3 Replies

@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.

@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

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