Sheet Function - Spaces in Names

[jomili]

I read a tutorial this morning on the SHEET function, which is a function I've never used before. I was working in one of my workbooks to test out the functionality, and hit a problem that wasn't covered in the tutorial and so far I can't find mention of on the internet. I have the following sheet names in my workbook:

Please Login or Register  to view this content.

Based on the tutorial I type in a formula like

Please Login or Register  to view this content.

and the formula returns the correct position number of the sheet. That works for ACCOUNT_XWALK as well, but not for any of the other Sheet names. I THINK it's because they have a space in the name. Based on that, if I key in

Please Login or Register  to view this content.

It's not found, but if I then change the Sheet name to "TimeNotes" and adjust that name in the formula it IS found (Which makes sense if the blank is causing the problem). But, here's the weird part; if I then adjust the name back (put the space back in) the formula also adjusts the name in the formula and still works (you can hit F2 to refresh, F9 to recalculate it and it's still valid). If I copy and paste the formula, it still works. But then if I type in the identical formula in another cells, it doesn't work.

However, if I type in

Please Login or Register  to view this content.

(notice the apostrophes) it works in all instances.

Neither the tutorial from Mindy at My Online Training Hub, nor ExcelJet's page on the Sheet Function (https://exceljet.net/excel-functions...sheet-function) mentions or shows the apostrophes. Is the problem that I'm doing something wrong, or should the apostrophes always go in, and the examples are just missing that?

[Fluff13]

You always need the apostrophes if the sheet name
a) includes spaces
b) is numeric
c) represents a valid range (ie TB1)

[jomili]

Not sure I follow you on the last one. You said "You always need the apostrophes if the sheet name...represents a valid range (ie TB1)"If Main!A1 is a valid range (and it is, the formula works), then I don't necessarily need apostrophes for a valid range.

Also, these two formulas both work:

Please Login or Register  to view this content.

So, since sometimes you have to have apostrophes to work, wouldn't it be a best practice to always put the apostrophes (so should be in any example or tutorial), or are there instances where the apostrophe would hose the formula?

[Fluff13]

If the sheet name is a valid range then you need the apostrophes, so if you had a sheet called TB1 it would need to be 'TB1'!A1
If you enter =SHEET('Main'!A1) Xl will remove the apostrophes.

[jomili]

So if you enter the apostrophes and they're unneeded, Excel removes them and the formula works, no issues.
But if you DON'T enter the apostrophes, and they're needed, the formula doesn't work.

So wouldn't it be a best practice to always put the apostrophes, just in case?

[Fluff13]

That's up to you, if you want to add them to be on the safe side, then add them, otherwise don't bother.
Purely personal preference.

[MyOnlineTrainingHub]

Hi John,

If the sheet name contains a space you must always surround it with the apostrophes. This is the case in all sheet references, whether in a formula, named range, chart reference etc.

If the sheet name doesn't contain a space then you can get away without apostrophes. And while in the example above, where you built the formula and then edited the sheet name, Excel added the apostrophes for you, it's recommended you always declare sheet names with apostrophes to ward against future changes that result in a space in the name. This is because Excel will not always dynamically add the apostrophes for you. e.g. if you're building the sheet reference with INDIRECT then you'd need to include the apostrophes manually.

Hope that clarifies things.

Mynda

MyOnlineTrainingHub.com

[jomili]

Mynda,
Thanks for replying. I think I understand what you're saying, my only comment would be, in reference to "you can get away without apostrophes", if options are "If you always put them in, it will always work" or "If you don't put them in, it will sometimes work", then I think any instructions should reference that best practice is to add the apostrophes.