how to set last/latest value of a line chart to a static cell?

[Rich]

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

 

[Barb Reinhardt]

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]

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]

Try a named range with this as the definition

=Offset(Sheet1!$B$1,count($B:$B),0)


HTH,
Barb Reinhardt

 

[Rich]

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.