Excel Structured Reference Dynamic Table Name
I've been at this for an hour now and I've ended up going back to using INDIRECT, which I would rather avoid. I feel like there must be a simpler way of using table format to get this done very cleanly, but I can't figure it out!
I have a sheet like this, where I want the yellow area on the right to show me the data from the available choices on the left, based on the value in the orange cell at the top. The top table is called tPeople, and the bottom table is called tPlaces. So if tPeople is entered in the cell, I want the data for that table to show up in the yellow area.
At the moment, I've set a Named Range of rPeople and rPlaces to get this working with an indirect, but I'd rather be actually using the table, and not use the indirect if possible.
=INDEX(INDIRECT($E$1),$F2,G$1)
Thanks!
Yes it can be done using OFFSET and INDIRECT on the table name. Here TblName is named range for cell I1. One caveat is that the rPeople and rPlaces table must be on the same row or the formula will return #Value error. Also [ID] [ID] is required to fix absolute reference for the structured table referencing. See here.
- Move values from column to rows in Excel
- Application.FileDialog vs GetOpenFilename: What is the difference?
- Only display the values in a certain rank position from filtered and sorted list
- Convert table to reflect column names as row values
- Excel: Dynamic stacking or arrays n-number of times
- Convert Excel formula to Power Query (if countifs)
- Trying to hide rows in excel based on two values
- Combine every unique value from column A with duplicated values into a new sheet
- Import formula from variable - Excel VBA
- VBA Macro to detect unique entries and copy them from one workbook to another is copying a small fraction of duplicates along with the uniques
- Copy paste cells from button row
- Get the value of the second non-empty cell in a row
- SUMIF with UNIQUE criteria for multiple columns, adding all values with same name
- How can I use filter function in Excel to filter based on selections including those that are "ALL"?
- Openpyxl - I can't set cell format and sum values correctly in time format (h:mm)
- how to mask hash users into random values [email protected] in azure databricks
- Find/Replace Row number within Formula
- Extracting & summing data from a single cell in excel
- How to map table references in a JSON file to corresponding values in an Excel file using Python?
- Conditional Formatting to highlight current date on calendar
- Convert number in excel to M or B
- How to have instructional text in Excel?
- How to copy a range to several worksheets by using a list of worksheets to copy to
- VBA - exclude certain boxes from a general check
- How to count resolved comments only via VBA in Excel
- Excel VBA - how do I get the RowSource of a ComboBox to change depending on the value of another Combobox?
- check if a file is open in Python
- How to copy only plain text of cells in Excel?
- VBA - Capitalise range of cells before the last period
- Getting the next value of the same user if the type is "no type"