Hi, thank you for checking out my problem.
The screenshot attached is a very quick mockup of the type of situation I'm dealing with. Effectively, I have a series of (hidden) tabs which each have the same table in them except that they are getting the daata for the tables imported from other spreadhseets. I then have a central tab at the front with the same table which I want to be the amalgamation of all of the other tabs.
The way I use it at the minute, in the example, is that the tabs are all named after the categories so I use a vstack, tocol combo to fetch this data using an indirect based on the category dropdown and then use the colour as a filter of what to display in this table.
My big issue is that I cannot see a good way of incorporating an 'all' option for the colour. My initial thought was to just stack this formula on top of itself for each colour when all is selected but there is a good chance that there won't be data in some of them so I know any empty arrays would break the whole thing as vstack cannot handle an empty array.
I've included a rough formula of how I fetch the data for the table currently, it may not be correct, I rushed it for demo purposes but it works in my actual spreadsheet. My thoughts on how to incorporate an all opption for the colour would be to add an if argument at the start with the condition being 'all' then do a vstack with a series of tocol's for each tab but I'm still unsure the best way of handling empty array errors. I'm also sure there must be a much simler way to do all of this.
Any advice or thoughts you could offer would be greatly appreciated and if any of what I've said is unclear, I'm more than happy to attempt to clarify further.
Many thanks
The screenshot attached is a very quick mockup of the type of situation I'm dealing with. Effectively, I have a series of (hidden) tabs which each have the same table in them except that they are getting the daata for the tables imported from other spreadhseets. I then have a central tab at the front with the same table which I want to be the amalgamation of all of the other tabs.
The way I use it at the minute, in the example, is that the tabs are all named after the categories so I use a vstack, tocol combo to fetch this data using an indirect based on the category dropdown and then use the colour as a filter of what to display in this table.
My big issue is that I cannot see a good way of incorporating an 'all' option for the colour. My initial thought was to just stack this formula on top of itself for each colour when all is selected but there is a good chance that there won't be data in some of them so I know any empty arrays would break the whole thing as vstack cannot handle an empty array.
I've included a rough formula of how I fetch the data for the table currently, it may not be correct, I rushed it for demo purposes but it works in my actual spreadsheet. My thoughts on how to incorporate an all opption for the colour would be to add an if argument at the start with the condition being 'all' then do a vstack with a series of tocol's for each tab but I'm still unsure the best way of handling empty array errors. I'm also sure there must be a much simler way to do all of this.
Any advice or thoughts you could offer would be greatly appreciated and if any of what I've said is unclear, I'm more than happy to attempt to clarify further.
Many thanks
