How can I use indirect references for a pivot table's data source?

Question

(This question is mostly about Excel, but it's also relevant for LibreOffice Calc, so I welcome answers about either.)

I have this sheet with many rows, some of which only become relevant at certain times. I have a cell on that sheet indicating which is the last relevant row. Now, I have a PivotTable (and a PivotChart) which I always want to consider the currently-relevant data. Right now, whenever I change the the max relevant row I also change the Pivot Table's source range, manually. I was wondering whether I couldn't automate this by making it somehow use the last-relevant-row indicator cell for an indirect reference.

Answer 1

I would try to add a column to the source table which calculates for each row whether it is relevant or not (e.g. the result is "Relevant" or "Irrelevant"). The basis for the formula is probably what you currently have in:

a cell on that sheet indicating which is the last relevant row

I would also convert the source of the Pivot Table to an Excel Table (Insert ribbon, Table) - this will ensure any formulas get copied as new rows are added.

Then you can add the new "Relevance" column to your Pivot Table or Pivot Chart as a Filter, and choose the "Relevant" entry.