How to create a Line Chart to automatically include data from new rows entered in a sheet.

How to create a Line Chart to automatically include data from new rows entered in a sheet.

I'm trying to create Line Chart widget on a Dashboard, to automatically update itself as the rows are added into the source sheet.

How this was set up:

  1. In the original data sheet, the actual and expected performance of a parent level tasks are calculated based on formulas.
  2. Every week this task is copied over to another sheet via automation, just to show the performance for a particular week. This sheet is the source sheet of the chart.
  3. A chart is then built from the data in the source sheet.

Problem:

The chart doesn't capture the data when new rows are automatically added to the sheet. How to fix this ?

Images attached for reference.





Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    The only way to "fix" this would be to "prep" a sheet and use cross sheet formulas to pull the data.


    Basically you would set up a column that will replicate the row number on your sheet that the rows are copied to.

    Then you would set up a series of cross sheet formulas and dragfill them down to account for the maximum number of anticipated rows that you would use in your chart.

    Blank rows won't leave huge gaps on the chart, so it will only look like it is updating with new rows when in reality it is already accounting for them.

  • Hi Paul,

    Could you please elaborate more, not following this ?


    Thanks,

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Mohammed Sulaiman Which portion do you need more detail on?

  • Hello Paul,

    In the above example, the original data sheet itself is the prep sheet, the data is pulled from another sheet by cell linking.

    This row with the data is copied over to the source sheet of the graph at regular intervals.


    I'm unable to follow this part :

    "Basically you would set up a column that will replicate the row number on your sheet that the rows are copied to.

    Then you would set up a series of cross sheet formulas and drag-fill them down to account for the maximum number of anticipated rows that you would use in your chart."

    Please elaborate more.


    Thanks,

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    When you copy the data over to the graph source sheet, how are you copying it? Is it manually, or are you adding new rows, or...?

  • Through automation, every week the row is copied over to the source sheet of the graph.

    And the date in the source sheet is automatically registered when the row is copied over.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. Copy Row inserts a new row. You are going to need to set up a text/number column on the destination sheet that replicates the row number. You will then need to create a new sheet that also replicates the row number and populate this into all of the rows you anticipate needing.


    From there you can use cross sheet references to pull the data from the target sheet to the new sheet as it is added.


    The chart would be built looking at this new sheet and encompass all rows including those that are blank. Then as the data is populated, the chart will update.

Sign In or Register to comment.