Update Macro to auto capture cell range and table name

bosinander
Iron Contributor
Sep 26, 2021
redhandler Sometimes I need the name and range in the workbook.
Addressing the table and then parse the formula is one way to do it.
G4 =Table1

F4 =MID(FORMULATEXT(G4);2;4711)
where 4711 just is high enough to get all the characters.

F2 parses the cell references;
=LET(table;Table1;
firstCell;INDEX(table;1;1);
lastcell;(INDEX(table;ROWS(table);COLUMNS(table)));
firstCellAddress;ADDRESS(ROW(firstCell);COLUMN(firstCell));
lastCellAddress;ADDRESS(ROW(lastcell);COLUMN(lastcell));
output;firstCellAddress & ":" & lastCellAddress;
output
)

.CurrentRegion is just fine and this gives similar possibilities without macros, eg if the table is moved from A1.
- Juliano-Petrukio
  Bronze Contributor
  Sep 26, 2021
  Considering you example:
  To get the range you type:
  =ADDRESS(@ROW(Table1),@COLUMN(Table1),4)&":"&ADDRESS(@ROW(Table1)+ROWS(Table1)-1,@COLUMN(Table1)+COLUMNS(Table1)-1,4)
  
  LET() function is not available to all excel users thats why I developed another way to get the range.
  
  To get the table name:
  
  On G6 Type =Table1[[#Headers],[Text]]
  On F6 Type=MID(FORMULATEXT(G6),2;FIND("[",FORMULATEXT(G6))-2)
  - bosinander
    Iron Contributor
    Sep 26, 2021
    One way to use LET is to make long formulas easier to read. Positive for both the builder in the future and for others using the model. Sep 2021 though, only available in 365.
    
    Using =Table1 is like using =Table1[#Data] and when still easy to read, I prefer short versions.
    
    Juliano-Petrukio's approach with finding special characters holds more possibilities 🙂 and is among others a way to get the name of linked files.
    
    Two cells linking to different files
    ='[Open Workbook.xlsx]Sheet1'!$A$1
    ='C:\Users\bosinander\[Closed Workbook.xlsx]Sheet1'!$A$1
    
    =LET(
    string;FORMULATEXT(A1);
    first;FIND("'";string);
    last;FIND("]";string);
    output;MID(string;first+1;last-first-1);
    SUBSTITUTE(output;"[";"")
    )
    
    For version not having the LET function, it is the same as
    =SUBSTITUTE(MID(FORMULATEXT(A1);FIND("'";FORMULATEXT(A1))+1;FIND("]";FORMULATEXT(A1))-FIND("'";FORMULATEXT(A1))-1);"[";"")
    
    Output;
    Open Workbook.xlsx
    C:\Users\bosinander\Closed Workbook.xlsx

Forum Discussion

Update Macro to auto capture cell range and table name

Resources