1

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?

Thanks!

3
  • Could you combine sheet1 and sheet 2? Make sheet1 a table where the CSV is imported with the formatting formulas to the right of the import columns? Then use the auto-expanding table as your chart data series? – Mark Fitzgerald May 15 '19 at 9:11
  • That would probably work. It means effectively rebuilding the spreadsheet as all the named ranges for the chart series will move as well. I'll try it if no-one suggests something more elegant :) – Flippertie May 15 '19 at 9:27
  • Rebuilds are good - gives you a chance to think about what you need and get rid of any errors or dross . – Mark Fitzgerald May 15 '19 at 10:13
2

Solved starting with the suggestion from @Mark Fitzgerald

I rebuilt the spreadsheet so the imported CSV data and columns for my chart data ranges were all on the same sheet.

To get the formulas to update dynamically:

  • Keep the data as normal cells NOT part of a Table
  • Add helper columns beside the imported data (no blank columns between)
  • After first data import select any cell in the imported range. On the Data Tab go to Connections and Properties (this is greyed out till you select an imported cell).
  • Here there is a checkbox to "Fill down Formulas in adjacent cells" N.B This is not
    available when you first define the data connection.

After that the formulas in the last row are copied down for any new data lines and deleted if your data import shrinks.

2
  • Glad to see I pointed you in the right direction to solve it yourself. Maybe a new problem solving skill you can share with others sometime? – Mark Fitzgerald May 15 '19 at 11:18
  • I'll do my best :-) – Flippertie May 15 '19 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.