Apply Results of Conditional Formatting to Other Cells

[Ludd1te]

I’d like to apply the results of conditional formatting to other cells, but not the act of conditional formatting.

I.e.:

1. I have a table of correlations; below that I have a table of the p-values for each of those correlations.
2. I apply conditional formatting to the lower table of p-values, making all cells with p-values less than, say, 0.01 bold.
3. I would now like to apply the pattern of which cells are bold and which are not to the table above that of correlations.

If I simply apply the conditional formatting per se to the upper table of correlations, I would bold all the very low (and negative) correlations, not the sufficiently large ones. Instead, I want to use the lower table to note which correlations in the upper table are sufficiently large.

As a simple example:

Upper Correlation Table

.9     .1

.1     .2

Lower Table of p-Values

*.001* .3

.3     .2

I.e., the p-value for the top-left correlation is .001, which is less than my criterion and so is made bold. I would like to copy this pattern of the top-left cell being bold and all others being not bold to the “Upper Correlation Table”.

I tried copying just the formatting (Copy Special>Formatting), but this copies the conditional formatting, not just the pattern of which are bold and not.

Any ideas?

Thank you all in advance.

[mikekaganski]

Just use formula in conditional formattings in Upper Correlation Table (UCT). E.g., if UCT is A1:B2, and Lower Table of p-Values is A4:B5, then first select A1:B2, go to Format->Conditional Formatting->Condition..., and define condition as Formula is - A4<0.01. I.e., you define condition for first (top-left) cell in range. As you don’t use absolute addressing ($), you will get this formula changed appropriately (like “if value of cell three rows below in the same column is less than 0.01”) when applied to other cells in range.