Hi,
For context, I currently have functions that analyzes a table and I'm trying to make a new table from that data. I have multiple selection dropdown menus and I want to know all selections selected per row by making the possible selections as headers and indicating 1 or null if selected or not (please refer to the image). The end goal is to get this as a table and export it to Power BI for visuals.
The issue is that the formulas spill the data and for some reason, spilled data is not compatible with tables (using spill as the original table will constantly be growing). The plan is to have a VBA script that can read a worksheet and convert the range of spilled data into a table (if that is possible), assuming that my spilled data is in a separate worksheet. The reason for this odd approach is because the initial table will be constantly growing with more inputs, meaning the range of spilled data will also grow.
To summarize, I need help to write a VBA script that can read spilled data ranges that is bound to grow and convert it to a table.
Thanks in advance for the help
For context, I currently have functions that analyzes a table and I'm trying to make a new table from that data. I have multiple selection dropdown menus and I want to know all selections selected per row by making the possible selections as headers and indicating 1 or null if selected or not (please refer to the image). The end goal is to get this as a table and export it to Power BI for visuals.
The issue is that the formulas spill the data and for some reason, spilled data is not compatible with tables (using spill as the original table will constantly be growing). The plan is to have a VBA script that can read a worksheet and convert the range of spilled data into a table (if that is possible), assuming that my spilled data is in a separate worksheet. The reason for this odd approach is because the initial table will be constantly growing with more inputs, meaning the range of spilled data will also grow.
To summarize, I need help to write a VBA script that can read spilled data ranges that is bound to grow and convert it to a table.
Thanks in advance for the help