|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
Apr 3rd, 2002, 06:46 AM
|
#1 |
|
New Member
Join Date: Mar 2002
Location: Chattanooga, TN
Posts: 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! |
|
|
|
Apr 3rd, 2002, 06:53 AM
|
#2 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: Newcastle, UK
Posts: 1,174
|
Quote:
What error message are you getting?
__________________
"Have a good time......all the time" Ian Mac |
|
|
|
|
|
Apr 3rd, 2002, 06:57 AM
|
#3 |
|
New Member
Join Date: Mar 2002
Location: Chattanooga, TN
Posts: 35
|
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).
|
|
|
|
|
Apr 3rd, 2002, 07:05 AM
|
#4 |
|
Board Regular
Join Date: Feb 2002
Location: Ahmedabad Gujarat
Posts: 303
|
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. nishith desai http://www.pexcel.com |
|
|
|
|
Apr 3rd, 2002, 07:05 AM
|
#5 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: Newcastle, UK
Posts: 1,174
|
Quote:
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 |
|
|
|
|
|
Apr 3rd, 2002, 07:06 AM
|
#6 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: Newcastle, UK
Posts: 1,174
|
Quote:
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 |
|
|
|
|
|
Apr 3rd, 2002, 07:11 AM
|
#7 |
|
New Member
Join Date: Mar 2002
Location: Chattanooga, TN
Posts: 35
|
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. ??? |
|
|
|
|
Apr 3rd, 2002, 07:19 AM
|
#8 |
|
Board Regular
Join Date: Feb 2002
Location: Ahmedabad Gujarat
Posts: 303
|
sorry ..i forgot to write... minus 1
try now.. =offset(e1,counta(f:f)-1,1) nishith desai http://www.pexcel.com |
|
|
|
|
Apr 3rd, 2002, 07:35 AM
|
#9 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
Quote:
Aladin |
|
|
|
|
|
Apr 3rd, 2002, 07:43 AM
|
#10 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Austin, Texas USA
Posts: 11,654
|
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 ] |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
