I am trying to get the cells in a specific range to populate with a value from a different cell, based on a starting FY24 Q1 and another cell that says how many quarters the value goes across. So that if the FY changes or the Quarter changes the cells automatically update with the correct values.
The closest I have got is:=IF(AM$5=$K30,$V30/$W30/1000000,IF(K30="x",$Y30," ")) and then copying across.
I am hoping this makes some sense as its difficult to explain.
I want column AM to populate with the value in Y based on the start date/quarter in Column K and then populate the number of quarters stated in column W. So if it starts in Q1 and if there is 2 Quarters then it populates 2 cells based on column W having 2. If it had 3 then it would populate the next 3 etc.
However, column K has different starting Quarters, therefore I would like the formula to start in the corresponding Quarter on the right and then populate the number of Quarters from Column W automatically. If a Quarter is changed in K due to target being missed, I would then like it to automatically update.
Is this even possible?
If you need anything further please ask, I hope this makes some sort of sense!