Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Literal '&' (Excel 2k, SP3)

    I'm using a combination of OFFSET and MATCH functions to return data from one worksheet to another (the worksheet is a result of a web query, so I have no control over the titles) without doing the searching and cell-referencing by hand. It works well unless there is an ambersand (&) in the title, for example "C&C." Using the formula builder, it appears MATCH is looking for "CC." I tried using the wildcard trick of inserting a tilde (~) before and after the ambersand, but then the formula builder shows MATCH looking for "C~~C."

    Is there any way around this limitation?

  2. #2
    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: Literal '&' (Excel 2k, SP3)

    Hi,
    Try doubling the ampersand - i.e. use C&&C
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Literal '&' (Excel 2k, SP3)

    Thanks, Rory, for the quick reply.

    Unfortunately, I still get an #NA error. If I change "C&C" to "CandC" it works just fine.

    Here is the formula I'm using:

    <hr>=OFFSET(Production!$A$1,MATCH("BNT",Sheet2!$A: $A,0)-1,MATCH("C&C",Sheet2!$1:$1,0)+MONTH(AC$2)-3,1,1)<hr>

    The intent is to find the row title "BNT" and column titled "C&C." Since there are values for each month of the year, MONTH shifts the appropriate number of columns to return the value in the cell.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Literal '&' (Excel 2k, SP3)

    Could you upload a workbook that shows the problem?
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Literal '&' (Excel 2k, SP3)

    Wow; you're fast!

    I was just cleaning the spreadsheet to upload and post. In doing so, I shortened several titles and deleted some rows, columns, and sheets. I tried to replicate the problem, and I can't. In fact it works with just one ambersand! I'm wondering if it might have something to do with the spaces or parentheses in the titles. Hopefully, I wasn't simply mis-spelling something. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Regardless, thanks for the help!

  6. #6
    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: Literal '&' (Excel 2k, SP3)

    Ah - I tried to replicate your problem in XL2002 and couldn't but thought it was perhaps a limitation of XL2000. (similar to trying to use an ampersand in a file header) I guess not! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Let us know if you still have an issue.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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