Just a quick post with two silly pieces of Excel information I (sadly) didn’t know about:
https://www.contextures.com/xlDataVal02.html
https://www.contextures.com/xlExcelTable01.html
This came about while I was working on an Excel book that had been exported from a larger enterprise application. The book came with all this nice formatting and data validation and look-ups and hidden tabs and stuff like that.
Some of the columns had drop-downs that would change based on values in other columns, and I didn’t really know how that was being done until I looked up this =INDIRECT(targetCell) function. So essentially what this function does it take the text value of the target cell and uses it wherever the INDIRECT is being called. In the example above and in my workbook, INDIRECT was used as the source for the data validation look-up drop-down.
For example: the C3 data validation list was pointed to something like =INDIRECT(B3), where B3 was something like “Fruit”. Elsewhere in the book was a named range called “Fruit”, with options like Apple, Orange, Pineapple, etc. This means that the “Fruit” named range was being used for the data validation look-up list.
If you changed B3 to “Vegetable”, a different set of options would appear in the data validation drop-down list. Neat, right? Because there was a different named range called “Vegetable” with different values like Potato, Celery, Lettuce, etc.
Doing the Data Validation tutorial above also helped me figure out how Named Tables can be used in Excel, which pointed me to the 2nd link.
I’ve mostly been a data-dump-into-Excel and run Pivot-tables kind of user, so named ranges and tables and INDIRECT weren’t really functions I had to use before, but now that I know a little bit more about these capabilities, I’ll see if I can put them to use.
Thaxton's Nerd Notes 