Is there a way to Indirectly call an array of Table names?
I am trying to find a way to dynamically merge several Tables in Excel.
eg. the tables are called Tbl_s1, Tbl_s2,... Tbl_sN.
These table names are stored in another Table called Tbl_Ref.
I was hoping to be able to call them all and stack them using:
=VSTACK(INDIRECT(INDEX(Tbl_Ref[Ref],,)))
This only results in a spill of 'N' #VALUE errors.
Does anybody have a suggestion for how I can get around this error?
Solution
Use REDUCE:
=DROP(REDUCE("",Tbl_Ref[Ref],LAMBDA(z,y,VSTACK(z,INDIRECT(y)))),1)
REDUCE will iterate each reference and vstack it one at a time then return the final result.
- Need help fixing my excel formula so it doesn't show #VALUE! error in end result
- Putting a concatenate inside of a count if
- Can I guarantee the Image Formula Function is finished before printing the result?
- Excel filter data in date intervals excluding all 0 values
- Count cells with two conditions
- Formula to filter data based on prior 3 boolean "true" values
- I want the result of a function to return the value at the top of the column in an array instead of the position of the cell it's searching for
- Create a smaller dynamic list in Excel from data in a larger list using multiple criteria
- Spill formula to sumifs by row & col for only relevant months
- Copy cells based on matching index numbers on excel?
- How to Combine Text Based on Multiple Criteria in Excel
- Xlookup multiple match types
- Separate octets of IP address using formulas
- Dynamic index match using indirect function to pull from other sheets
- Trying to keep last 9 of various strings
- Drag cells keeping the reference in incremental order
- Use native Excel functions to count occurrences over threshold
- Excel - Summing Multiple Material IDs Cost from one Cell
- How to use Use a Formula to Determine Which Cells to Format to format a range of cells
- Create a list based on multiple filter criteria without row limitation in COUNTIF formula
- How to I get excel to look up values in the nth column based on a number in a cell?
- Combining multiple OR conditions with AND in Excel formula
- Excel to dynamically generate SQL script using formula
- EXCEL: If DATE is between 10th January to 10th February in the "Date Column", return "JAN" in the "Month Column" - and so on for the rest of the year
- Create string of text with Textjoin, dependent on a selection from DropDown
- How do I split a value between two years over a date range in Excel?
- Only sort filtered list within a value from another column (similar to the partition by function in SQL)
- Create unique list and sum up values per unique row (without defining the filter criteria multiple times in the LET function)
- Generate a random list based on a main list (consisting two columns)
- How to create an excel formula using LET and other dynamic array functions which gives an calculated output of each column?