 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Copy last value in range to new cell
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.  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi All, > [quoted text clipped - 12 lines] > Any help greatly appreciated > Marty... 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 > =INDEX(M21:Y21,1,MAX(IF(M21:Y21<>"",COLUMN(M21:Y21)))-MIN(COLUMN(M21:Y21))+1) > [quoted text clipped - 20 lines] >> Any help greatly appreciated >> Marty... Another option perhaps - In cell Z21:- =OFFSET($M$21,,COUNTA(M21:Y21)-1) SignatureRegards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------- It's easier to beg forgiveness than ask permission :-) > Hi All, > [quoted text clipped - 12 lines] > Any help greatly appreciated > Marty... but fails if there are gaps. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Another option perhaps - In cell Z21:- > [quoted text clipped - 19 lines] >> Any help greatly appreciated >> Marty... 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................... > but fails if there are gaps. > [quoted text clipped - 21 lines] >>> Any help greatly appreciated >>> Marty... Give this regularly entered formula a try... =INDIRECT("R1C"&SUMPRODUCT(MAX(COLUMN(M21:Y21)*(M21:Y21<>""))),FALSE) Rick > Hi All, > [quoted text clipped - 12 lines] > Any help greatly appreciated > Marty... 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 > Give this regularly entered formula a try... > [quoted text clipped - 18 lines] >> Any help greatly appreciated >> Marty... Another alternative... =LOOKUP(2,1/(M21:Y21<>""),M21:Y21) > Hi All, > [quoted text clipped - 12 lines] > Any help greatly appreciated > Marty... > Another alternative... > > =LOOKUP(2,1/(M21:Y21<>""),M21:Y21) Divisions by zero, eh? I like it... nice formula! Mind if I "steal" it for future postings? Rick Thankseveryone!!! It works perfect!!! > Hi All, > [quoted text clipped - 12 lines] > Any help greatly appreciated > Marty... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.