Touch Sheet to update formulas with Vlookup

L_123 · 02/08/18

can a Vlookup (or other cross sheet reference) be used to touch a sheet to update today() formulas in unopened sheets?

if we can exploit this cross sheet functionality I might be able to create a master sheet that I can open and refresh all of my sheets daily without having to open each one of them. This would allow me to create much more functional longer time range alerts in many of my sheets.

Stefan 7k · 02/08/18

Hi,

maybe this could work (didn't try it), but you would still have to address every sheet using a today() formula.

Maybe my solution serves you too ;-)

In my current project I have one central sheet with a cell that contains the today() formula.

This cell is linked from every sheet that requires todays date.

I have a Zapier action running daily, that opens the central sheet, writes a 0 to a new row and saves the sheet. This triggers the today() function and all liked cells are updated.

Once in a month I delete the rows with the zeros.

Hope this helps.

Stefan

J. Craig Williams · 02/09/18

Stefan,

You can modify your Zap to update a row on the central sheet instead of adding a new row. This will save you from having to the monthly task.

Craig

Stefan 7k · 02/10/18

Hi Craig,

ah, right, it's in the less common options.

Thanks!

Stefan

Mike_BTS · 08/11/18

I use Zapier for this as well. i have it update a single cell. I found that it sometimes did not save the sheet due to the update to the cell being the same. So I added a second step. I update that cell twice a day, each with a different phrase. This forces a save since the content is new. It also creates and easy check to know that either the "am" or "pm" update went through.

J. Craig Williams · 08/11/18

I avoid the no-update issue by using the timestamp. While I am no where close the allowed successful Zaps per month limit, I don't want any Zaps using twice as many transactions as necessary.

Craig

Touch Sheet to update formulas with Vlookup

Comments

Categories