New data not automatically adding to Dashboard with cell linking of full column

Jan Peters

Hi -

I have set up a dashboard with a chart that is pulling in submission dates from a tracking sheet to report # of submissions per week. I've selected the whole column so that it will automatically populate as new data is added. New data is added either via a FORM submission or is manually entered by adding new rows. I've tried the full column selection as well as a range selection from within the column, but either way it is not picking up the new data as it is added and I'm forced to manually update the linking multiple times through-out the week. IS there something that you would recommend I do differently?

Thanks for your help!

Jan

Paul Newcome

You would need to "prep" a second sheet to cover as many entries as you anticipate.

Both sheets would need a Row ID column that will essentially replicate the Row Number.

On the source sheet, you would dragfill this down to the last row and let autofill pull for any new rows added.

=COUNTIFS([Other Column]$1:[Other Column]@row, OR(@cell = "", @cell <> "")

On the Chart Data Sheet you would simply manually enter 1 on the first row and 2 on the second row then dragfill this down to cover the maximum number of anticipated rows. So if you think you are going to have 25, go ahead and pre-fill 50. If you think you are going to have no more than 50, pre-fill 75. Basically you want to pre-fill the maximum number expected and then add some "just in case" buffer.

Then in the first data column, you would use a formula such as

=IFERROR(INDEX({Source Sheet 1st Data Column, [Row ID]@row), "")

The second data column:

=IFERROR(INDEX({Source Sheet 2nd Data Column}, [Row ID]@row), "")

So on and so forth to pull all of the different columns you want.

Dragfill these down to cover all of those rows you prepped with the Row ID.

Then build your chart off of this new sheet and include entire columns.

Blank rows will not leave huge gaps in your chart, but since they are already being referenced by your chart, they will appear as they are populated by the formulas pulling from your source sheet.

Genevieve P.

Hi Jan,

I would suggest using a cross-sheet formula instead of cell-linking. For example, you can use the COUNTIFS function to return a count of the number of rows that meet different criteria... for example if a date falls between two specified dates. You would want to reference the entire column in your formula so that new submissions coming will will be automatically updated.

To help you build this, would you be able to post 3 screen captures? (Blocking out any sensitive data)

• A screen capture of your source sheet where the new entries come in
• A screen capture of your tracking sheet with the current cell links
• A screen capture of the dashboard to show what you'd like the final result to be

Thanks!

Genevieve

Paul Newcome

@Genevieve P I thought about updating cross sheet formulas such as COUNTIFS as well. The reason I went with my solution is the part of the original post that specifies:

"I've tried the full column selection as well as a range selection from within the column, but either way it is not picking up the new data as it is added and I'm forced to manually update the linking multiple times through-out the week."

@Jan Peters If you are displaying metrics such as the count of entries per day, then I would suggest Genevieve's solution.

My solution above is very specific to not having to adjust chart ranges as new rows are added.

Genevieve P.

Oh goodness, @Paul Newcome, so sorry! I had opened this tab earlier and obviously didn't refresh to see you had already answered, haha.

Andrée Starå

@Genevieve P @Paul Newcome

We need some kind of indicator like we have in Smartsheet so we know someone is writing.

🤩

Paul Newcome

@Genevieve P Haha. I do that all the time. Open a handful of tabs, get distracted, then answer without refreshing only to find someone else (usually @Andrée Starå) has already answered.

Andrée Starå

@Paul Newcome You got it wrong! It's the other way around! haha

Jan Peters

@Genevieve P @Paul Newcome

Thank you both so much for your recommendations!

I do have a roll-up summary sheet but I was trying to pull that full column of dates in which really only created an added layer. So, I tried your suggestion for using a cross sheet formula and linked directly to the date column in the originating data sheet.

=COUNTIFS({Rapid Response Delivery Tracking Range 8}, >=DATE(2020, 4, 5), {Rapid Response Delivery Tracking Range 8}, <=DATE(2020, 4, 11))

That seems to be working - fingers crossed!

Now if I could only get it to not return a '0' when it doesn't find any dates within the date range that I'm requesting, that would be a beautiful thing. 😉

Thank you again!

Jan

Paul Newcome

@Jan Peters

=IF(original formula > 0, original formula)

What this says is that if the original formula generates a number greater than zero, generate it. Otherwise leave it blank.

=IF(COUNTIFS({Rapid Response Delivery Tracking Range 8}, >=DATE(2020, 4, 5), {Rapid Response Delivery Tracking Range 8}, <=DATE(2020, 4, 11)) > 0, COUNTIFS({Rapid Response Delivery Tracking Range 8}, >=DATE(2020, 4, 5), {Rapid Response Delivery Tracking Range 8}, <=DATE(2020, 4, 11)))

Jan Peters

THANK YOU @Paul Newcome !! This works perfectly!🤗

Paul Newcome

Happy to help! 👍️