Results 1 to 5 of 5
  1. #1
    jimlac
    Guest

    text reference in formula

    I am trying to find a way to allow text entered into a cell to be used as part of a formula. For example, I have a workbook containing identical worksheets with a different dataset on each worksheet and one worksheet which contains a chart with a range of data I want to graph. I want to enter the name of the worksheet containing the data to be graphed into a cell on the graph worksheet and have that name picked up by formulas in the range being referenced by the chart. Any suggestions?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: text reference in formula

    It's a little unclear what you want. Do you want a text result such as, where d1 contains the spreadsheet name and d2 contains the date:
    =D1&" "&TEXT(D2,"mm/dd/yyy")

    or do you want a formula which refers to a cell in that other spreadsheet, depending on which spreadsheet is named, such as, where d1 contains the spreadsheet name and d3 in that other spreadsheet contains the value you want to reference:
    =INDIRECT("'"&D1&"'!"&"d3")
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    jimlac
    Guest

    Re: text reference in formula

    Thanks for your response. It put me on the right track. I was trying to achieve the second of your examples. This is what worked for me- =INDIRECT(""&C23&"!"&"h3") , where C23 is the cell containing the name of the worksheet which contains the data, and H3 contains the cell reference in that worksheet which contains the value I want to bring in.

    More generally, =INDIRECT(""&C23&"!"&A30&"") does the same thing, but cell A30 contains the cell reference in the data sheet (the H3 in this case). This allows the formula to be copied and have the H3 become H4, H5, etc. and it's easy enough to create that sequence of text using Excel's drag and copy feature.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: text reference in formula

    Glad to help. Note that if your indirectly referenced spreadsheet has a space in the name, the indirect reference will need to be built as
    'my name'!rc
    as opposed to
    myname!rc
    where rc are the row and columns refs
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: text reference in formula

    Another limitation of the Indirect function...
    If the reference is in an external workbook, the workbook must be open or Indirect will return #REF.

Posting Permissions

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