Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: formula works BUT NOT ALL THE TIME!!!! please help troubles

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Chattanooga, TN
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    After posting my query on this message board (I needed to show the last entered value in any given column in a cell at the top of the sheet), I was given the following formula:

    =INDEX(F:F,MATCH(9.99999999999999E+307,F:F))

    where "F" is the column in which I am looking for the information. Well, this formula is great except that it does not work on some of my files. I have looked for patterns but have found none. It is not because of an old file format because it does not work on some files created under a month ago.

    I need either a fix to make it work on all sheets or a new formula that doesn't have this problem. Help please!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-03 05:46, megslow wrote:
    After posting my query on this message board (I needed to show the last entered value in any given column in a cell at the top of the sheet), I was given the following formula:

    =INDEX(F:F,MATCH(9.99999999999999E+307,F:F))

    where "F" is the column in which I am looking for the information. Well, this formula is great except that it does not work on some of my files. I have looked for patterns but have found none. It is not because of an old file format because it does not work on some files created under a month ago.

    I need either a fix to make it work on all sheets or a new formula that doesn't have this problem. Help please!
    In what way does it not work?
    What error message are you getting?
    "Have a good time......all the time"
    Ian Mac

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Chattanooga, TN
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I do not receive an error message... it simply fills in the cell with all zeroes (ex: if the value should be 12.84, it comes up as 0.00).

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you try with this formula.

    if you want the data in say cell e1 then

    write a formula in e1 as

    =offset(e1,counta(f:f),1)

    I hope this will work.

    ni****h desai
    http://www.pexcel.com



  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-03 05:57, megslow wrote:
    I do not receive an error message... it simply fills in the cell with all zeroes (ex: if the value should be 12.84, it comes up as 0.00).
    is that 0:00 or 0.00
    Have you tried changing the Format of the Cell?
    Sounds like a Time/General number problem.
    "Have a good time......all the time"
    Ian Mac

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-03 05:57, megslow wrote:
    I do not receive an error message... it simply fills in the cell with all zeroes (ex: if the value should be 12.84, it comes up as 0.00).
    is that 0:00 or 0.00
    Have you tried changing the Format of the Cell?
    Sounds like a Time/General number problem.
    "Have a good time......all the time"
    Ian Mac

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Chattanooga, TN
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nisht: I tried that formula but it returned to me a value of "0"

    Ian: It returns "0.00" And it is not a formatting issue as I have it set correctly as "number" set to two decimal places.

    ???

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry ..i forgot to write... minus 1

    try now..

    =offset(e1,counta(f:f)-1,1)

    ni****h desai
    http://www.pexcel.com




  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-03 05:46, megslow wrote:
    After posting my query on this message board (I needed to show the last entered value in any given column in a cell at the top of the sheet), I was given the following formula:

    =INDEX(F:F,MATCH(9.99999999999999E+307,F:F))

    where "F" is the column in which I am looking for the information. Well, this formula is great except that it does not work on some of my files. I have looked for patterns but have found none. It is not because of an old file format because it does not work on some files created under a month ago.

    I need either a fix to make it work on all sheets or a new formula that doesn't have this problem. Help please!
    The formula should simply work if F is of numeric type. I'd be interested in looking at a file, where you have a problem.

    Aladin

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Megslow, the formula, =INDEX(F:F,MATCH(9.99999999999999E+307,F:F)), should work just fine. I believe that you have a stray 0 somewhere in column F perhaps well beyond the row containing what you believe to be the last entry. Do this...

    1. Select the cell containing what you believe to be the last entry.
    2. Press Control+Down Arrow.

    Is cell F65536 selected? If not you may have found a stray entry -- clear the contents of this cell. Repeat steps 1 thru 2 above (clearing all stray values) until you reach F65536. Now, does the formula produce the correct results?

    [ This Message was edited by: Mark W. on 2002-04-03 06:46 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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