Allow user to edit formula

[thart21]

Don't know if this is possible but I have a formula to calculate a date and need to allow the user to change it when needed. Was thinking of an input box or even a dedicated cell that they could do this in and have it replace that part of the formula that needs to be changed.

=SUM(SUMIFS('PGI Data'!$G:$G,'PGI Data'!$A:$A,$P1,'PGI Data'!$M:$M,$L1,'PGI Data'!$C:$C,"<="&DATE(YEAR($M1),MONTH($M1)+1,-6),'PGI Data'!$N:$N,C10,'PGI Data'!$B:$B,"Z002"))

I want the user to have the ability to change the "-6" in the "DATE" part of this formula to whatever they need it to be. This calculates the last day of the month in Column M minus 6 days. Sometimes it may need to be -4 or -5 or anyother number (not more than 7)

Any way to do this one? Thanks for any help!

Toni

 

[Ruddles]

Validate the value your user enters, save it in a cell like Z99 or somesuch and then use the cell reference in your formula:-

=SUM(SUMIFS('PGI Data'!$G:$G,'PGI Data'!$A:$A,$P1,'PGI Data'!$M:$M,$L1,'PGI Data'!$C:$C,"<="&DATE(YEAR($M1),MONTH($M1)+1,[COLOR=red][B]Z99[/B][/COLOR]),'PGI Data'!$N:$N,C10,'PGI Data'!$B:$B,"Z002"))

Don't let your user anywhere near your formulae!

 

[Ruddles]

Is it that you always want, like, the last Friday of the month or something? Because you can calculate that fairly easily and incorporate it into a formula without any user input.

 

[thart21]

Thanks for the response Ruddles, I will try that out. The standard that they want is the end of the month -6 but, if there is a holiday or another factor, they need the ability to adjust it.

Thanks again!

Toni