Formula Bar suddenly loses quotation marks

BWMagee

Board Regular
Joined
Feb 18, 2014
Messages
105
Have come across a seemingly inexplicable error.

I am copying a formula from column A across. The formula is an INDEX MATCH referring to Sheet1, which appears in the formula bar as 'Sheet1'!
When I drag it across to column B, I now want it to MATCH to Sheet2. So I highlight the column, Find "Sheet1" (minus quotations), Replace with "Sheet2" (minus quotations). It works. Formula bar now reads 'Sheet2'!
I have done this 22 times when suddenly the '' marks disappear from the formula bar, so that instead of reading 'Sheet22'! it just reads Sheet22!
I don't know why it's done this, as I followed exactly the same process, but no big deal, it still works.
Then I drag that formula across, Find "Sheet22", Replace with "Sheet23" and suddenly it is opening a dialog box asking me to look for the sheet in my system. It shouldn't be doing this, as the sheet is in the same workbook. I cancel that, and see what happens when I type 'Sheet23'! into the formula bar instead of Sheet23! and guess what, it works!
So the problem appears to be the loss of the '' marks in the formula bar when referring to different worksheets. I have no idea why these disappeared. Can anyone tell me why? And how to stop it from doing this?
All help is much appreciated.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

BWMagee

Board Regular
Joined
Feb 18, 2014
Messages
105
I believe I have found the answer. The first 22 sheets all had spaces in their names, which is why their names were enclosed in quotation mark. The 23rd sheet had no spaces in its name, so Excel ditched the quotation marks. When I dragged that across and replaced it with the next name, containing spaces, Excel didn't add the quotation marks back in. So to resolve this issue I will have to copy and paste from column with the quotation marks, instead of dragging across one at a time. It seems ridiculous that Excel would have this inconsistency.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,205
Messages
5,640,834
Members
417,171
Latest member
Mr___D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top