What I want is to have a column header above an indexing column, such that it would display "PK_[SheetName]".
I am trying to use a formula that can be copied and pasted in cells on different sheets, like a "template" formula. The formula will reside in different locations (different row, column) on different worksheets within a workbook. In this case, my workbook has, on each tab, an "Index" (ascending integers starting at 1) for each row of information to the right of Column A. I also have a need for a way to reference the current cell for other indexes within the same sheet, which have a hierarchy as well (e.g., if cell adjacent to or might have particular strings (e.g., the "hierarchy index cell" only starts an index if the cell above it and to the right has a string of text, like "Items", and continues in its own column until a range of cells are blank (presumably the end of the table). For the main index, which I'll call the Primary Key (PK), the counter starts at 1 and increases to the end of the range of the information. (I'm not complicating it further by having it check if the cells are blank for PK; I'm only using the PK for a fixed number of cells, which vary from sheet to sheet). But I want the column heading above the PK index to incorporate the sheet name.
I know that CONCAT, MID, FIND and CELL to obtain the current workbook name. I am trying to NOT have to create separate defined "Names" for each PK column heading on each sheet. I'm not savvy enough to know if there's a way to define a Name for a cell that could vary in position on each sheet of the workbook, and have it pull the tab name from the CELL function uniquely for each worksheet.
I have tried looking at MS Support pages for CELL and ADDRESS functions, have found a thread that comes close but doesn't address how to reference the current cell in which the function resides Another Thread, and another reference linked from that thread Secondary Reference.
What I lack is the ability to have a formula reference the "current cell". The current formula I have is:
=CONCAT("PK_" & MID(CELL("filename", A23),FIND("]", CELL("filename", A23)) + 1, 255))
Where:
- A23 = the particular cell the PK number is in
- [worksheet name] = ThisWorksheet
The result is "PK_ThisWorksheet"
I came across the ADDRESS function, and tried to plug in ADDRESS(COLUMN(), ROW(), 4) where "A23" exists:
=CONCAT("PK_"&MID(CELL("filename",ADDRESS(COLUMN(), ROW(), 4)),FIND("]",CELL("filename",ADDRESS(COLUMN(), ROW(), 4)))+1,255))
But I got the "There's a problem with this formula" Yellow Exclamation Point Triangle Warning, and can't get any further. I was expecting it to come back with "PK_ThisWorksheet".
Again, if there's a workaround that doesn't involve in-cell functions, I'm all ears, but I would very much prefer a solution that uses functions for the sake of "ease", both in usage/repeat usage as well as in teaching others who might have to manage the spreadsheet after me.