Dynamic list of row / columns based on table

Contributor

Hi everyone, I am trying to generate lists based on two criteria (date and initial) value on the table. Desired result will be color formatted as well based upon the criteria (processing, verifying and reviewing). Any help will be greatly appreciated. 

 

rbalza_1-1666154688988.png

 

3 Replies

@rbalza I'd suggest you look into Power query and Power Pivot to achieve something similar, tough without the fancy colors. See attached.

 

by the way, I noticed you cross posted the same question here:

https://www.myonlinetraininghub.com/excel-forum/excel/dynamic-list-of-row-columns-based-on-table#p26... 

Hi @Riny_van_Eekelen, thanks once again for your response. Really appreciated it. However, can't do it on a power query/power pivot as it should be on a formatted view (my bad for not including it on the requirement)...I was trying the combination of using regular functions such as XLOOKUP, INDEX/MATCH, SPILL but to no avail...

@rbalza Can't really think of an easy way to do this with just formulas. But you can play around a bit with the pivot table format and some conditional formatting to produce this:

Riny_van_Eekelen_0-1666182800185.png

See attached.

 

PS: changed the query a bit as well.