HI,
I am trying to create a savings log which would enable us to detail down the savings we generate as a business.
Essentially the main issue I am facing is that I can dedicate fields to detail the saving total along with a date that can be used to identify a realisation month for the saving but i cannot think of a way to apportion the savings down depending on type and across monthly ranges.
This is where things have become a little tricky as there are a few controls that are needed.
1. Savings can be designated as either a one off or annualised in column P. So in essence the total value is either captured in a single month based on the realisation date column E, or the saving total split over 12 values and 12 months from the realisation date. Saving total can be found in column R.
2. Savings can be entered but can only be accounted in the month columns U to AR once approved and the "yes" option selected from the drop down in column T.
I toyed with If statements and sum ifs based on approval status in column T and I can get the value to display or not in a single column. I stumbled across Index and Match functions but not sure if this is the solution or how to get this working.
What I am lost with here is how to get the value to appear in the correct month in the range of Columns U to AR, and then how to get this to either lump the value into one month if designated one off in column P or if annualised is selected to start at the realisation month and then display 12 consecutive values for the period of 12 months starting from the realisation month.
I've attached a dropbox link below to a copy of the workbook with a typed example of what i'm hoping to achieve.
https://www.dropbox.com/s/3bic7p6zya0f763/2019 Price Increase & Savings Log ME.xlsx?dl=0
Any help is appreciated.
Thanks,
I am trying to create a savings log which would enable us to detail down the savings we generate as a business.
Essentially the main issue I am facing is that I can dedicate fields to detail the saving total along with a date that can be used to identify a realisation month for the saving but i cannot think of a way to apportion the savings down depending on type and across monthly ranges.
This is where things have become a little tricky as there are a few controls that are needed.
1. Savings can be designated as either a one off or annualised in column P. So in essence the total value is either captured in a single month based on the realisation date column E, or the saving total split over 12 values and 12 months from the realisation date. Saving total can be found in column R.
2. Savings can be entered but can only be accounted in the month columns U to AR once approved and the "yes" option selected from the drop down in column T.
I toyed with If statements and sum ifs based on approval status in column T and I can get the value to display or not in a single column. I stumbled across Index and Match functions but not sure if this is the solution or how to get this working.
What I am lost with here is how to get the value to appear in the correct month in the range of Columns U to AR, and then how to get this to either lump the value into one month if designated one off in column P or if annualised is selected to start at the realisation month and then display 12 consecutive values for the period of 12 months starting from the realisation month.
I've attached a dropbox link below to a copy of the workbook with a typed example of what i'm hoping to achieve.
https://www.dropbox.com/s/3bic7p6zya0f763/2019 Price Increase & Savings Log ME.xlsx?dl=0
Any help is appreciated.
Thanks,
