Dynamic Conditional Formatting

[joec]

I have a sheet of Info and I use two conditions that colour rows of
cells across six colms. When I enter info on a new row only one cell is
coloured if either of the conditions is true. When I sort the page the
new row has cells coloured correctly but now the last row has only one
cell coloured.
I would like my range of data to include the CF conditions every time a
new row is added.
Any help please?
Regards
Joe

 

[Ken Johnson]

Hi Joe,
I'm very behind the times when it comes to Macs ie Old iMac OS 9.2
Office 2001 so this might not apply.
However, Insert>List enables you to convert your data to a List,
and Edit>Preferences>Edit>(tick) Extend List Formats and Formulas, I
think would do what you are wanting.

Ken Johnson

 

[joec]

Ken
Thanks for your help. I tried using your suggestion of a List but still
can't get it to extend the two conditions to a new entry- just one cell
colours if either of the conditions are true instead of all the cells
in the row.
I seem to remember using OFFSET in a formula that kept the List dynamic
but need more info on this.
Best Regards
Joe

 

[Bob Greenblatt]

Ken
Thanks for your help. I tried using your suggestion of a List but still
can't get it to extend the two conditions to a new entry- just one cell
colours if either of the conditions are true instead of all the cells
in the row.
I seem to remember using OFFSET in a formula that kept the List dynamic
but need more info on this.
Best Regards
Joe

Joe,

Something is fishy. I just tried crating a list with conditional formatting
that colors a row in the list conditionally based on a cell value. The
formatting extends properly when the list is extended. Try using the format
painter to insure that the conditional formatting really applies to all the
cells in the table.

 

[Ken Johnson]

Hi Joe,

For a dynamic named range (DNR) the OFFSET formula is used in the
"Refers to:" box of the Define Name dialog.
For example, say you want a DNR in Sheet 1 column A called DNR_1 (crap
name I know, it can be something more descriptive as long as it doesn't
resemble a standard address or start with a number or contain certain
special characters)...

1. Select A1 then Go Insert>Name>Define...
2. Type DNR_1 in the "Names in workbook:" box at the top of the Define
Name dialog
3. Click in the Refers to: box and change the formula to...

=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1)

Avoid using the arrow keys to navigate your way around the "Refers to:"
box, they move the cell selection on the sheet and can change what you
have already typed in the box. Use the mouse pointer only to get to
different places in the equation when editing the "Refers to:"
equation.

4. Click the Add button then OK

5. Test it out by typing a heading in A1, then something in A2 and A3
to represent DNR_1's first two entries. Then click inthe Name box on
the right side of the Formula Bar and type DNR_1 then press Enter. If
you then see that range A2:A3 is selected you know that you did
everything correctly.

Maybe you could set up 6 DNR's, one for each of your columns. Your Cond
Formatting should grow as the DNR's grow if it is set up using the DNR
names.

Try it and see.

I'd try it out myself if it wasn't so late (12:23 in the morning).

Ken Johnson

 

[joec]

Ken

Thank you so much for taking the trouble to reply. The CF conditions do
extend when new data is added. However, just for the record, how would
I add to the Offset formula to include the fact that I have 6 Cols
(A:F) which might be added to at a later date? I'm still keen to Name
my data dynamically.
Best Regards
Joe

 

[Ken Johnson]

Hi Joe,
I got your email and I've been doing a bit of research on the problem.
I'm still working on it and will send it soon.
I've found out some pretty interesting new things myself and hope you
find them equally interesting.

Ken Johnson