I think I've figured out an outline of what I need to do, now I just need to figure out how to do it.

I have added lookup columns on each scenario worksheet for each option. this will give me a range that I can then do an array formula to get the unique items in the order they appear in the list. I have gotten a test to do this, but I haven't gotten it working in the actual files yet. I will need to do some more reading and experimenting to get a handle on the whole array formula concept.

Once I get that working, that will give me the legend showing each footnote with it's number and text.*

From there, I will need to use the values in each rows option lookups to get that text's index in the legend list to add the footnote numbers to the actual row description.


I am pretty confident this will do what I need. Unfortunately, these steps are a little below my current competency level in Excel, so it will take some time learning/experimenting to get this working.

*Edit: I also need to make sure the list includes only non-empty values, which is more complicated than what I have so far.