How to 'mirror' a conditional format

[NickS]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi,
Is it possible to mirror a conditional format in another cell. I appreciate I can copy/paste but depending on the conditional format of one cell I wish to update a second (third, fourth,etc) cell to replicate the format. If the condition is not fulfilled I want to leave my second (third, fourth, etc) cell untouched. To put the same conditions into all the cells feeding off the first one would be very painful!
Thank you.
Nick

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi,
Is it possible to mirror a conditional format in another cell. I appreciate I
can copy/paste but depending on the conditional format of one cell I wish to
update a second (third, fourth,etc) cell to replicate the format. If the
condition is not fulfilled I want to leave my second (third, fourth, etc) cell
untouched. To put the same conditions into all the cells feeding off the first
one would be very painful!
Thank you.
Nick

I'm not exactly sure what you want to do - either make the conditional
formatting in all cells the same using a formula with relative references,
or make the other cells conditional upon the value of some other cell. I
don't see how this is painful, or maybe I just don't understand what you
want. However, in either case, just highlight the cells involved. Select
conditional formatting, and enter the conditional formula using the
appropriate cell references. Press enter, and all the selected cells will
have the same conditional formatting.

 

[CyberTaz]

I think what you may be overlooking is the fact that you can select multiple
non-contiguous cells at once in order to set the formatting for them all at
the same time. Select one cell or contiguous range of cells then hold the
Command key while you click additional individual cells or while you drag to
select additional ranges. Then set the formating.

 

[NickS]

Sorry I wasn't very clear. I'm comfortable selecting multiple cells and applying conditional format to them. I am also comfortable having the condition refer to a fixed reference cell.

My problem is I want the format to be conditional on the condition of a cell. Does that make sense?! There is nothing in the content of the cell that will indicate if the condition is fulfilled or not other than the fact the cell is, lets say, coloured red and bold. I then have another workbook referring to the content of the cell - so I naturally pick up the contents, but what I don't see is whether it is red and bold (and I want to replicate that formatting in the second workbook). The actual content of the cell passes through a number of iterations so to repeat the conditional formatting is next to impossible. Perhaps there is no alternative than to build in something extra in the content that flags the condition (which I was hoping to avoid).

To give some context I am building a holiday planner for a couple of dozen employees. The employees have an annual holiday allowance. They also are entitled to one day of extra holiday per month (use it or lose it). I track the holiday in a summary worksheet. I want to be able to flag up if the day showing in the summary is one of these 'special' extra days by colour coding it differently. The summary is driven by a bunch of individual sheets for each employee (they fill out a form for each period of absence) which roles up into a summary for the employee in their individual workbook. This all gets matched against a Calendar. So there are a lot of lookups etc going on. That's why I want to avoid the conditional formatting carrying through to the summary workbook...

 

[Bob Greenblatt]

Sorry I wasn't very clear. I'm comfortable selecting multiple cells and
applying conditional format to them. I am also comfortable having the
condition refer to a fixed reference cell.

My problem is I want the format to be conditional on the condition of a cell.
Does that make sense?! There is nothing in the content of the cell that will
indicate if the condition is fulfilled or not other than the fact the cell is,
lets say, coloured red and bold. I then have another workbook referring to the
content of the cell - so I naturally pick up the contents, but what I don't
see is whether it is red and bold (and I want to replicate that formatting in
the second workbook). The actual content of the cell passes through a number
of iterations so to repeat the conditional formatting is next to impossible.
Perhaps there is no alternative than to build in something extra in the
content that flags the condition (which I was hoping to avoid).

To give some context I am building a holiday planner for a couple of dozen
employees. The employees have an annual holiday allowance. They also are
entitled to one day of extra holiday per month (use it or lose it). I track
the holiday in a summary worksheet. I want to be able to flag up if the day
showing in the summary is one of these 'special' extra days by colour coding
it differently. The summary is driven by a bunch of individual sheets for each
employee (they fill out a form for each period of absence) which roles up into
a summary for the employee in their individual workbook. This all gets matched
against a Calendar. So there are a lot of lookups etc going on. That's why I
want to avoid the conditional formatting carrying through to the summary
workbook...

So you want the conditional formatting to be based on the format not the
contents of another cell? If so, you can't do this directly. Your best bet
is to use a "helper cell" that is not visible, or out of the print range,
that changes content, and then make the condition based on this cell.

 

[NickS]

Thanks Bob - I'll give that a shot (sadly my spreadsheet performance is now grinding to a halt - Excel ultimately does have it's limits!)