Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula follows the most recent entry in a column???
I have a simple formula =sum(a16), that I use to collect data from cell A16
and place it in cell B32. If I were to add data to A17 (and eventually A18, A19, A20, etc.) how would I structure a formula to automatically collect the data from the next cell every time I make a new entry and place that data in B32 without rewriting the original formula each time to specify the next cell? Thanks in advance for your help. lb303910 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula follows the most recent entry in a column???
Try something like this:
B32: =LOOKUP(10^99,A:A) That formula returns that last numeric entry in Col_A Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "lb303910" wrote: I have a simple formula =sum(a16), that I use to collect data from cell A16 and place it in cell B32. If I were to add data to A17 (and eventually A18, A19, A20, etc.) how would I structure a formula to automatically collect the data from the next cell every time I make a new entry and place that data in B32 without rewriting the original formula each time to specify the next cell? Thanks in advance for your help. lb303910 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula follows the most recent entry in a column???
One way
=SUM(A16:INDEX(A16:A65536,COUNT(A16:A65536))) assuming you do not leave any empty cells in-between -- Regards, Peo Sjoblom http://nwexcelsolutions.com "lb303910" wrote in message ... I have a simple formula =sum(a16), that I use to collect data from cell A16 and place it in cell B32. If I were to add data to A17 (and eventually A18, A19, A20, etc.) how would I structure a formula to automatically collect the data from the next cell every time I make a new entry and place that data in B32 without rewriting the original formula each time to specify the next cell? Thanks in advance for your help. lb303910 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula follows the most recent entry in a column???
If you're only looking to keep a running total in cell B32, would something
like this work for you? B32: =SUM($A$16:$A$65536) *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: Try something like this: B32: =LOOKUP(10^99,A:A) That formula returns that last numeric entry in Col_A Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "lb303910" wrote: I have a simple formula =sum(a16), that I use to collect data from cell A16 and place it in cell B32. If I were to add data to A17 (and eventually A18, A19, A20, etc.) how would I structure a formula to automatically collect the data from the next cell every time I make a new entry and place that data in B32 without rewriting the original formula each time to specify the next cell? Thanks in advance for your help. lb303910 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula follows the most recent entry in a column???
Thank You Ron,
This works like a charm. After reading the description of the lookup command I can understand the concept you are using. However, I can't quite figure out why you chose the "10^99" argument. If you get a chance to share that info with me I'd appreciate it. Also, your other suggestion did not achieve what I was hoping for but after re-reading my question, I clearly did not provide exactly what I was trying to do. Thanks again for your help. Larry "Ron Coderre" wrote: Try something like this: B32: =LOOKUP(10^99,A:A) That formula returns that last numeric entry in Col_A Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "lb303910" wrote: I have a simple formula =sum(a16), that I use to collect data from cell A16 and place it in cell B32. If I were to add data to A17 (and eventually A18, A19, A20, etc.) how would I structure a formula to automatically collect the data from the next cell every time I make a new entry and place that data in B32 without rewriting the original formula each time to specify the next cell? Thanks in advance for your help. lb303910 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula follows the most recent entry in a column???
Thank You Peo,
This is a very useful formula and it did answer a question I had but not the question I wrote about. After re-reading my question I can see where I clearly did not explain what I was trying to achieve. Thanks again for your help, Larry "Peo Sjoblom" wrote: One way =SUM(A16:INDEX(A16:A65536,COUNT(A16:A65536))) assuming you do not leave any empty cells in-between -- Regards, Peo Sjoblom http://nwexcelsolutions.com "lb303910" wrote in message ... I have a simple formula =sum(a16), that I use to collect data from cell A16 and place it in cell B32. If I were to add data to A17 (and eventually A18, A19, A20, etc.) how would I structure a formula to automatically collect the data from the next cell every time I make a new entry and place that data in B32 without rewriting the original formula each time to specify the next cell? Thanks in advance for your help. lb303910 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula follows the most recent entry in a column???
Hi, Larry
Regarding: 10^99 in B32: =LOOKUP(10^99,A:A) For that formula to work, the first argument of the LOOKUP function must be a number that is greater than any potential value in the lookup range. In that situation, the LOOKUP function returns the last numeric value in Col_A. Technically, to guarantee that no value in Col_A will be larger than the argument value the largest number that Excel can recognize would be used: 9.99999999999999E+307 From a practical standpoint (and mostly to facilitate formula readability), I used 10^99. I made the assumption that no cell in your worksheet would contain a number as large as 10 to the 99th power (1 with 99 zeroes). If you had mentioned that your worksheet pertained to an astronomy problem, I might have gone with 9.99999999999999E+307. I hope that helps. *********** Regards, Ron XL2002, WinXP-Pro "lb303910" wrote: Thank You Ron, This works like a charm. After reading the description of the lookup command I can understand the concept you are using. However, I can't quite figure out why you chose the "10^99" argument. If you get a chance to share that info with me I'd appreciate it. Also, your other suggestion did not achieve what I was hoping for but after re-reading my question, I clearly did not provide exactly what I was trying to do. Thanks again for your help. Larry "Ron Coderre" wrote: Try something like this: B32: =LOOKUP(10^99,A:A) That formula returns that last numeric entry in Col_A Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "lb303910" wrote: I have a simple formula =sum(a16), that I use to collect data from cell A16 and place it in cell B32. If I were to add data to A17 (and eventually A18, A19, A20, etc.) how would I structure a formula to automatically collect the data from the next cell every time I make a new entry and place that data in B32 without rewriting the original formula each time to specify the next cell? Thanks in advance for your help. lb303910 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do i make a formula reference the last entry of a column | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |