If I understand you correctly, the resulting formulas are going to be hugely complicated, especially as many of the formulas to be merged refer to equivalent values on the next line. Whoever in future has to update (or even understand!) the formulas will have a very difficult job.

Instead, what about a macro which is run whenever a new day's data is added? It would process each sheet's columns A:F by temporarily creating the current formula columns, convert the formulas to values and then delete the unwanted columns. You'd end up with exactly what you want (except that the "Blue" columns would be values rather than formulas) and it would be much easier to maintain.

Regards,

Brian.