Copy conditional formats, using named ranges

lillian

Board Regular
Joined
Oct 12, 2005
Messages
72
after digging around the web, I came across a method from the J-walk.com eee newsletter that looks promising - but I can't get it to work:

----------------------------------
http://j-walk.com/ss/excel/eee/eee003.txt (halfway down the page)
Created by David Hager
----------------------------------
To make a conditional format based on the value in the previous
worksheet, create the following defined name formulas.

GlobRef as:

=INDIRECT("rc",FALSE)

which gives the value from the cell it is used in.

PrevShtValue as:

=INDIRECT(INDEX(GET.WORKBOOK(1),GET.DOCUMENT(87)-1)&"!"&ADDRESS(ROW(),
COLUMN()))

which gives the value from the cell of the same address in the previous
sheet.

Then, combine these in yet another defined name formula.

GTPSV (this cell value is greater than previous sheet value) as:

=GlobRef>PrevShtValue

which is used as the conditional formatting formula (in Excel 97 and later
versions).

----------------------------------

Has anyone used this or can anyone speak to the details of how to use this?

Cheers!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
ranges are created

I created the ranges as described, without changing anything. I don't quite understand how they call the conditional formats.

On the page where I want the values from the previous sheet to apply, I selected the range & apply the GTPSV name & nothing happened.

:confused:

Wait - edit here - the conditional format that I had applied earlier to "hide" #N/A errors went away & all values from the formula in the cells appear in the default format.
 
Upvote 0
That example assumes that you want to Conditionally Format those cells that have a value greater than the value in the same cell on the previous sheet. To use it select the cells, choose Format|Conditional Formatting, Condition 1 Formula is:

=GTPSV

click Format, choose one and click OK twice.

If you want some other condition, add another named formula which refers to the relevant condition and use that name in your CF formula.
 
Upvote 0
in terms of application

so, it's not really copying the condition, it's creating a new condition that refers to formatting on another worksheet?

Thanks for the quick replies!
 
Upvote 0
Re: in terms of application

lillian said:
so, it's not really copying the condition, it's creating a new condition that refers to formatting on another worksheet?

Thanks for the quick replies!

What is it you want to do exactly?
 
Upvote 0
I'm using a formula to pull in a value, checking on two different cells and matching them. The value on the other sheet has conditional formatting, with about 10 different conditions.

I'd like the value returned to include formatting.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top