All,

I figured I would just post this first before I rabbit hole myself. I've been working on a scenario planning tool for work and I have the following formula figuring out my days on hand inventory over a period of 24 weeks. The range Q3:AN3 represents the time periods. This formula works perfectly in the current setup.

=IFERROR((SUMPRODUCT(--(SUBTOTAL(9,OFFSET(Q3:AN3,,,,COLUMN(Q3:AN3)-COLUMN(Q3)+1))<=P5))+LOOKUP(0,SUBTOTAL(9,OFFSET(Q3,,,,COLUMN(Q3:AN3)-COLUMN(Q3)+1))-P5-Q3:AN3,(Q3:AN3-SUBTOTAL(9,OFFSET(Q3,,,,COLUMN(Q3:AN3)-COLUMN(Q3)+1))+P5)/Q3:AN3))*7,0)

My challenge is I'm building in what if functionality and I am going to be inserting a column before each time period to allow for data validation drop downs with modifiers (90%-110%) to influence different variables that compute DOH. Effectively the Q3:AN3 will double in length but I will need the array to only reference every other column, amongst other things. I just don't want it to count the modifiers in anyway or shape.

I have been writing the entire script in VBA, there is much more going on in this workbook but I am unsure if this is possible or how to go about doing it. I have some info on using index to make it work but I wanted to check with someone before I waste a day trying to make it happen if there is a better way and/or it is not possible.

Thanks!

Chris