Conditional formatting or...??

[Eva_]

In the attached file "Production Time Line TEST SHEET" you see an example of what I would like to achieve. The content of Value and the From & To columns are picked up from other worksheets and what I would like is to automate the update of the schematic "production time line" and the split of project value per month - as I do both manually right now.

I'm sure one of you Excel geniuses in here can help me to a solution.

[DonkeyOte]

Can you explain as to why

i) 80k in row 7 is not split across Oct-Dec - rather 20k seemingly going in Jan of following year
ii) 36k in row 12 is not split across Feb-Mar - rather split across Feb-Apr

You could combine the two tables .... ie store the values and use Conditional Formatting to colour those cells containing values... is there a given reason as to why they must be separate ?

[DonkeyOte]

Just as a follow up... it seems from your format table that the value table is incorrect and should be as I expected ... assuming you're happy to combine both tables into 1 and use Conditional Formatting for cells with values...

E6: =IF(AND(E$4>=DATEVALUE("1-"&$C6),E$4<=DATEVALUE("1-"&$D6)),$B6/(1+DATEDIF(DATEVALUE("01-"&$C6),DATEVALUE("01-"&$D6),"m")),"")

copy this across range E6:P12

Without knowing your version giving specific Conditional Format advice is tricky as the 2 versions differ somewhat, however, you can use a Formula based approach in both, so:

Select cell E6 and extend range down to P12, then apply a Conditional Format: FormulaIs:

=E6<>""

[Eva_]

As for i) and ii) - my mistake. I just made quick example of. But then again, that is one of the reasons I would like to automate it. :-)

Actually I only need to total value for production each month (line 13 in the first table). Only reason for splitting up is, that I did not think of the other solution. In other words the second table is just use as intermediate result.

[Eva_]

I use Excel 2003 and will test your formula right away... :-)

[Eva_]

I have now tried copying your formula into the sheet, however I have minor problem. Maybe this is a stupid question, but why does to formula continue being shown as such, instead of a value? Have attached an example...

[DonkeyOte]

It was entered without an = sign

Highlight the cell and set format to General

Now edit the formula and insert the = sign.

Then copy across as outlined.

[Eva_]

Oops... Sorry... But it is still not working... Must admit I'm hopeless with formulas, so I have difficulty seeing through it, to find out what's wrong.

I get the message "The formula you typed contains an error etc... and it highlights the second E$4...??

[DonkeyOte]

Attached.

Be sure to work through it as no one should ever use something at work they don't understand -- especially when it comes to revenue / cost forecasts.

Columns Q:AB are effectively surplus to requirements and can be disposed of.

[Eva_]

Now it works... Thank you. :-)

I only have difficulty creating a (for me) complex formula. Making the revenue / forecasts manually is no problem to me. I will surely work my way through the steps in formula, so that I can re-use it in some other connection...