Copy last value in range to new cell

  • Thread starter Thread starter ...ms...
  • Start date Start date
M

...ms...

Hi All,

I am creating a document transmittal form that will show all revisions of a
document.
The range that the revisions are shown for a document is say M21 to Y21.
The revisions are entered starting at M21 and moving to the right in dated
order.
How can I make cell Z21 show the current revision for the document from the
range M21 to Y21.
Note also that the revisions may be in forn 01, 02, 03... or A, B, C...

I thought about a if then loop to stop when it hits a blank cell but it
would be a pretty long formula...

Any help greatly appreciated
Marty...
 
=INDEX(M21:Y21,1,MAX(IF(M21:Y21<>"",COLUMN(M21:Y21)))-MIN(COLUMN(M21:Y21))+1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
but fails if there are gaps.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Give this regularly entered formula a try...

=INDIRECT("R1C"&SUMPRODUCT(MAX(COLUMN(M21:Y21)*(M21:Y21<>""))),FALSE)

Rick
 
Whoops! I grabbed the wrong formula from my test spreadsheet. Here is the
(regularly entered) formula I meant to post...

=INDIRECT("R"&ROWS($1:21)&"C"&SUMPRODUCT(MAX(COLUMN(M21:Y21)*(M21:Y21<>""))),FALSE)

Rick
 
I looked at your formula and the one I posted and decided parts of them
could be combined to eliminate the disadvantage of the of each. My function,
because it uses INDIRECT, is Volatile whereas as yours, as an array entered
formula, requires one to remember to use Ctrl+Shift+Enter to commit it after
any editing sessions. At the cost of one additional character, but
eliminating one function call, here is our two formulas blended together...

=INDEX(M21:Y21,1,SUMPRODUCT(MAX(COLUMN(M21:Y21)*(M21:Y21<>""))-COLUMN(M21)+1))

Rick
 
Another alternative...
=LOOKUP(2,1/(M21:Y21<>""),M21:Y21)
Click to expand...

Divisions by zero, eh? I like it... nice formula! Mind if I "steal" it for
future postings?

Rick
 
True, but reasonable to assume from the OPs initial desire to use a formula
that stops at the first blank cell that it will suffice in this instance :-)

Regards
Ken...................
 
Back
Top