 |
 | 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 / April 2007Help with displaying the contents of the last populate cell.
I have numerous sheets within a book where all cells in column C in all sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you reference both columns P and R hold a monetary value and are formatted as Currency. Is there a way that cell D1 can automatically be populated with the contents of the last cell in column C that has a value in it. E.G. Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200. Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250. Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900. Any help offered would be appreciated. If the cells are always filled up to the last cell meaning if the first filled cell is in C2 and the last cell in C30 all the cells in-between are filled you can simply use =INDEX(C2:C10000,COUNT(C2:C10000)) change the ranges accordingly if you think you will fill more than 10000 cells post back if it's not that way Regards, Peo Sjoblom >I have numerous sheets within a book where all cells in column C in all > sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". For you [quoted text clipped - 12 lines] > > Any help offered would be appreciated. Peo, Thanks just what I wanted. Thank U. > If the cells are always filled up to the last cell meaning if the first > filled cell is in C2 and the last cell in C30 all the cells in-between are [quoted text clipped - 27 lines] > > > > Any help offered would be appreciated. If you want the value of the last numeric value in the column range.... Try something like this: D1: =LOOKUP(10^99,C1:C20) Adjust the range reference to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP > I have numerous sheets within a book where all cells in column C in all > sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you [quoted text clipped - 11 lines] > > Any help offered would be appreciated. Ron, Thar worked a treat as well. I understand the lookup, however, I do not understand the Can you please explain what the 10^99, can you please explain. If possible, can you also explain Peo solution. Thank you for your time. > If you want the value of the last numeric value in the column range.... > [quoted text clipped - 25 lines] > > > > Any help offered would be appreciated. Through a fortunate quirk in the LOOKUP function, if you use it to find a value that is greater than any other value in the list...it returns the last item of that type (text or numeric) in the list. Excel's maximum possible number is 9.99999999999999E307, but I use 10^99 to avoid all that typing. If you were looking for the last text value in a column range, this would work: =LOOKUP(REPT("z",255),C2:C20) Note: REPT("z",255) returns a string of 255 z's Regarding Peo's solution, the COUNT function returns the count of numeric cells. As long as the series of numeric values is contiguous (no blanks or text within the list), it returns the position of the last numeric cell within the list. In Peo's application, the INDEX function returns the n-th item in C2:C10000, where "n" is the count of numeric cells. I hope that helps. (Post back if you have more questions) *********** Regards, Ron XL2002, WinXP > Ron, > [quoted text clipped - 36 lines] > > > > > > Any help offered would be appreciated. Ron, Thank U for explaining. So much to learn and put into pratice. Regards > Through a fortunate quirk in the LOOKUP function, if you use it to find a > value that is greater than any other value in the list...it returns the last [quoted text clipped - 62 lines] > > > > > > > > Any help offered would be appreciated. "<<<Through a fortunate quirk in the LOOKUP function>>>" I wouldn't exactly describe it as a "quirk".<g> I believe it's more like a computer program "blindly" following the code of the author. Since Lookup() is *supposed* to be properly used on *only* an ascending ordered list, *AND* If Lookup() can't find the lookup value, it uses the largest value in the array (list) that is less than or equal to lookup value, Lookup() assumes the *largest* value is the *LAST* value, since it is programmed to believe the list is sorted, ascending. And we make the lookup value larger then any number that would *normally* exist in the list: 10^99 - 99^99 Or we make it as large as any number which *can* exist in the list: 9.99999999999999E307  SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Through a fortunate quirk in the LOOKUP function, if you use it to find a > value that is greater than any other value in the list...it returns the [quoted text clipped - 72 lines] >> > > >> > > Any help offered would be appreciated. You didn't like "quirk", RD? It seems like we both described the same function behavior. However, I apologize to LOOKUP() if I disparaged it in any way. <vbg> Best Regards, Ron > "<<<Through a fortunate quirk in the LOOKUP function>>>" > [quoted text clipped - 97 lines] >>> > > >>> > > Any help offered would be appreciated. Come to think of it, you're label is probably appropo.<bg> SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > You didn't like "quirk", RD? > [quoted text clipped - 109 lines] >>>> > > >>>> > > Any help offered would be appreciated.
|
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.