Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
lb303910
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
lb303910
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
lb303910
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
How do i make a formula reference the last entry of a column Knightrider Excel Worksheet Functions 2 June 1st 05 04:29 AM
Return Count for LAST NonBlank Cell in each Row Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM


All times are GMT +1. The time now is 12:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"