Apr 16 2023 09:21 AM
I am aware that you can reference other sheets in a function, but how do you reference the current sheet in one?
IF(Current_sheet_name="user template", value_if_true, value_if_false)
Thanks!
Apr 17 2023 12:38 AM
You can use the CELL function to reference the current sheet name in an Excel function.
Here’s how:
I hope this helps!
Apr 17 2023 12:40 AM
Solution
(the workbook must be saved)
If you run Excel 365:
=IF(TEXTAFTER(CELL("filename",A1),"]") = "user template", value_if_true, value_if_false)
with other versions:
=IF(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))) = "user template", value_if_true, value_if_false)
Sep 04 2023 02:47 AM - edited Sep 04 2023 02:48 AM
This worked for me:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
The right square bracket appears only once in the returned filename string.
Tom
Oct 22 2023 02:09 PM
Oct 25 2023 10:30 AM
If you use the CELL function in more than one sheet in your workbook, they will all update to the match the last one you use.
So if you have Sheet1 and Sheet2 and put
=CELL("filename")
in Sheet1!A1 it will show "Sheet1" in that cell. Then if you go to Sheet 2 and put the same thing in cell Sheet2!A1, it will show "Sheet2" there. But if you then went back to Sheet1, Sheet1!A1 will now show "Sheet2".
Oct 25 2023 11:49 AM
That's why it shall be used with reference like in @L z. post.
Oct 25 2023 01:26 PM
@Sergei Baklan I changed my formula to
=CELL("filename",A1)
as shown in the other post you mention, and I see the same behavior. But I tried the version with RIGHT and LEN instead of the TEXTAFTER version, and that works as expected.
Thanks for pointing out my mistake!
Nov 30 2023 03:23 PM
There is a subtle weakness to all of the solutions so far: they all assume that the filename and path do not contain the right square bracket ("]"). But in both Windows and Mac (OS X and later), filenames and paths can contain that character.
However, with the new text functions in Excel 365 and Excel for the Web, you can fix this weakness and greatly simplify the formula:
=TEXTAFTER(CELL("filename",A1),"]",-1)
The TEXTAFTER function can be very useful for this sort of thing.
Dec 02 2023 05:28 AM
Nope, the question was if we are in right sheet or not. All answers assume we have "]" at the right. Compare your formula with one from @L z. above.
Dec 18 2023 10:33 AM
Dec 28 2023 12:05 PM
Jan 02 2024 02:47 AM
@L z. solution will not work if worksheet will be uploaded to Sharepoint and opened from browser:
"filename" | Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved. Note: This value is not supported in Excel for the web, Excel Mobile, and Excel Starter. |
Mar 14 2024 02:38 PM
@Needsleep =(MID(@CELL("filename",A1),FIND("]",@CELL("filename",A1))+1,256)) try this one; it worked for me
Mar 28 2024 11:11 AM
Apr 17 2023 12:40 AM
Solution
(the workbook must be saved)
If you run Excel 365:
=IF(TEXTAFTER(CELL("filename",A1),"]") = "user template", value_if_true, value_if_false)
with other versions:
=IF(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))) = "user template", value_if_true, value_if_false)