Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Insert a text value at a cross reference! (Excel2000>)

    I need some help on a problem.

    If you look at the attached file, I need to develop a formula to insert the appropriate course into the cross-ref based on date and name. For eg. A formula should calculate to equal "MS exchange server 2003" in cell G2 as Gavin does this course on that date. If no course is assigned for that date, it should return a blank cell.

    I cannot use Pivot tables, as this calculates a sum, count, etc. I need the name of the course for that date and name.
    If any other option is avalable to get this result...please sdvise me.
    Thanx
    Regards,
    Rudi

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

    Re: Insert a text value at a cross reference! (Excel2000>)

    Insert a new column D. In D2, enter this formula:

    =TEXT(A2,"yyyymmdd")&B2

    and fill down as far as needed (for example by double clicking the fill handle in the lower right corner of D2). In G2, enter this formula:

    =IF(ISNA(MATCH(TEXT($F2,"yyyymmdd")&G$1,$D$2:$D$40 ,0)),"",INDEX($C$2:$C$40,MATCH(TEXT($F2,"yyyymmdd" )&G$1,$D$2:$D$40,0)))

    and fill down, then right to P1241. You can now hide column D, it is only needed for the formulas.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert a text value at a cross reference! (Excel2000>)

    Hans, I don't know how you get it right, but it works brilliantly.
    Cheers Pal, have a beer on me! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

Posting Permissions

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