Here is one I did earlier (a few years ago in fact).
=IFERROR(IF(EOMONTH($B20,0)=T$3,"L","")&IF(COLUMN()-VALUE(LEFT($C20,FI
ND("
",$C20)))
T($C20,FIND("
",$C20)))))<>"",IF(RIGHT(INDIRECT(ADDRESS(ROW(),COLUMN()-VALUE(LEFT($C
20,FIND("
",$C20))))))="R","","P"),""))&IF($F20="Yes",IF(T$3=EOMONTH($G20,$H20),
"R",""),IF(T$3=EOMONTH($E20,$H20),"R","")),"")
The workbook was inherited but I am responsible for the mess of the formula.
It dynamically forecasts activity in a month based on data elsewhere in the workbook.
It is also a darn sight quicker than the equivalent VBA code with the workbook recalculating in about 5 minutes and the most efficient VBA version taking over 30 minutes. Important to consider when doing "what if" changes.
Can I remember how it works? Erm No!
It works 99% of the time but there are a few exceptions where it doesn't and weeding these out takes too much time so I need to fix it.
I wish formulas had comment structures like programming languages do!
Posted for cathartic purposes only, but similar stories welcome.
|
