I apologize in advance for this long post. I'll try to be as brief as I can.
I'm working on a budget spreadsheet, which is used to make an interactive chart for my users. I've attached an example of the spreadsheet and chart. Column A through G have the budget information. This data is for the my users to see if they want to see detail. The chart they normally look at is populated by the values in columns N through T, which are all calculations to show percentages.
My chart is a stacked bar chart, filled with the percentages of Expensed, Encumbered, Pre-Encumbered, and Unobligated. As long as Unobligated is zero or above, my chart looks good.
The problem comes in when someone overspends, so their unobligated goes into negative numbers. That drives my chart below the zero line, which I don't want to see.
I THINK I may have a solution, but am unsure how to implement it, or what the repercussions might be. It involves adding another series to my chart, "Expended Exceeds Budget"
I thought in column U I could have a formula that looks at Unobligated, and if Unob is less than zero, the value in U would be 100%. Then, I'd use Conditional Formatting to look for !00% in column U, and so replace the formulas in columns Q-T (for only those lines) to Zero, so my chart would show a solid black bar, indicating my customer has exceeded the budget.
I think this would work, but am unsure on the conditional Formatting part (not familar enough with it). Would the conditional formatting REPLACE the formulas, or would it overlay the formulas with a zero value? if the former, I'd have to recreate the formlas for those lines every time I updated the data (and we're looking at getting a daily feed to update the values). So, I'd need to have a macro to replace those formulas every time data is updated.
I'd appreciate any help I can get on this, and if you have a better idea on how to accomplish this I'm eager to listen.
Thanks,
John
Bookmarks