Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Oct 2006
    Location
    Albany, New York, USA
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate a formula (2003)

    Hi folks, first time here. This question was presented to me last week and it's been driving us all crazy. It seems like it should be so simple. This person wants to concatenate three cells (fF4, G4, H4) in Sheet2. The concatenate function is in cell F3. He wants to create a formula (like in D4). Of course cell F3 is formatted as text. How can he change the format so that cell F3 becomes a formula and calls the information from Sheet1? I remember being able to do this in Lotus 123.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenate a formula (2003)

    Welcome to Woody's Lounge!

    You can use the INDIRECT function for this, but with two modifications:
    1) Don't include the = in the concatenation.
    2) Excel interprets the apostrophe before the text Sheet1 in cell G4 as a text indicator, so it is ignored in the formula. So you must either use two apostrophes (not a double quote), or remove the apostrophes altogether.

    The formula in F3 becomes
    <code>
    =INDIRECT(G4&H4)
    </code>
    or
    <code>
    =INDIRECT(CONCATENATE(G4,H4))
    </code>
    You can use the & operator instead of the CONCATENATE function.
    See attached version.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Concatenate a formula (2003)

    Just as a third alternative in addition to Hans' suggestions, you could add the "missing" apostrophe in your formula:
    <code>=INDIRECT("'"&G4&H4)</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Oct 2006
    Location
    Albany, New York, USA
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate a formula (2003)

    Much thanks HansV that works perfectly.

Posting Permissions

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