Forum Discussion
workbook and sheet name via formula
Afraid for Excel Online that's only with Office Script, there are properties getName both for workbook and worksheet.
VBA and related functions don't work in Excel Online.
Thanks to the help above the following formula worked for me.
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
CELL("filename",A1) returns the full path filename and worksheet name with the work book in square brackets []
LEN (string) gives the length of a string
FIND (string1, string2)returns the position from the left of the first occurrence of string1 in string2.
RIGHT(string ,n) gives the last n characters counting from the right.
Hi Everyone,
My workaround for returning the worksheet cell/range reference in Excel Online is as follows:
Pick an input cell where you will reference a cell or range. In this case it is D2.
In another cell (in this example I use A2), paste the following formula:
=REPLACE(FORMULATEXT(D2),1,1,"")
This formula turns the formula in D2 into a text string and then replaces the equals sign in the first character with "", removing it from the string.
In D2, type the equals sign, then use your cursor to select the cell or range you need the reference for and hit return. A2 should now display the reference of the range/cell added to D2.
This works across sheets and probably across workbooks with the correct permissions.
I am personally using it as a way to expedite modification of a 3rd formula.
In this 3rd formula, I am replacing range/cell references with a reference to what appears in D2, but enclosed in the Indirect function:
Indirect(D2)
This will take the string in D2 and treat it as a cell reference again.
Now I am able to replace the cell/range references in the formula by inputting them into other cells rather than changing the formula itself.
