The Problem – Apostrophes in Sheet Names
You are trying to use a formula to reference an Excel Sheet with an apostrophe in its name and you keep receiving an error message or a #REF! reference error.
The Solution (Very Easy!)
Great news! Simply replace the apostrophe with two apostrophes in your formula. That’s right, two consecutive apostrophes, not a quote. Very simple solution indeed!
Below I provide two examples. You can manually update the name in your formula (or in the cell you are referencing if using an indirect lookup), or if working with many sheet names, you may opt to use the substitute solution shown below (formula displayed in E5).
|
1 |
=substitute(A4,"'","''") |
The substitute can be nested in an indirect formula also.
Please comment below on any other challenges you would like for me to cover. I am contemplating doing a quick solution series in this format!
Also, be sure to check out Dose for Excel (click the image below)! Add over 100 functions to Excel to increase your productivity and more! Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in. Thanks for supporting my blog!
Some topics addressed in this post:
Ref Error in lookup
#REF! Error in lookup
Ref Error in formula
#REF! Error in formula
How to fix ref error in Excel from apostrophe
Apostrophe in Excel sheet name issue