I am creating a formula to try yo find the total amount in pounds that is in in-transit. The current lead time is 5 days. But I have placed a manual override where we could change the lead time to 10 days for example. How do I get it to change outcome to be similar to the 2nd picture below.
the equation in the first white cell is the following =SUM(OFFSET(B1,0,0,5)) where 5 is the default lead time, but I would like to have that change depending on whether there is a value in the lead-times override (Final Column)
This equation is dragged down to the remaining white cells in this table so that the last white cell is =SUM(OFFSET(B12,0,0,5))
This is what I want it to look like
Sorry if I am unclear in my question since, it is a bit hard to explain in writing.
CodePudding user response：
Just check if the value exists and use it. If not, fall back to 5.
=SUM(OFFSET(B1,0,0,IF(NOT(ISBLANK($C$9)), $C$9, 5)))
Don't forget to make the override cell static (with the dollar signs) or they will shift when dragging your formula.
CodePudding user response：
The problem with this solution is that the override will not always be in cell C9. The entire C column is where I input the override. The row where the override is, could change. Below are some examples of what I want to happen.
