Is there a more efficient way to draw these bars?

[Mattman55]

I'm using this formula to draw a bar in a cell if that cell falls in a certain date range. The effect is a Gantt chart (see image).

=IF(AND(EW$5>=$H576,EW$5<=$I576),"███","")

But now that I've copied the formula several years to the right and two thousand rows down, my spreadsheet is over 10Mb and using all my memory to run.

Is there a more efficient way to do what I'm trying to do? The built in chart functions don't really cut it.

I know there are some creative people out there! Thanks in advance for your ideas!!

Matthew

 

[Fluff]

Why not use conditional formatting?

 

[Mattman55]

Why not use conditional formatting?

Sort of the same reason. Per Google, While conditional formatting makes it easy to flag cells that go outside a range of values, the formatting is super-volatile. Every time your worksheet recalculates, the conditional formatting rules are reevaluated. When this involves a lot of cells, the worksheet can become very slow and unresponsive.

 

[Fluff]

Have you actually tried it on the range your interested in?

 

[Mattman55]

Have you actually tried it on the range your interested in?

I'm having trouble with the formula. Shouldn't the circled cell turn blue in this instance?

 

[Fluff]

You need to remove the quotes & the row/column needs to match the first row in the applies to range, although I suspect the formula you need is more like

=AND(AQ$5>=$H1809,AQ$5<=$I1809)

for the applies to range of AQ1809:AW1811

 

[Mattman55]

You need to remove the quotes & the row/column needs to match the first row in the applies to range, although I suspect the formula you need is more like

=AND(AQ$5>=$H1809,AQ$5<=$I1809)

for the applies to range of AQ1809:AW1811

You, my friend, are a genius! My file is now only 2Mb!! Thank you, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.