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

megslow

New Member
Joined
Mar 10, 2002
Messages
35
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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

???
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top