I have a spreadsheet that builds a chart from an imported CSV data file. The CSV imports into Sheet1. Sheet2 formats the CSV data into data series for the chart which is in Sheet3. The chart is dynamic and updates as new data rows are added to Sheet 2.
Whenever I import new data I have to count the new records in Sheet1 then copy down the formulas in sheet 2 to the required number of rows to make the graph update.
I want this to be automatic. I've tried copying the formulas down into blank rows but that returns errors that break the chart. Also tried converting the data in sheet 2 to a table hoping it would auto-expand but that doesn't happen.
I could probably kludge some VBA to check the last row in Sheet1 and copy the formulas in Sheet 2 to that number of rows.
Before I try that - is there a better way built in to Excel to have a table check if its source data has expanded and automagically expand itself to match?