I am using the formula below to either pull in the result only from another cell, or i pull in the date and the result see below for what the pivot table shows:
=IF(OR(M5="(blank)",M5=""),"",IF(LEFT($AY5,3)<>$AY$1,TRIM(MID(SUBSTITUTE(M5,CHAR(10),REPT(" ",LEN(M5))),(1)*LEN(M5)+1,LEN(M5))),LEFT(M5,(LEN(M5)-6))))
If for instance the month is Dec, and the result is Dec, i just want the result (100%). If the month is Jan and the column is for Dec, then i want the first two rows Because the month mismatches the column header. I am good with what i have and it works.
But, i have another column that equals Monthly, quarterly or annually. all annual results are Dec, and all quarterly are (Mar, Jun, Sep, Dec) and i would like the formula to understand, for annual metrics. Basically i need a formula for the 3 possible options.
So if the final matches the month, it just shows result. but if it is annual, i want sep to have the dec 21 value, and aug to have the dec 2020 value
Please and thank you, i am getting stuck here.
Dec-01-22 100% Green |
=IF(OR(M5="(blank)",M5=""),"",IF(LEFT($AY5,3)<>$AY$1,TRIM(MID(SUBSTITUTE(M5,CHAR(10),REPT(" ",LEN(M5))),(1)*LEN(M5)+1,LEN(M5))),LEFT(M5,(LEN(M5)-6))))
If for instance the month is Dec, and the result is Dec, i just want the result (100%). If the month is Jan and the column is for Dec, then i want the first two rows Because the month mismatches the column header. I am good with what i have and it works.
But, i have another column that equals Monthly, quarterly or annually. all annual results are Dec, and all quarterly are (Mar, Jun, Sep, Dec) and i would like the formula to understand, for annual metrics. Basically i need a formula for the 3 possible options.
So if the final matches the month, it just shows result. but if it is annual, i want sep to have the dec 21 value, and aug to have the dec 2020 value
APR | MAY | JUN | JUL | AUG | SEP |
Prior-3 | Prior-2 | Prior-1 | prior | previous | current |
99% | 99% |
Please and thank you, i am getting stuck here.