Dashboard & Chart Widgets

Martha Hemingway

Is it possible to have a Chart Widget that is currently populated, maintain the same attributes (specifically the chart type and even series/colors) whenever the data source is updated?

Example - I have a sheet that lists in one column different Business Purposes. In the next column, is a formula that counts how many times the Business Purpos@row is used in a specific month on a different sheet. My intent is to show on the dashboard Chart widget, one month content in a Pie chart. Whenever I go to update the next month's content, the widget seems to default back to a Line chart type. I am not wanting to have to touch every dashboard widget attribute such as the chart type every single month.

Is there a better way to organize the data to default the chart into a Pie chart?

Paul Newcome

That's because your November column is blank. You mentioned you would want it to flip on the first of each month, so the INDEX formula is pulling from the current month's column.

Paul Newcome

The TODAY() function does not update on its own unless the sheet is somehow activated. The easiest way to have the sheet automatically activated and saved to update the TODAY() function without you having to maintain anything would be to insert a date type column and then set up a Record a Date automation triggered daily and set to run at 12:00am with a condition of where the new date type column is blank or is not blank.

This will force update the TODAY() function to ensure your INDEX functions properly without you having to ever touch this sheet again.

Paul Newcome

Instead of referencing the newly added column in your chart, use a helper column to pull in the most recently updated month column and reference this one column in your chart. That way your data source isn't changing, only the data contained within.

Current Setup:

BP ..... Jan ..... Feb ..... Mar

New Setup:

BP ..... Display ..... Jan ..... Feb ..... Mar

Martha Hemingway

@Paul Newcome - brilliant!!!

So here's what I have:

Context:

DSM Field Movements Ranges are all referencing a sheet where the DSM enters their DSM #, the store # they are visiting (with the date) and the purpose for their visit.

Would the Display Column be a formula of some sorts? Could it reference whatever is in the column next to it? That way when December is over with and it's time to display the January calculations, I can simply just move the December column out of the way?

Paul Newcome

It wouldn't be set up where you can just move a column out of the way, but we can set it up with a formula so long as we know the logic for when to switch to the next month.

Would you want it to flip on the first of each month, or would you want it to wait to flip to the next month once the number changes from zero? Or do you have some other kind of logic for when it should flip?

Martha Hemingway

I would love for it to flip on the first of each month!!! @Paul Newcome - I am legit so happy that this could work!!!

My formula for each column (Month) is doing a count based on the date

Paul Newcome

If you leave the month columns in chronological order (Jan - Dec from left to right), the formula to pull in the current month's numbers would be

=INDEX(Jan@row:Dec@row, 1, MONTH(TODAY()))

You may want to consider looking into one of the methods to automatically update the TODAY function just to be sure. My personal choice is a Record A Date automation set to run daily at 12:00am.

Martha Hemingway

Well I might have something wrong..... I tried the formula, however, it's displaying blank.... I would expect (or I want) the Display column to put a 1 in the cell if I have a 1 in the December column..... Would you be open to connecting live to help me?

Paul Newcome

That's because your November column is blank. You mentioned you would want it to flip on the first of each month, so the INDEX formula is pulling from the current month's column.

Martha Hemingway

YES!!!! It worked!!!

Can you elaborate a little more on what you meant here: "You may want to consider looking into one of the methods to automatically update the TODAY function just to be sure. My personal choice is a Record A Date automation set to run daily at 12:00am."

Paul Newcome

The TODAY() function does not update on its own unless the sheet is somehow activated. The easiest way to have the sheet automatically activated and saved to update the TODAY() function without you having to maintain anything would be to insert a date type column and then set up a Record a Date automation triggered daily and set to run at 12:00am with a condition of where the new date type column is blank or is not blank.

This will force update the TODAY() function to ensure your INDEX functions properly without you having to ever touch this sheet again.

Martha Hemingway

@Paul Newcome - brilliant! Thank you so much for explaining and walking me through this! Life (and Time)! Saver!

Paul Newcome

Happy to help. 👍️

Martha Hemingway

@Paul Newcome - So i must have done something incorrectly because my column isn't populating. Here are screen shots for your recommendation of "insert a date type column and then set up a Record a Date automation triggered daily and set to run at 12:00am with a condition of where the new date type column is blank or is not blank."

Insert a date type column

Record a Date automation

The automation has been running:

Why is my AutoUpdate column still empty/blank?

Paul Newcome

Click on the "and" in the condition to switch it to an "or".

Martha Hemingway

🤦‍♀️ I can't believe I missed that. I was even reading your directions outloud when creating the automation. I need to go refill my coffee cup.

Thanks again!!!

Paul Newcome

Happy to help. 👍️