Auto-updating column to show the last text entry in any row!

Posted by Jonathan E on December 03, 2001 6:13 AM

Does any one know how to display in a cell the last text entry in a row?

Example for cell D30, search from G30 to say BZ30 returning the last non-void entry,

& for D31 searching from G31 to BZ30, etc.

There may be no entry until in a row until say R & then none until a number of columns later.

The idea is to be able to easily see in column D the last entry in all the rows 30 to 60, from columns G to BZ30.

Entries are made on a column/ date basis and older columns ard hidden as they are completed.

Entries need to be displayed in column D whethe hidden or not, if entries are void for any cell then the text in previous column (same row) should be displayed.

Example:

If K30="a", P30="b",
then D30 should display "b",

If K50="w", M50=null entry, M50="x"
and D50 should display "x"

This sounds simple, but I would really appreciate some help.

Posted by Mark W. on December 03, 2001 7:45 AM

{=INDIRECT(ADDRESS(ROW(),MAX(ISTEXT(G30:BZ30)*COLUMN(G30:BZ30))))}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by Jonathan E on December 03, 2001 2:47 PM

Thanks Mark W, this works great, I'm made up now!
& thanks for the note about Control+shift+enter key, I'll obviously need to swop up about these!
Thanks again, Jonathan E.