When cell is updated to new value it updates past and present cell values in target sheet

Cathy Betzer

We have a sheet similar to a price sheet that populates a target sheet via index/match formula. Stakeholders submit orders via a SmartSheet form. However, I don't know how to update orders in the future not in the past. Past order should still be the same value / cost. Thank you in advance for your help.

Cathy

Paul Newcome

You will need a column in the price sheet that contains the "effective" date. From there, I can help you with the appropriate formula if you are able to provide a couple of screenshots for context.

Cathy Betzer

Thank you for your offer of help. My sheet is actually re: pay rates for various extra work shifts. I added an effective date column as you suggested.

The target sheet brings in the extra shift description field - so that is working just fine. Task is to only change amounts for extra shifts from effective date forward.

I would like to use datamesh but not sure that will work due to "effective date" issues.

Paul Newcome

The effective date column needs to go into the sheet that houses the base rates that you are looking up. It goes in your reference table.

Each time you change the rates, instead of changing the rates in the existing row, you would create a new row and enter the new effective date.

From there you would use something along the lines of

=SUMIFS({Reference Table Rate Column}, {Reference Table Date Colum}, @cell = MAX(COLLECT({Reference Table Date Column}, {Reference Table Date Colum}, @cell<= [Shift Date]@row)))

Cathy Betzer

1. effective date is in my source sheet that houses my base rates.
2. I can't enter a new row as you suggest. The sheet is my employee listing and their various compensation info. I really don't want to add two rows for each employee.
3. That formula may be beyond my abilities. I'll have to chew on that for awhile.

Thank you for your suggestions.

Paul Newcome

You don't have to add new rows to each employee. Only new rows in the reference/rate sheet.

In putting together an example visual for you, I realized you will also need a helper date column that can be hidden after setting up) on the target sheet.

In the snippet below, the target sheet is on the left and the reference sheet is on the right. Of course column names will be different and you will be using {Cross Sheet References} instead of [Column Name]:[Column Name], but the syntax of each formula needed is below.

Helper Column Formula:

=MAX(COLLECT([Effective Date]:[Effective Date], [Effective Date]:[Effective Date], @cell <= [Date of Extra Shift]@row))

Rate Column Formula:

=SUMIFS(Rate:Rate, [Effective Date]:[Effective Date], @cell = [Helper Column]@row)