Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refencing and inserting another cell (Excel 2003)

    I have a spreadsheet (please see attached) that goes from B5 to J13. The range B7:B13 contains Row labels. The range D5:J5 contains dates. D7 to J13 contain numbers, some of which are negative. What formula can be inserted into C7 to show the first negative number's corresponding (column) date. For example, in row 7, if F7 contains the first negative number (left to right), then I'd like the formula to look above in cell F5 and place that corresponding date in C7. Any help on this would be appreciated and thanks much in advance.
    Bob W.
    Attached Files Attached Files

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

    Re: Refencing and inserting another cell (Excel 2003)

    You can use this formula in C7 and fill down:

    =INDEX($D$5:$J$5,IF(ISERROR(MATCH(0,D7:J7,-1)),1,IF(MATCH(0,D7:J7,-1)=COUNTA(D7:J7),#N/A,MATCH(0,D7:J7,-1)+1)))

    MATCH(0,D7:J7,-1) will return the index of the last non-negative number, or #N/A if the first number is already negative. If there are no negative numbers, MATCH returns the number of cells.
    ISERROR translates #N/A to 1 (the first number is negative).
    A result that equals the number of cells is translated to #N/A (no negative number found).
    Other results are increased by 1 to go from the last non-negative number to the first negative one.
    INDEX is used to look up the corresponding date.

    See attached version.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refencing and inserting another cell (Excel 20

    Thanks Hans. Works like a charm. You certainly are the guru of Microsoft. I have another quick question, if you don't mind. In Word, you've created a table that goes to a 2nd page and, in the process, splits a row in the table. In order to print this properly with a complete row starting page two, you split the table. Many moves later (too much to use the undo feature), you decide you want to make your two page table a one page htm file. How can you re-connect your two tables back into one. After much experimentation I was able to right click in the right spot and was given this choice; but I can't find it anymore. Thanks again in advance for any info you can provide.
    Bob W.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refencing and inserting another cell (Excel 20

    Hi Bob

    Probably not the right way but I highlight the offending part of the table and Ctrl X, place my cursor immediately below what is left of the table and Ctrl C, that should do but id there is a gap between the tables you can place the cursor between them and Del until they are joined.

    HTH

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Refencing and inserting another cell (Excel 20

    For the future: if you have a question about an unrelated subject, please start a new thread in the appropriate forum. It's very confusing for other Loungers if we would ask and answer Word questions in the Excel forum, Excel questions in the Outlook forum etc.

    (Table rows in Word have a property Allow Row to Break Across Pages; if you clear this check box, Word will not split a row unless it is longer than one page)

Posting Permissions

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