Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to set last/latest value of a line chart to a static cell?
Each day I have to add a value to the NEXT cell in a column on a worsheet
which is the datasource for an Excel line chart. I want to display the last/latest value entered in this range to a static cell in the worksheet (which I will be referencing elsewhere). Since the latest value entered in this range is in a different cell each day (the next cell in the range) how can I capture this latest value - the way the line chart does? Is there a worksheet formula/set of formulas that could accomplish this? One solution I am thinking of is to write a VBA function that checks for the latest value entered in this range and then set the value of this VBA function to my static cell. Can I do this with worksheet formulas? Or should I go with the VBA function idea? Thanks, Rich |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to set last/latest value of a line chart to a static cell?
You could use the OFFSET function to get there. Without knowing what else
you have on your sheet, it's tough to tell you what to do. Let's assume this 1) Your data is in row 2 2) A2: Identifier for the data 3) B2: ...end of data is numeric data that's entered Set a named range to this =OFFSET(Sheet1!$A$2,0,count($2:$2) Once you have that named range working, you can refer to the named range. As you add data, you the "current data" moves to the right. HTH, Barb Reinhardt "Rich" wrote: Each day I have to add a value to the NEXT cell in a column on a worsheet which is the datasource for an Excel line chart. I want to display the last/latest value entered in this range to a static cell in the worksheet (which I will be referencing elsewhere). Since the latest value entered in this range is in a different cell each day (the next cell in the range) how can I capture this latest value - the way the line chart does? Is there a worksheet formula/set of formulas that could accomplish this? One solution I am thinking of is to write a VBA function that checks for the latest value entered in this range and then set the value of this VBA function to my static cell. Can I do this with worksheet formulas? Or should I go with the VBA function idea? Thanks, Rich |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to set last/latest value of a line chart to a static cell?
Thank you for your reply. This sounds like a good idea. Let me expand the
scenario Row A B xVal yVal 1 1/1 3 2 1/2 5 3 1/3 9 4 1/4 13 5 1/5 14 .... I should have asked how do I capture the last/latest value (Numeric value) entered for the datasource of a line chart? I set the datasource y range for the line chart to Column B from row 1 to row 31 for example and the x range to column A from row 1 to 31. I want to display the latest value entered in the B column. Today the latest value is in cell B5. Tommorrow the latest value will be in cell B6.... Would the Offset formula work to display the latest value entered in range(B1:B31) ? How to implement this? Thanks "Barb Reinhardt" wrote: You could use the OFFSET function to get there. Without knowing what else you have on your sheet, it's tough to tell you what to do. Let's assume this 1) Your data is in row 2 2) A2: Identifier for the data 3) B2: ...end of data is numeric data that's entered Set a named range to this =OFFSET(Sheet1!$A$2,0,count($2:$2) Once you have that named range working, you can refer to the named range. As you add data, you the "current data" moves to the right. HTH, Barb Reinhardt "Rich" wrote: Each day I have to add a value to the NEXT cell in a column on a worsheet which is the datasource for an Excel line chart. I want to display the last/latest value entered in this range to a static cell in the worksheet (which I will be referencing elsewhere). Since the latest value entered in this range is in a different cell each day (the next cell in the range) how can I capture this latest value - the way the line chart does? Is there a worksheet formula/set of formulas that could accomplish this? One solution I am thinking of is to write a VBA function that checks for the latest value entered in this range and then set the value of this VBA function to my static cell. Can I do this with worksheet formulas? Or should I go with the VBA function idea? Thanks, Rich |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to set last/latest value of a line chart to a static cell?
Try a named range with this as the definition
=Offset(Sheet1!$B$1,count($B:$B),0) HTH, Barb Reinhardt "Rich" wrote: Thank you for your reply. This sounds like a good idea. Let me expand the scenario Row A B xVal yVal 1 1/1 3 2 1/2 5 3 1/3 9 4 1/4 13 5 1/5 14 ... I should have asked how do I capture the last/latest value (Numeric value) entered for the datasource of a line chart? I set the datasource y range for the line chart to Column B from row 1 to row 31 for example and the x range to column A from row 1 to 31. I want to display the latest value entered in the B column. Today the latest value is in cell B5. Tommorrow the latest value will be in cell B6.... Would the Offset formula work to display the latest value entered in range(B1:B31) ? How to implement this? Thanks "Barb Reinhardt" wrote: You could use the OFFSET function to get there. Without knowing what else you have on your sheet, it's tough to tell you what to do. Let's assume this 1) Your data is in row 2 2) A2: Identifier for the data 3) B2: ...end of data is numeric data that's entered Set a named range to this =OFFSET(Sheet1!$A$2,0,count($2:$2) Once you have that named range working, you can refer to the named range. As you add data, you the "current data" moves to the right. HTH, Barb Reinhardt "Rich" wrote: Each day I have to add a value to the NEXT cell in a column on a worsheet which is the datasource for an Excel line chart. I want to display the last/latest value entered in this range to a static cell in the worksheet (which I will be referencing elsewhere). Since the latest value entered in this range is in a different cell each day (the next cell in the range) how can I capture this latest value - the way the line chart does? Is there a worksheet formula/set of formulas that could accomplish this? One solution I am thinking of is to write a VBA function that checks for the latest value entered in this range and then set the value of this VBA function to my static cell. Can I do this with worksheet formulas? Or should I go with the VBA function idea? Thanks, Rich |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to set last/latest value of a line chart to a static cell?
I created a named range called rangeA
A B -------------- a 1 b 2 c 3 d 4 e 5 I named this range rangeA. Then I pasted your formula in cell E1, but do not get a the latest value for columnB which should be 5. How do I apply your formula to my named range? Thank you for sharing these suggestions. I just need to learn how to do it. "Barb Reinhardt" wrote: Try a named range with this as the definition =Offset(Sheet1!$B$1,count($B:$B),0) HTH, Barb Reinhardt "Rich" wrote: Thank you for your reply. This sounds like a good idea. Let me expand the scenario Row A B xVal yVal 1 1/1 3 2 1/2 5 3 1/3 9 4 1/4 13 5 1/5 14 ... I should have asked how do I capture the last/latest value (Numeric value) entered for the datasource of a line chart? I set the datasource y range for the line chart to Column B from row 1 to row 31 for example and the x range to column A from row 1 to 31. I want to display the latest value entered in the B column. Today the latest value is in cell B5. Tommorrow the latest value will be in cell B6.... Would the Offset formula work to display the latest value entered in range(B1:B31) ? How to implement this? Thanks "Barb Reinhardt" wrote: You could use the OFFSET function to get there. Without knowing what else you have on your sheet, it's tough to tell you what to do. Let's assume this 1) Your data is in row 2 2) A2: Identifier for the data 3) B2: ...end of data is numeric data that's entered Set a named range to this =OFFSET(Sheet1!$A$2,0,count($2:$2) Once you have that named range working, you can refer to the named range. As you add data, you the "current data" moves to the right. HTH, Barb Reinhardt "Rich" wrote: Each day I have to add a value to the NEXT cell in a column on a worsheet which is the datasource for an Excel line chart. I want to display the last/latest value entered in this range to a static cell in the worksheet (which I will be referencing elsewhere). Since the latest value entered in this range is in a different cell each day (the next cell in the range) how can I capture this latest value - the way the line chart does? Is there a worksheet formula/set of formulas that could accomplish this? One solution I am thinking of is to write a VBA function that checks for the latest value entered in this range and then set the value of this VBA function to my static cell. Can I do this with worksheet formulas? Or should I go with the VBA function idea? Thanks, Rich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Latest Taxation Books available at jain book depot LATEST BOOKRELEASES | Excel Worksheet Functions | |||
Is there a way to draw a vertical line from a point on the chart line to the bottom of the chart | Excel Programming | |||
How do I display latest value in chart in the legend w formula? | Charts and Charting in Excel | |||
How do i export a pivot chart as a static chart object? | Charts and Charting in Excel | |||
how do I keep the top line static in excel files | Charts and Charting in Excel |