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

[megslow]

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!

 

[megslow]

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*

 

[megslow]

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?

 

[Mark W.]

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?

 

[megslow]

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

 

[Aladin Akyurek]

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

 

[Mark W.]

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

Aladin

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

 

[Aladin Akyurek]

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.