Formula to extract most recently recorded data
Posted by Ernie on November 19, 2001 11:29 AM
I need a way to extract the cell data from the last column which contains text. For example:
STORAGE #1 STORAGE #2 STORAGE #3 STORAGE #4
A2 text = "StA"; B2 = "StB"; C2 = "StB"; D2 = (blank)
A3 text = "StL"; B3 =(blank);C3 =(blank);D3 = (blank)
A4 text = "StT"; B4 = "StU"; C4 = "StV"; D4 = "StW"
In column F, I need a formula producing the most recent Storage Location as follows:
F2 = StB F3 = StL F4 = StW
Posted by Aladin Akyurek on November 19, 2001 11:49 AM
In F2 array-enter: =OFFSET(A2,,,SUM(MAX((ISTEXT(A2:D2))*(COLUMN(A2:D2)))))
Copy down this as far as needed. To array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.
Aladin
==========
Posted by CMorrigu on November 19, 2001 11:51 AM
I would probably get the ActiveCell.SpecialCells(xlLastCell).Column, then step backwards until activecell.value is not nothing, and copy/paste that in cells(activecell.row, 6).Shadow Source
Posted by Ernie on November 19, 2001 12:25 PM
Your formula returns value A2...How do I get it to return value C2? The formula must determine that C2 is the last non-blank column out of Columns A thru D.
Posted by Aladin Akyurek on November 19, 2001 12:36 PM
Ernie --
Quite right. Try this array-formula instead:
=INDIRECT(ADDRESS(ROW(),SUM(MAX((ISTEXT(A2:D2))*(COLUMN(A2:D2))))))
Aladin
========
Posted by Aladin Akyurek on November 19, 2001 1:08 PM
By the way...
the array-formula with OFFSET should have been:
=OFFSET(A2,,SUM(MAX((ISTEXT(A2:D2))*(COLUMN(A2:D2))))-1)
Aladin
=========
Posted by Ernie on November 20, 2001 7:00 AM
Re: Formula works perfectly.....question on searching
The array-formula =INDIRECT(ADDRESS etc.....) works perfectly. Thanks!
Next question...Surely a similar inquiry has been answered on this message board in the past. Is there a way to search any word or phrase ever posted to this message board?
By now it is obvious this is the first time I've visited / posted to this board...but it is great..I got what I was looking for. Thanks again.
Posted by Aladin Akyurek on November 20, 2001 8:12 AM
Searching message board
> Next question...Surely a similar inquiry has been answered on this message board in the past. Is there a way to search any word or phrase ever posted to this message board?
===================================
Posted by Cory on August 08, 2001 at 12:50:04:
By request, I'm reposting this:
To search MrExcel a little easier, go to www.AllTheWeb.com and change the language option to english. Type in Mrexcel followed by the subject of what your looking for, i.e.:
mrexcel formatting
That should return pages from mrexcel's message board (archive or not), or if it only returns one, there should be the option for "more pages like this" which will return info from the message board specifically...
Cory
============================
