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.
- Excel function to construct an array from values without VBA
- Excel - Aggregate dynamically values in a Table using array formulas (MAP, BYROW, CHOOSECOLS, TEXTJOIN, LAMBDA, FILTER, HSTACK) in a single Cell
- EXCEL: How do I return multiple columns using XLOOKUP, when the lookup_value is a range?
- Filtering an Array (not Range) based on a SUM of CHOOSECOLS applied to it
- Add rows to table produced with filter function based on additional argument
- Spill formula on % calculation
- Epoch Unix Value formula
- SORT formula with empy column in data range and descending order based on another column (not the first column in the data range)
- Update the nth element of an array on Excel using formulas
- Is there a way to Indirectly call an array of Table names?
- Power Query Dynamic Column Reference
- Excel(VBA) Formula in added column needing logical workaround
- Converting Feet-Inches to Inches Excel
- Daytime and Nightime Occurance Duration of an Event
- compile error : Ambiguous name detected: Worksheet_Change
- Excel - Formula to see if IP address is contained within list of subnets
- How to find duplicate values in one column while checking if a condition in another column is met
- How to update excel formula to work for all columns continuously to the right and not just the current 2 it's set up for
- How to do consecutive subtraction of adjacent columns and return the result concatenated in cell with comma separator?
- In Excel, create table showing the row,column of the cells in another table
- How to reliably increment a Character+Numeric value in excel
- Incorrect comparison result with calculated date
- SUMPRODUCT with TEXTSPLIT and MATCH formula returning #VALUE! error when summing multiple intersections in Excel
- List all values of overlapping dates
- Remove 'The ' from the beginning of a book title
- How to enter information in one cell based on text in another cell
- How to create a Excel formula for converting Cell value to Hexadecimal number?
- Finding a value in an Excel array
- Remove URL and keep file name with extension
- How do I select the date from a column that corresponds to the maximum value in another?