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

Mohammed Sulaiman

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.

Paul Newcome

No. That's why I say that you need to pre-fill rows to account for how many you think you will need.

If you think you will need 25 rows, go ahead and use the row number formula to pre-fill 50. Then you will use formulas with cross sheet references to pull the data over to the chart sheet as it is added to the other sheet, and you would set the chart to look at all 50 rows.

Paul 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.

Mohammed Sulaiman

Hi Paul,

Could you please elaborate more, not following this ?

Thanks,

Paul Newcome

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

Mohammed Sulaiman

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,

Mohammed Sulaiman

@Paul Newcome

Paul 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...?

Mohammed Sulaiman

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 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.

Mohammed Sulaiman

Hi @Paul Newcome ,

There are three sheets,

1. Original Data sheet where there is a parent task, with multiple level of children. Every week, I just need a snapshot of how the overall parent task is performing, and this is calculated through a formula to show Actual vs Expected Performance. This metric is a %.
2. Prep sheet where the data is pulled from the original data sheet by cell linking. No new data is added to this sheet. it just pulls the % through cell linking from the original data sheet. The % changes every time there is an update in the original data sheet.
3. Based on your suggestion, I created a new sheet, which is now the source sheet for the graph, and linked the cells, from the sheet in step 3. But the cells do not link to blank rows. Which leads to the same original problem, of having to manually link the cell, when a new row is copied over in step 3.

The data which I'm trying to show the graph is :

The intention is to have an automated process, of showing the actual performance vs expected graph, week over week.

Am I insane to try this in Smartsheet ?

Paul Newcome

No. That's why I say that you need to pre-fill rows to account for how many you think you will need.

If you think you will need 25 rows, go ahead and use the row number formula to pre-fill 50. Then you will use formulas with cross sheet references to pull the data over to the chart sheet as it is added to the other sheet, and you would set the chart to look at all 50 rows.

Mohammed Sulaiman

I think that worked, had to use the Index and Match formulas to cell link the data from one sheet to another.

Thanks,

Paul Newcome

That is correct. An INDEX/MATCH function could be used. to pull the data. A way to make that a little bit easier is why I mentioned having a column that essentially duplicates the row number. Then you can use a cell reference instead of the MATCH function which requires an extra cross sheet reference and can put a little extra burden on the sheet (depending on the size it may not be noticeable though).