Home |
Search |
Today's Posts |
#1
|
|||
|
|||
selecting last entry
I have several cells reading data from a single cell - the last one in a
column - the value of which keeps changing as data is added. How do I ensure that the several cells read only from the last entry in the column, and not from older obsolete entries? |
#2
|
|||
|
|||
This will get you the last value in a column
=INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... I have several cells reading data from a single cell - the last one in a column - the value of which keeps changing as data is added. How do I ensure that the several cells read only from the last entry in the column, and not from older obsolete entries? |
#3
|
|||
|
|||
Many thanks. I stupidly omiteed to add that the list of entries does not
comprise the entire column - only part of it (let's say C5 to c10) after that, there's oodles of other things in the same column. I'd like to know how to restrict the lookup to that small section. "Bob Phillips" wrote: This will get you the last value in a column =INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... I have several cells reading data from a single cell - the last one in a column - the value of which keeps changing as data is added. How do I ensure that the several cells read only from the last entry in the column, and not from older obsolete entries? |
#4
|
|||
|
|||
=INDEX(C5:C10,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9. 99999999999999E+307},C5:C1
0))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... Many thanks. I stupidly omiteed to add that the list of entries does not comprise the entire column - only part of it (let's say C5 to c10) after that, there's oodles of other things in the same column. I'd like to know how to restrict the lookup to that small section. "Bob Phillips" wrote: This will get you the last value in a column =INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... I have several cells reading data from a single cell - the last one in a column - the value of which keeps changing as data is added. How do I ensure that the several cells read only from the last entry in the column, and not from older obsolete entries? |
#5
|
|||
|
|||
Many thanks. I tried this myself, but I got, and still get #N/A which I
understand is a MATCH error value. (The first one worked fine as described, it returned the value from the bottom of the worksheet.) What am I doing wrong with this one? "Bob Phillips" wrote: =INDEX(C5:C10,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9. 99999999999999E+307},C5:C10))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... Many thanks. I stupidly omiteed to add that the list of entries does not comprise the entire column - only part of it (let's say C5 to c10) after that, there's oodles of other things in the same column. I'd like to know how to restrict the lookup to that small section. "Bob Phillips" wrote: This will get you the last value in a column =INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... I have several cells reading data from a single cell - the last one in a column - the value of which keeps changing as data is added. How do I ensure that the several cells read only from the last entry in the column, and not from older obsolete entries? |
#6
|
|||
|
|||
That sounds like you have all numbers or all text.
If all numbers, and always all numbers, use =INDEX(C5:C10,MAX(MATCH(9.99999999999999E+307,C5:C 10))) If all text, and always all text, use =INDEX(C5:C10,MAX(MATCH("ZZZZZZZZZZZZZZZZZZZZ",C5: C10))) If it could be both, but coul;d be all numbers or all text, use =INDEX(C5:C10,MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZ ZZZZZZZ"},C5:C10)),MATCH(9 ..99999999999999E+307,C5:C10),IF(ISERROR(LOOKUP(9. 99999999999999E+307,C5:C10) ),MATCH("ZZZZZZZZZZZZZZZZZZZZ",C5:C10),MATCH({"ZZZ ZZZZZZZZZZZZZZZZZ",9.99999 999999999E+307},C5:C10))))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... Many thanks. I tried this myself, but I got, and still get #N/A which I understand is a MATCH error value. (The first one worked fine as described, it returned the value from the bottom of the worksheet.) What am I doing wrong with this one? "Bob Phillips" wrote: =INDEX(C5:C10,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9. 99999999999999E+307},C5:C1 0))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... Many thanks. I stupidly omiteed to add that the list of entries does not comprise the entire column - only part of it (let's say C5 to c10) after that, there's oodles of other things in the same column. I'd like to know how to restrict the lookup to that small section. "Bob Phillips" wrote: This will get you the last value in a column =INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... I have several cells reading data from a single cell - the last one in a column - the value of which keeps changing as data is added. How do I ensure that the several cells read only from the last entry in the column, and not from older obsolete entries? |
#7
|
|||
|
|||
Perfect! many many thanks.
"Bob Phillips" wrote: That sounds like you have all numbers or all text. If all numbers, and always all numbers, use =INDEX(C5:C10,MAX(MATCH(9.99999999999999E+307,C5:C 10))) If all text, and always all text, use =INDEX(C5:C10,MAX(MATCH("ZZZZZZZZZZZZZZZZZZZZ",C5: C10))) If it could be both, but coul;d be all numbers or all text, use =INDEX(C5:C10,MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZ ZZZZZZZ"},C5:C10)),MATCH(9 ..99999999999999E+307,C5:C10),IF(ISERROR(LOOKUP(9. 99999999999999E+307,C5:C10) ),MATCH("ZZZZZZZZZZZZZZZZZZZZ",C5:C10),MATCH({"ZZZ ZZZZZZZZZZZZZZZZZ",9.99999 999999999E+307},C5:C10))))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... Many thanks. I tried this myself, but I got, and still get #N/A which I understand is a MATCH error value. (The first one worked fine as described, it returned the value from the bottom of the worksheet.) What am I doing wrong with this one? "Bob Phillips" wrote: =INDEX(C5:C10,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9. 99999999999999E+307},C5:C1 0))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... Many thanks. I stupidly omiteed to add that the list of entries does not comprise the entire column - only part of it (let's say C5 to c10) after that, there's oodles of other things in the same column. I'd like to know how to restrict the lookup to that small section. "Bob Phillips" wrote: This will get you the last value in a column =INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... I have several cells reading data from a single cell - the last one in a column - the value of which keeps changing as data is added. How do I ensure that the several cells read only from the last entry in the column, and not from older obsolete entries? |
#8
|
|||
|
|||
If you want to retieve the last numeric value from C5:C10, invoke:
=LOOKUP(9.99999999999999E+307,$C$5:$C$10) If you want to retieve the last text value from C5:C10, invoke: =LOOKUP(REPT("z",255),$C$5:$C$10) The latter will fetch a formula-blank (created by, say, someting like ="") if this text value is the last entry. dihirod wrote: Many thanks. I stupidly omiteed to add that the list of entries does not comprise the entire column - only part of it (let's say C5 to c10) after that, there's oodles of other things in the same column. I'd like to know how to restrict the lookup to that small section. "Bob Phillips" wrote: This will get you the last value in a column =INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.9 9999999999999E+307},A:A))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... I have several cells reading data from a single cell - the last one in a column - the value of which keeps changing as data is added. How do I ensure that the several cells read only from the last entry in the column, and not from older obsolete entries? -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#9
|
|||
|
|||
You could try this for *either* text or numbers:
=LOOKUP(2,1/(1-ISBLANK(C5:C10)),C5:C10) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "dihirod" wrote in message ... Perfect! many many thanks. "Bob Phillips" wrote: That sounds like you have all numbers or all text. If all numbers, and always all numbers, use =INDEX(C5:C10,MAX(MATCH(9.99999999999999E+307,C5:C 10))) If all text, and always all text, use =INDEX(C5:C10,MAX(MATCH("ZZZZZZZZZZZZZZZZZZZZ",C5: C10))) If it could be both, but coul;d be all numbers or all text, use =INDEX(C5:C10,MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZ ZZZZZZZ"},C5:C10)),MATCH(9 ...99999999999999E+307,C5:C10),IF(ISERROR(LOOKUP(9 .99999999999999E+307,C5:C10 ) ),MATCH("ZZZZZZZZZZZZZZZZZZZZ",C5:C10),MATCH({"ZZZ ZZZZZZZZZZZZZZZZZ",9.999 99 999999999E+307},C5:C10))))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... Many thanks. I tried this myself, but I got, and still get #N/A which I understand is a MATCH error value. (The first one worked fine as described, it returned the value from the bottom of the worksheet.) What am I doing wrong with this one? "Bob Phillips" wrote: =INDEX(C5:C10,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9. 99999999999999E+307},C5:C1 0))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... Many thanks. I stupidly omiteed to add that the list of entries does not comprise the entire column - only part of it (let's say C5 to c10) after that, there's oodles of other things in the same column. I'd like to know how to restrict the lookup to that small section. "Bob Phillips" wrote: This will get you the last value in a column =INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A))) -- HTH RP (remove nothere from the email address if mailing direct) "dihirod" wrote in message ... I have several cells reading data from a single cell - the last one in a column - the value of which keeps changing as data is added. How do I ensure that the several cells read only from the last entry in the column, and not from older obsolete entries? |
#10
|
|||
|
|||
Ragdyer wrote: You could try this for *either* text or numbers: =LOOKUP(2,1/(1-ISBLANK(C5:C10)),C5:C10) That means: Return any last entry from C5:C10. |
#11
|
|||
|
|||
Exactly !
Isn't that what was asked for ... in the OP ... and in the subject line?<g Isn't an error return from a formula just as valid a response as a "1" or an "A"? As long as the error is *not* caused by the "checking" formula *itself*! And of course, the magic word in my suggestion was "try", since the OP is the final judge as to the functionality and validity of any submitted suggestion. BUT, I must admit that you do perhaps have a point, in that it would have been better practice on my part to "advise" that *all* formula returns, values as well as errors, will be displayed by my suggestion, as well as *all* keyed in entries. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Aladin Akyurek" wrote in message ... Ragdyer wrote: You could try this for *either* text or numbers: =LOOKUP(2,1/(1-ISBLANK(C5:C10)),C5:C10) That means: Return any last entry from C5:C10. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you create a selection box for data entry within excel | Excel Discussion (Misc queries) | |||
limit text entry in a range of cells | Excel Discussion (Misc queries) | |||
Data Validation / Cell Entry | Excel Discussion (Misc queries) | |||
double-clicking a list entry | Excel Discussion (Misc queries) | |||
Move the last entry in a column to a different cell, when the loc. | Excel Worksheet Functions |