Index and Match Formula

Chrisjohn France Juguan

Hello everyone!

Just having some observation in the index and match formula that I used (data captured is from another file). Seems like the value of the formula only updates when I open the said file, then I'd need to save the file as soon as the values of the formula are updated. I noticed it because this column with the index match formula produces data w/c i use as basis in sorting a particular category to generate a list via a Report File. The report file is then published to a dashboard. Every week when the updates are expected to create a change in the list, the list in the dashboard does not change not until I open the grid file with the index match formula so that the data captured by the formula updates. Is it better to use the Vlookup formula instead?

Paul Newcome

The TODAY function does not update to today's date until the sheet has been activated. To activate a sheet, you would need to open it (which is what you are currently doing), have a form submission, submit an update request, or have an automation run.

My recommendation would be to insert a hidden date type column and have a Record A Date automation run each morning at 12:00am to insert the date. This will update all instances of TODAY on that sheet.

Paul Newcome

Is the TODAY function incorporated anywhere in the source data through other formulas?

Chrisjohn France Juguan

Hello Paul!,

Yes, my index formula actually contains cell reference in column [Current Week] which has a TODAY Function. Here's my formula in the CURRENT WEEK column

Paul Newcome

The TODAY function does not update to today's date until the sheet has been activated. To activate a sheet, you would need to open it (which is what you are currently doing), have a form submission, submit an update request, or have an automation run.

My recommendation would be to insert a hidden date type column and have a Record A Date automation run each morning at 12:00am to insert the date. This will update all instances of TODAY on that sheet.

Chrisjohn France Juguan

Thank you so much Paul! will try this one :)

Paul Newcome

Happy to help. 👍️

CK17

@Paul Newcome,

Is there a way to have the hidden date column just update / refresh the first row cell and not the entire column? My thoughts are that I also use a "Date Completed" based on a checkbox of "Complete". It updates the Date Complete anytime there are changes in the row after the checkbox is selected. If the Date automation triggers that, then the date complete will always update to the hidden column / current date. Does that make sense?

Paul Newcome

@CK17 You would need to find something that is only ever going to be on row 1 and then build that in as a condition in the Automation.