Every month, I have to review if the financial analyst have balance the projects budget for their accounts. I have to review over 25 accounts with multiple projects. I already have a formula looks at "actuals" that hit (let's say January) and compares it to the forecast for the same month. The results are as follows:
Compare and Return
1. If the budgets match, return a Yes
2. If the budgets don't match, return a No (this means they have not yet reconciled this project yet)
3. If the budgets are blank, return an N/A (this means that we didn't forecast anything for this month)
What I want is a formula in the SI Fin Details tab to "scan" the Jan column in the TechDev tab and return a Yes or No based on (the same will be for all the months):
1. If the above return equals "Yes" or "N/A" for all the projects that match the SI (let's say in this case 34108) in column K, than return a Yes
2. If the return is a combination of "Yes" and "No" for all the projects that match the SI (let's say in this case 34108) in column K, than return a No (as not all projects have been reconciled). This prompts me to follow-up with the analyst
3. If the SI column K has an "#N/A", ignore as the project is not yet a project with a budget or forecast. Do nothing
Please see attached example file... thx
Bookmarks