Reading last row of growing table

Hey folks,

I have two questions and would appreciate any help. I use a spreadsheet with columns A to D listing each date's high, low and close of a stock. Column E is the difference between the high and low price. It's a simple sheet, I add a row with the day's values at the end of every day. But it gets complicated in two ways.

First, in another sheet, I have a graph that charts the last 20 dates. Second, I have a word document which I print out which says "the high price for the date #### was ###, the low price was ### etc."

The way I have it set up now is really complicated and I was wondering if anyone had a simpler solution.

Right now I have named the cells in the last row "Xdate" "Xhigh" "Xlow" "Xclose". And the graph reads off a table set up with "Offset(Xdate,-1,0), Offset (xdate, -2, 0) Offset (Xdate, -3, 0) etc etc.. up to (Xdate, -20, 0). Meanwhile the word document also references Xdate, Xhigh etc.

To make sure the X cells are always in the last row, I have to highlight the last row, copy, insert above, and then paste in values in to the last row. This preserves the xnames but it's several steps. I'd love to be able to just copy and paste a new row and have the graph and word doc update automatically.

Is this possible? Will I have to use a macro? Please lemme know if I need to clarify or add any details, many thanks in advance for your help,

AR

I'm having trouble picturing it without a spreadsheet to look at but your Xdate range should be a dynamic range like (assuming you're looking for the last 20)
Xdate = OFFSET(A2,COUNT($A$2:$A$10000)-20,0,20)
Does that help?

http://www.contextures.com/xlNames01.html#Dynamic

Oh wow, that's perfect for the graph!! Thank you! I'm creating a spreadsheet with that method right now, using array formulae and will upload it as soon as I'm done.

That's half of my query quickly (and painlessly) solved, what about linking a cell to Word? Effectively, whereas I use all 20 days for the graph, I'd like just the latest date in the dynamic range for my word doc ( I print it out at the end of each day as a summary sheet of what happened that day).

Aha, actually to get the last value only should I edit the Offset formula to be

Xdate = OFFSET(A2,COUNT($A$2:$A$10000)-1,0,1) ?

Lemme try it...

Yes I would! This is so great, I expected this to be a huge hassle with lots of links and VBA code, so I'd been putting it off for ages, but this is great! I could not be happier, gracias!

Glad everything worked out. FYI, for a single cell, you don't need the last argument, Excel assumes 1 cell so

Xdate = OFFSET(A2,COUNT($A$2:$A$10000)-1,0)