Dynamic chart in Calc

[hendis]

Hi, all! New poster here, so I may be asking for something that isn’t possible, but here goes… (Also, TIA!) Running Calc 7.2.7.2 on a Lenovo laptop with Windows 11.

I’ve got some financial data in a spreadsheet in Calc. I’ve got dates prepopulated in column A, which never changes. Each week, I add 1 row of data to cells B:H at the bottom (bottom of the data, not bottom of the sheet.) In short, the completed range “grows”.

Here’s my question: Can I create a dynamic chart that automagically “shifts” each week to the bottom 12 rows containing data in columns B:H? (Column A is prepopulated with dates far into the future.) Alternatively, can I transfer the bottom 12 rows containing data (in columns B:H) to another worksheet, and create a chart from that?

[dscheikey]

Hello Hendis, your project is not that difficult to implement. There is a function LOOKUP() with which you can determine the last cell described in column B. If you add values later, the value will change. With OFFSET() you can display the range of your last 12 values and the columns A:H.

=OFFSET($Sheet1.A1,LOOKUP(2,1/$Sheet1.$B:$B<>"",ROW($Sheet1.$B:$B))-12,0,12,8)
Please conclude as a matrix formula with CTRL+SHIFT+ENTER.

I have created an example file for you.
If you have further questions, please comment on my answer.

hendis.ods (26,6 KB)

[hendis]

dscheikey,

I can only dream of the day when I’m as smart as you…

But unfortunately, the formula you provided did not work, even after updating it to my current sheet names and data.

However, I still want to thank you for pointing me in the right direction. Something that you said in your email started it all off. "There is a function LOOKUP() with which you can determine the last cell described in column B. If you add values later, the value will change."

This is what I did: I used a LOOKUP function (with TODAY() as the search criteria) to find today’s date (or the latest listed date equal to or before today’s date), putting it (the LOOOKUP function) in the 13th row of a helper sheet. Then, in each row above the 13th row, I simply subtracted 7. This gave me a list of 12 dates in column A of my helper sheet. This list will change as the date returned by the LOOKUP changes, which in turn, will change as TODAY() changes. For the data related to each date, I simply did a VLOOKUP with the calculated date as the search criterion.

Of course, it will take several weeks to determine if my (actually your) solution works, but thank you!

hendis

[dscheikey]

Hello Hendis, I am wondering why this should not work for you with my formula. Have you looked at my example? Did it not work for you? Which LibreOffice version are you using?

The formula should look slightly different in Excel.

=OFFSET(Sheet1!A1,LOOKUP(2,1/--(Sheet1!$B:$B<>""),ROW(Sheet1!$B:$B))-12,0,12,8)

Do they not use LO? Then they should also specify it.