looking for the right Conditional formatting formula/function.

[BigPapaMurf]

Note: might be easier if you skip the long story and just look at the sample doc.

SHORT STORY:

Whether the existing conditional formatting applies or not on table3 / columnC, I want conditional formatting for table3 / columnC to show red if priority on table4 = 1

---------------
LONG STORY:
I've got 2 spreadsheet docs.

One spreadsheet (spreadsheet1) pulls data from one table (call it table1)and generates a different table (table2) based on a preference I select via dropdown (not important). Table2 does not include items from table1 when there is a $0 balance. Table2 generates a "priority list" where the top row would be priority #1, second row, #2, etc, based on certain factors.

The other document (spreadsheet2) and table (table3) has the same info as spreadsheet1/table1, as well as a check box and some conditional formatting.

The current formatting works, but I want to add one more rule to it. To try to get this to work, I created another column and the priority #'s align with their corresponding text. I used this formula...

=if($E2>0,VLOOKUP($D2,$D$13:$E$24,2,0),"")

It may be easier for you to just look at the sample spreadsheet I created to understand what I'm trying to do. Note. I simplified the formula and put it all on the same sheet.

What I want is for the text in columnC to be red, if the matching priority = 1. This should have the highest priority.

I can't seem to figure out, if i should use any one of the following statements.... if, or, and, xor, , not, match, index. Please offer a suggestion.

-----------------
Referring to the sample: should I add a column to spreadsheet2 / table4 to display the corresponding cell from spreadsheet1 / table2 / columnA ?

I can't wrap my head around it.

Thanks
Murphy

[BigPapaMurf]

If I can delete spreadsheet2 / table4 / priority column all together, that may help. But I think I tried that and the formula said I couldn't reference the other spreadsheet due to Excel limitations or something. I don't recall specifically.

[BigPapaMurf]

I thought I tried this simple formula yesterday but it may have just slipped my mine. Got it working by just using a =m2=1 formula applying to G2:G11.