8

Please look at this pictureenter image description here

By this formula both A1 and A2 should display the value of Sheet Space!E8 & Sheet Space!E9 cell respectively.

But as the Sheet Space Worksheet has space in it's name and I can't add apostrophe ' to sheet name manually (as the sheet name is reproducing Dynamically).

Please note that I can't add apostrophe in C1 manually as Sheet Space in C1 also comes dynamically from another cell.

So how can I edit the formula so that It will not display #REF! in A1 and A2?

12

This looks like your previous problem with the added twist that the sheet name consists of several words. Excel handles those references by surrounding the sheet name in single quotes (apostrophes). So the reference string in cell A1 would be:

='Sheet Space'!E8

Converting that to an INDIRECT reference you can copy down the column would look like this:

=INDIRECT("'"&$C$1&"'!E"&ROW()+7)

You were on the right track with what you needed to do. With INDIRECT, you can build the cell reference with a combination of literal strings, cell references, and formulas. So you just build what you need as a string.

BTW, those single quotes don't hurt anything if the sheet name doesn't contain spaces. So this version of the formula can be used with any kind of sheet name in C1.

2
  • Is there a reason to use & vs. CONCATENATE other than being shorter? (Personally I like using literal operators for my literals & strings, but I'm curious about Excel.) Dec 23 '20 at 18:53
  • @TannerStrunk, it's strictly personal preference. Excel converts either representation to the same internal form for processing.
    – fixer1234
    Dec 24 '20 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.