Is it possible to remove a cross sheet reference when a condition is met and keep the data?

Gary Fairman

I have an interesting scenario where I have a cross sheet reference that pulls a current outstanding total from another sheet. This works perfectly but we want to keep history of what this total outstanding value was at the end of each day.

The image below should help explain the scenario. Essentially what I want to do is to keep the literal value of the cell link when the date is in the past.

This is the formula I have so far but I dont know how to keep the value on the dates in the past.

=IF(Date@row >= TODAY(); {Cell Link Total Outstanding Value}; Keep Literal value at the end of the day)

Any bright ideas would be welcome.

Thanks

Gary

Paul Newcome

You will need to set up a second sheet. You would then use a copy row automation to copy "today's" row at the end of the day over to this other sheet. You would then use a formula with cross sheet references to pull this data back from the second sheet if the date is in the past.

=IF([Date Column Name]@row = TODAY(); {Cell Link Total Outstanding Value}; INDEX({Second Sheet Data Column}, MATCH([Date Column Name]@row, {Second Sheet Date Column}, 0)))

Adam Murphy

I don't think it is possible to remove the formula automatically. To retain historical values from calculated fields I use a helper sheet to which I copy the row in question, then I add helper column to my sheet to pull back the legacy value from the helper sheet. Hope that helps!

Paul Newcome

You will need to set up a second sheet. You would then use a copy row automation to copy "today's" row at the end of the day over to this other sheet. You would then use a formula with cross sheet references to pull this data back from the second sheet if the date is in the past.

=IF([Date Column Name]@row = TODAY(); {Cell Link Total Outstanding Value}; INDEX({Second Sheet Data Column}, MATCH([Date Column Name]@row, {Second Sheet Date Column}, 0)))

Gary Fairman

Nice one Paul and Adam . That works. You just saved us from having to hire another person to manually remove the few 100 links we have like this (and climbing) on a daily basis.

FYI…I got about 90% of the way to a potential solution using Bridge to achieve the same result. Was going to overwrite the formula with the value at a specific time every day. Had some fun matching the dates that way though and there is so little documentation on Bridge out there to assist.

One subtle correction to your formula @Paul Newcome, the commas in your INDEX(MATCH) should be semi-colons.

Thanks again.

Gary Fairman

Added some error handling on the formula too so that instead of displaying an error when there are no matches it just puts 0 in the column.

=IFERROR(IF([Date Column Name]@row >= TODAY(); {Cell Link Total Outstanding Value}; INDEX({Second Sheet Data Column}; MATCH([Date Column Name]@row; {Second Sheet Date Column}; 0))); 0)