I want to display the name of the table in a cell. That's what I proposed because I needed a solution that does not use tables or macros.
Is there a way to condense these formulas into a single cell? I want to create it in order to have only one cell, which I can paste wherever I want, which dynamically displays the name of this table.
Column M is simply FORMULATEXT in the right column for illustrative purposes.
The underlying idea is that if I cut and paste the table, including the cells immediately to its left, into another location on the spreadsheet, it will still work properly and display the name of the table. The structured reference in cell N24 guarantees its operation, even if I insert something else between the table and the current N column.
- Cell n25 returns the name of the header of an arbitrary column of the table. The advantage is that it will automatically update when the name of the table or column changes, without the table being at a particular location.
- Cell n24 recovers some of the formulatext in n25. Specifically, it starts searching from the second character (that is, after the = in the formula) until it finds the[[Quimarquelafindunomdelatableetledébutdunomdelacolonne(nousvoulonsseulementlenomdelatable)CelafonctionnesurmonExcel2016maisnefonctionneramalheureusementpasaveclesversionsantérieuresd'Excelcequiestpénible[[Whichmarkstheendofthetablenameandthestartofthecolumnname(weonlywantthetablename)ThisworksonmyExcel2016butunfortunatelywon'tworkonearlierofExcelversionswhichisapain[[quimarquelafindunomdelatableetledébutdunomdelacolonne(nousvoulonsseulementlenomdelatable)CelafonctionnesurmonExcel2016maisnefonctionneramalheureusementpasaveclesversionsantérieuresd'Excelcequiestpénible[[whichmarkstheendofthetablenameandthestartofthecolumnname(weonlywantthetablename)ThisworksonmyExcel2016butunfortunatelywon’tworkonearlierofExcelversionswhichisapain
- Cell n23 looks for and removes the brackets from what is returned in n24.
- Cell n22 removes underscores and replaces them with spaces. Not strictly necessary, but it looks more orderly.
The problem with this way of proceeding is that the top three cells must be placed above the current n25 for this to work. They take up space and look messy. I skirted it by simply setting the color of the white cell text except for the top one.
Is there a better way to do this, without using macros or tables, but bonus points for a solution usable in Excel 2010? Or will this question simply be the exhibition to use to mark my slip into madness?