cell reference on dashboard

Ken Hallaran

I have a dashboard that references a cell in a sheet. That cell contains a formula that displays a value based on date triggers (future, open, past due). My problem is the cell value will only update if I open the sheet and then click save. Is there a way to do this so I don't have to open the sheet everyday? Its a background sheet that is setup once when a new contract is signed.

Andrée Starå

@Ken Hallaran

If you create a sheet that Zapier connects to and updates a date cell as often as needed and then connect it to the other sheets with cell-linking or a VLOOKUP - INDEX/MATCH formula, it will update the client sheets when Zapier updates the date.

Make sense?

Andrée Starå

Hi Ken,

There are a couple of workarounds.

The one I usually use in my client solutions is Zapier.

It's is an excellent option for this scenario.

Is that an option for you?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Ken Hallaran

We use zapier. Would I have to create a zap for every sheet (each new client has its own copy of the sheet)?

Andrée Starå

@Ken Hallaran

No, you would only need to link the "main" sheet to the others so they get updated together.

Make sense?

Ken Hallaran

Not sure what you mean by main sheet. Here is my setup. I have template set built that gets duplicated for each new client and then put in their own work space. How would I set up the zap? Seems like I would have to connect the zap to each sheet?

Ken Hallaran

Probably best to spell it out, Im still an amateur at this stuff.

Andrée Starå

@Ken Hallaran

If you create a sheet that Zapier connects to and updates a date cell as often as needed and then connect it to the other sheets with cell-linking or a VLOOKUP - INDEX/MATCH formula, it will update the client sheets when Zapier updates the date.

Make sense?

Ken Hallaran

Yes! My brain is still half a sleep. Thanks!

Andrée Starå

@Ken Hallaran

Excellent! (not the brain part) 😉

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!