cell reference changes and conditional formatting

I have a sales goal tracking spreadsheet that represents one period (month)
at a time. I have Sun - Sat across the top, then the categories (retail,
Corp, mail order, etc on the side.
For each day of the week, I have the Last Year's ACTUAL sales, the GOAL
number (usually 150% of LY), then THIS YEAR'S ACTUAL sales number. Obviously,
this is repeated three more times (going down the spreadsheet for each week).
I have used the conditional formatting to tell the TY Actual number to be
red or green based on if we exceed LY actual or not for each category. No
problem there. The problem is when I tried to use Format Painter so that I
didn't have to do every TY Actual cell, it keeps the reference cell instead
of understanding it needs the corresponding LY Actual cell that is two
columns over from it.

Is there a quick way to format the TY Actual cells?

Thanks in advance for you help!

Maggie

You should be able to create the CF once then use the format painter. It's
hard to be specific without knowing more details of your spreadsheet's
layout, but the key will be setting up the CF with a formula using a relative
(or mixed) reference (eg A4 or A$4 rather than $A$4).
If you'd like more specifics, please post with the condition you've got
specified currently, and some details of the layout.
--Bruce

"Maggie Boby" wrote:

> I have a sales goal tracking spreadsheet that represents one period (month)
> at a time. I have Sun - Sat across the top, then the categories (retail,
> Corp, mail order, etc on the side.
> For each day of the week, I have the Last Year's ACTUAL sales, the GOAL
> number (usually 150% of LY), then THIS YEAR'S ACTUAL sales number. Obviously,
> this is repeated three more times (going down the spreadsheet for each week).
> I have used the conditional formatting to tell the TY Actual number to be
> red or green based on if we exceed LY actual or not for each category. No
> problem there. The problem is when I tried to use Format Painter so that I
> didn't have to do every TY Actual cell, it keeps the reference cell instead
> of understanding it needs the corresponding LY Actual cell that is two
> columns over from it.
>
> Is there a quick way to format the TY Actual cells?
>
> Thanks in advance for you help!
>
> Maggie

Thanks for the response! I'm thinking the '$' in front of the A is the issue.
Isn't that what makes it 'absolute'? But I'll give you an example:

Cells F5,6,7, and 8 are the ACTUAL Last Yr. #'s (there are 4 categories -
Corp. sales, Mail Order, Gov't, and Education).
In the "Actual THIS YR #'s is where I want the conditions that if the number
is greater than last year (the F cells), it should be green, and less than
last year, red. so the formula reads as follows: "cell value is greater than
=$F$5" and "cell value is less than =$F$5" -- that's for the Corp. sales line.

The problem is when I use the format painter, it's still referencing $F$5
instead of 6, 7, or 8 for the other categories.

Do you know what I mean?? Even being able to do a "block" of numbers at a
time would drop my conditional formatting task down to 30 blocks from the
current 120 (having to format each cell separate).

Thanks again..
Maggie

"bpeltzer" wrote:

> You should be able to create the CF once then use the format painter. It's
> hard to be specific without knowing more details of your spreadsheet's
> layout, but the key will be setting up the CF with a formula using a relative
> (or mixed) reference (eg A4 or A$4 rather than $A$4).
> If you'd like more specifics, please post with the condition you've got
> specified currently, and some details of the layout.
> --Bruce
>
> "Maggie Boby" wrote:
>
> > I have a sales goal tracking spreadsheet that represents one period (month)
> > at a time. I have Sun - Sat across the top, then the categories (retail,
> > Corp, mail order, etc on the side.
> > For each day of the week, I have the Last Year's ACTUAL sales, the GOAL
> > number (usually 150% of LY), then THIS YEAR'S ACTUAL sales number. Obviously,
> > this is repeated three more times (going down the spreadsheet for each week).
> > I have used the conditional formatting to tell the TY Actual number to be
> > red or green based on if we exceed LY actual or not for each category. No
> > problem there. The problem is when I tried to use Format Painter so that I
> > didn't have to do every TY Actual cell, it keeps the reference cell instead
> > of understanding it needs the corresponding LY Actual cell that is two
> > columns over from it.
> >
> > Is there a quick way to format the TY Actual cells?
> >
> > Thanks in advance for you help!
> >
> > Maggie

Actually, it's the $ in fron of the 5. $F$5 is an absolute reference; if you
copy the cell containing that reference right OR down, the cell reference
won't change. The $ indicates which component(s) of the reference are fixed.
By changing the reference to $F5, the row part of the cell reference will
change with the cell containing that reference is copied up or down (the
column, F, would remain fixed when you copy left or right because of the $ in
front of the F).
So you should be able to just change the first such condition, making at
least the row part a relative reference. Then the format painter will
operate as you as expecting.

"Maggie Boby" wrote:

> Thanks for the response! I'm thinking the '$' in front of the A is the issue.
> Isn't that what makes it 'absolute'? But I'll give you an example:
>
> Cells F5,6,7, and 8 are the ACTUAL Last Yr. #'s (there are 4 categories -
> Corp. sales, Mail Order, Gov't, and Education).
> In the "Actual THIS YR #'s is where I want the conditions that if the number
> is greater than last year (the F cells), it should be green, and less than
> last year, red. so the formula reads as follows: "cell value is greater than
> =$F$5" and "cell value is less than =$F$5" -- that's for the Corp. sales line.
>
> The problem is when I use the format painter, it's still referencing $F$5
> instead of 6, 7, or 8 for the other categories.
>
> Do you know what I mean?? Even being able to do a "block" of numbers at a
> time would drop my conditional formatting task down to 30 blocks from the
> current 120 (having to format each cell separate).
>
> Thanks again..
> Maggie
>
> "bpeltzer" wrote:
>
> > You should be able to create the CF once then use the format painter. It's
> > hard to be specific without knowing more details of your spreadsheet's
> > layout, but the key will be setting up the CF with a formula using a relative
> > (or mixed) reference (eg A4 or A$4 rather than $A$4).
> > If you'd like more specifics, please post with the condition you've got
> > specified currently, and some details of the layout.
> > --Bruce
> >
> > "Maggie Boby" wrote:
> >
> > > I have a sales goal tracking spreadsheet that represents one period (month)
> > > at a time. I have Sun - Sat across the top, then the categories (retail,
> > > Corp, mail order, etc on the side.
> > > For each day of the week, I have the Last Year's ACTUAL sales, the GOAL
> > > number (usually 150% of LY), then THIS YEAR'S ACTUAL sales number. Obviously,
> > > this is repeated three more times (going down the spreadsheet for each week).
> > > I have used the conditional formatting to tell the TY Actual number to be
> > > red or green based on if we exceed LY actual or not for each category. No
> > > problem there. The problem is when I tried to use Format Painter so that I
> > > didn't have to do every TY Actual cell, it keeps the reference cell instead
> > > of understanding it needs the corresponding LY Actual cell that is two
> > > columns over from it.
> > >
> > > Is there a quick way to format the TY Actual cells?
> > >
> > > Thanks in advance for you help!
> > >
> > > Maggie

Bruce:
You are an angel! I understood what you meant, went back to my
spreadsheet and did as you suggested... but only got a half-fix... until I
figured out that I had to take the 'fixed' out of the cell letter AND number.
So instead of having $F$5 or $F5, I really just needed F5 and viola! we're
good to go!

Thanks for your expertise! Have a great weekend!

Maggie

"bpeltzer" wrote:

> Actually, it's the $ in fron of the 5. $F$5 is an absolute reference; if you
> copy the cell containing that reference right OR down, the cell reference
> won't change. The $ indicates which component(s) of the reference are fixed.
> By changing the reference to $F5, the row part of the cell reference will
> change with the cell containing that reference is copied up or down (the
> column, F, would remain fixed when you copy left or right because of the $ in
> front of the F).
> So you should be able to just change the first such condition, making at
> least the row part a relative reference. Then the format painter will
> operate as you as expecting.
>
> "Maggie Boby" wrote:
>
> > Thanks for the response! I'm thinking the '$' in front of the A is the issue.
> > Isn't that what makes it 'absolute'? But I'll give you an example:
> >
> > Cells F5,6,7, and 8 are the ACTUAL Last Yr. #'s (there are 4 categories -
> > Corp. sales, Mail Order, Gov't, and Education).
> > In the "Actual THIS YR #'s is where I want the conditions that if the number
> > is greater than last year (the F cells), it should be green, and less than
> > last year, red. so the formula reads as follows: "cell value is greater than
> > =$F$5" and "cell value is less than =$F$5" -- that's for the Corp. sales line.
> >
> > The problem is when I use the format painter, it's still referencing $F$5
> > instead of 6, 7, or 8 for the other categories.
> >
> > Do you know what I mean?? Even being able to do a "block" of numbers at a
> > time would drop my conditional formatting task down to 30 blocks from the
> > current 120 (having to format each cell separate).
> >
> > Thanks again..
> > Maggie
> >
> > "bpeltzer" wrote:
> >
> > > You should be able to create the CF once then use the format painter. It's
> > > hard to be specific without knowing more details of your spreadsheet's
> > > layout, but the key will be setting up the CF with a formula using a relative
> > > (or mixed) reference (eg A4 or A$4 rather than $A$4).
> > > If you'd like more specifics, please post with the condition you've got
> > > specified currently, and some details of the layout.
> > > --Bruce
> > >
> > > "Maggie Boby" wrote:
> > >
> > > > I have a sales goal tracking spreadsheet that represents one period (month)
> > > > at a time. I have Sun - Sat across the top, then the categories (retail,
> > > > Corp, mail order, etc on the side.
> > > > For each day of the week, I have the Last Year's ACTUAL sales, the GOAL
> > > > number (usually 150% of LY), then THIS YEAR'S ACTUAL sales number. Obviously,
> > > > this is repeated three more times (going down the spreadsheet for each week).
> > > > I have used the conditional formatting to tell the TY Actual number to be
> > > > red or green based on if we exceed LY actual or not for each category. No
> > > > problem there. The problem is when I tried to use Format Painter so that I
> > > > didn't have to do every TY Actual cell, it keeps the reference cell instead
> > > > of understanding it needs the corresponding LY Actual cell that is two
> > > > columns over from it.
> > > >
> > > > Is there a quick way to format the TY Actual cells?
> > > >
> > > > Thanks in advance for you help!
> > > >
> > > > Maggie