+ Reply to Thread
Results 1 to 3 of 3

What do single quotes and ! mean in the INDIRECT function

  1. #1
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    What do single quotes and ! mean in the INDIRECT function

    Hello!

    Here is the example of the INDIRECT function that returns the value of the specific cell in the specific sheet.

    =INDIRECT("'"&"SheetName"&"'!B2")

    (1) What is the role and the meaning of the first single quote surrounded by double quotes?

    (2) What is the role and the meaning of the second single quote and the following exclamation mark?

    Thank you very much!

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: What do single quotes and ! mean in the INDIRECT function

    hi Vitalite. try these steps to understand why
    1. open a new workbook in Excel.
    2. create a worksheet named Test WS
    3. go to Sheet1, cell A1 and press equals sign to do a formula
    =
    4. select Test WS and refer to B2. press ENTER. it should look like that:
    ='Test WS'!B2

    now create another worksheet if you don't have a Sheet2. Name it Sheet2. Do similar steps from 3 & 4
    go to Sheet1, cell B1 and press equals sign to do a formula
    =
    select Sheet2 and refer to B2. press ENTER. it should look like that:
    =Sheet2!B2

    notice the difference between those 2 formulas? if you have a worksheet name with space, it will identify the whole thing by putting single quotes surrounding it:
    'Test WS'
    if you don't have a space in your worksheet name, then it won't have these single quotes:
    Sheet2

    so that answers part of your question 1 on the single quotes. now to the double quotes. this is needed in excel formulas to denote you want a text. for eg, if you want to show a text "abc" in a formula, you have to use:
    ="abc"
    you cannot input:
    =abc
    that will give you an error.

    and for question 2, the exclamation mark is to differentiate the worksheet name and the cell.

    so your INDIRECT formula is saying
    i want a text of a single quote ("'"), combined (&) with a text of a worksheet name ("SheetName") and combined with a text of exclamation mark and the cell B2 ("'!B2")

    and if you dont know the use of INDIRECT, combining the above text simply gives you... a text. try this:
    ="'"&"SheetName"&"'!B2"
    it returns:
    'SheetName'!B2
    so INDIRECT does not return you a text, but what is inside the worksheet 'SheetName'!B2

    so if you have absorbed the above well, you could actually do without the single quote because SheetName does not have spaces
    =INDIRECT("SheetName!B2")
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: What do single quotes and ! mean in the INDIRECT function

    Quote Originally Posted by benishiryo View Post
    hi Vitalite. try these steps to understand why
    [snip]
    Thank you very much for this detailed and very helpful explanation. Now I know. :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 12-28-2014, 02:43 PM
  2. Single Quotes in HTML links
    By AndianAtWork in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-18-2012, 07:27 PM
  3. Save As .csv - Issue With Single Quotes
    By mattydalton in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-28-2010, 03:54 AM
  4. Adding single Quotes to each cell
    By vijaykdp in forum Excel General
    Replies: 1
    Last Post: 10-07-2010, 10:51 AM
  5. Application.Run - Spaces and single quotes
    By Phil_V in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-03-2009, 06:46 AM
  6. single and double quotes
    By MCCCLXXXV in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2008, 06:05 PM
  7. my code is adding single quotes
    By DKY in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2005, 05:48 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1