Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

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

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

    Default

    Do this...

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

    [ This Message was edited by: Mark W. on 2002-04-03 06:46 ]
    I tried the above. There is no other data below the final value in that column. *sigh*

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

    Default

    On 2002-04-03 06:19, nisht wrote:
    sorry ..i forgot to write... minus 1

    try now..

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

    ni****h desai
    http://www.pexcel.com
    This formula is good... unfortunately it returns the first value in the column whereas I need the last value. Can it be modified to give me that?

  3. #13
    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, by chance do you have any hidden rows? My previously stated exploratory approach wouldn't have found 'em. You could select all rows and unhide 'em. Or, select column F:F, choose the Edit | Find... menu command and search for 0. Do you get a "Microsoft cannot find..." message?

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

    Default

    On 2002-04-03 09:20, Mark W. wrote:
    Megslow, by chance do you have any hidden rows? My previously stated exploratory approach wouldn't have found 'em. You could select all rows and unhide 'em. Or, select column F:F, choose the Edit | Find... menu command and search for 0. Do you get a "Microsoft cannot find..." message?
    I "unhid" any rows and columns. I deleted one previously hidden columns. I searched column F for zero values but none were found. The formula still returns zeroes.
    Thank you for taking time on this. : )
    Meg.

    [ This Message was edited by: megslow on 2002-04-03 10:04 ]

  5. #15
    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 06:43, Mark W. wrote:
    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 ]
    Megslow has sent me a copy of a file with the problem:

    Control+Down Arrow takes me straight to row 65535 in F. LEN(F65535) & CODE(F65535) both give #VALUE!.

    Typing TRUE in a cell in the relevant sheet displays 1. I couldn't find any custom formatting applied to the whole sheet.

    I've no idea how this sheet is messed up as it is.

    Inserted a new sheet in the WB and cut the relevant area from the problem sheet and pasted in the new sheet. Everything is now as it shoud be.

    Any clue about what might have happened to this problem sheet?

    Aladin

  6. #16
    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


    Any clue about what might have happened to this problem sheet?

    Aladin
    Aladin, what does =MATCH(9.99999999999999E+307,F:F) return?

  7. #17
    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 11:52, Mark W. wrote:

    Any clue about what might have happened to this problem sheet?

    Aladin
    Aladin, what does =MATCH(9.99999999999999E+307,F:F) return?
    Right. Forgat to mention that: 65535.00, with formatting as number (INDEX is expected to return a 2-decimal number). It returns Excel's last row.




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
  •