automatically change the color of a chart according to current period

I have a chart where on the x-axis I have months as follows P1/12 P2/12 P3/12 P4/12 P5/12 P6/12.. and on the y-axis I have revenues 1000, 2000 etc.

I have data about the previous months revenues and for rest of the year I fill in manually my forecasts. Now what I want is that the chart draws automatically the bars for my forecasts in different color so that it is easier to see what is actually happened and what are my forecasts. How do I go about it?

Is there a possibility that I write the last month e.g. P6/12 into one cell and the chart would have a formula to recognize this and from there on the color of the bars would change?

Thanks,

Jaakko

Perhaps you can attach a sample workbook, so it would be easier to see what you are trying to achieve.

Pete

Excelforumsample.xlsx

This is a super simple sample of what Im doing.
The data is coded to fill in the chart with the numbers that have occurred, in this case untill P6/12 from another data source. Meaning the formula recognizes, which month is going on and leaves the rest of the year blank for me to fill in manually with my forecasts (yellow in the chart). So in the bar chart I would need my forecast numbers to be in different color as well like I have now colored them in yellow.

(I put the current month in one of the cells in the sample. This is because Im already using one cell with the current month to use in one of the formulas in sourcing the data, untill the last occurred month. Maybe this could also be used in the formula to draw the bars from this month onwards.)

I hope Im being clear enough..

Thanks for all the help!

Unfortunately the data has to be like it is and I can't add another row. Im creating an excel tool to use for years to come and trying to make everything as automatic as possible, so there is no knowing when somebody is using this and when the numbers should be on the second row anyways..

thanks for trying though!
BR,
Jaakko

damn.. okay well actually know considering your option I might be able to add the row and do it like you suggested! thanks!

then just one more thing, if it would be a line graph and not a bar - how do I make the second series start right from where the first series ended? I dont want there to be a line of zeros, which then jumps next to the series one and continues..

excellent!

now the only problem is that I have a formula in the actualized numbers row in each cell, in the empty cells as well, which are trying to search for the value from the original data. Now the cells look empty for example P7/12 but they have the formula in them. The graph sees them as zeros and draws the line to continue as a zero.

It would be easy to just erase the formulas but again Im trying to make everything as automatic as possible since I dont know who is going to use this later on..

YES! sorry did not quite understand that first!

You are a saver! thanks you so much!