Hi Folks,
I have a workbook with a list of associates and the hours they are to work
in a week.
I have a second workbook with the associates listed acrose the bottom
(b.....t), cells a4....a18 are numbered 15....1, which leaves me with a grid.
I would like to change a cell colour or have the word goal appear above each
associate depending on hours work.
Example:
Associate_1 is working 40 hours their goal would be to achieve 10 units,
therefore the #10 cell turns green. I can complete the conditional formatting
formula to make this happen and it does work, but unless I am wrong I have to
change the cell references for each cell, a cell at a time there for with 40
associates I have 600 cells to apply conditional formatting to.
Any thoughts on how I can make this easy?
I hope I have explained things clearly enough, the autofill function wont
change the cell references for me.
Thank you in advance,
Ian

You can select a larger area and refer to the active cell in your
conditional formatting, and if you use relative references the formula will
change to the next cell etc.\\ Assume you select A1:E40 and select from A1,
then A1 will be the active cell, assume you use a formula like

=A110

then in B1 it will change to

=B140

--
Regards,

Peo Sjoblom

(No private emails please)


"Ian D. Weatherall" <Ian D. wrote in
message ...
Hi Folks,
I have a workbook with a list of associates and the hours they are to work
in a week.
I have a second workbook with the associates listed acrose the bottom
(b.....t), cells a4....a18 are numbered 15....1, which leaves me with a
grid.
I would like to change a cell colour or have the word goal appear above
each
associate depending on hours work.
Example:
Associate_1 is working 40 hours their goal would be to achieve 10 units,
therefore the #10 cell turns green. I can complete the conditional
formatting
formula to make this happen and it does work, but unless I am wrong I have
to
change the cell references for each cell, a cell at a time there for with
40
associates I have 600 cells to apply conditional formatting to.
Any thoughts on how I can make this easy?
I hope I have explained things clearly enough, the autofill function wont
change the cell references for me.
Thank you in advance,
Ian

Hi!

Based on your description the grid is in the range B4:T18

You can apply CF to the entire grid with a single formula.

Select the entire grid range B4:T18
Goto FormatConditional Formatting

Now, when you enter your formula, cell B4 is the active cell. So enter the
formula that would apply to cell B4 and don't use absolute referencing.
Excel will automatically adjust the references in the formulas for the other
cells in the grid.

Of course, this all depends on what your formula is and would help if you
were to post more specific details. But in general, you should not have to
manually enter 600 formulas.

Biff

"Ian D. Weatherall" <Ian D. wrote in
message ...
Hi Folks,
I have a workbook with a list of associates and the hours they are to work
in a week.
I have a second workbook with the associates listed acrose the bottom
(b.....t), cells a4....a18 are numbered 15....1, which leaves me with a
grid.
I would like to change a cell colour or have the word goal appear above
each
associate depending on hours work.
Example:
Associate_1 is working 40 hours their goal would be to achieve 10 units,
therefore the #10 cell turns green. I can complete the conditional
formatting
formula to make this happen and it does work, but unless I am wrong I have
to
change the cell references for each cell, a cell at a time there for with
40
associates I have 600 cells to apply conditional formatting to.
Any thoughts on how I can make this easy?
I hope I have explained things clearly enough, the autofill function wont
change the cell references for me.
Thank you in advance,
Ian

Hi. You can use the format painter to copy conditional formats. Cell
refs will change if you don't use the $. Just do one CF formula and
format paint it into the others. Same as copy, then paste special --
formats. Will this work for you?

Len

Ian D. Weatherall wrote:
Hi Folks,
I have a workbook with a list of associates and the hours they are to work
in a week.
I have a second workbook with the associates listed acrose the bottom
(b.....t), cells a4....a18 are numbered 15....1, which leaves me with a grid.
I would like to change a cell colour or have the word goal appear above each
associate depending on hours work.
Example:
Associate_1 is working 40 hours their goal would be to achieve 10 units,
therefore the #10 cell turns green. I can complete the conditional formatting
formula to make this happen and it does work, but unless I am wrong I have to
change the cell references for each cell, a cell at a time there for with 40
associates I have 600 cells to apply conditional formatting to.
Any thoughts on how I can make this easy?
I hope I have explained things clearly enough, the autofill function wont
change the cell references for me.
Thank you in advance,
Ian

Thankyou for the suggestions, I will try certainly try them.
I have been playing alittle more today and am trying this method.

b3=if((b20/4)=15,"GOAL"," ")
b4=if((b20/4)=14,....)
b5=if((b20/4)=13.......)
b20=hours scheduled, goal is 1 unit every 4 hours, b20 will always be
divisable by 4.

I do have another question related to this new method though, can I use auto
fill to move down the column without the b20 reference not increasing.

Then set CF to look for the word goal.

Thank you again,
IAn


"LenB" wrote:

Hi. You can use the format painter to copy conditional formats. Cell
refs will change if you don't use the $. Just do one CF formula and
format paint it into the others. Same as copy, then paste special --
formats. Will this work for you?

Len

Ian D. Weatherall wrote:
Hi Folks,
I have a workbook with a list of associates and the hours they are to work
in a week.
I have a second workbook with the associates listed acrose the bottom
(b.....t), cells a4....a18 are numbered 15....1, which leaves me with a grid.
I would like to change a cell colour or have the word goal appear above each
associate depending on hours work.
Example:
Associate_1 is working 40 hours their goal would be to achieve 10 units,
therefore the #10 cell turns green. I can complete the conditional formatting
formula to make this happen and it does work, but unless I am wrong I have to
change the cell references for each cell, a cell at a time there for with 40
associates I have 600 cells to apply conditional formatting to.
Any thoughts on how I can make this easy?
I hope I have explained things clearly enough, the autofill function wont
change the cell references for me.
Thank you in advance,
Ian

If you put b$20 in the formula instead of b20, it keeps the 20 from
increasing when autofill or copy paste is used. I think that is what
you are asking.

Len

Ian D. Weatherall wrote:
Thankyou for the suggestions, I will try certainly try them.
I have been playing alittle more today and am trying this method.

b3=if((b20/4)=15,"GOAL"," ")
b4=if((b20/4)=14,....)
b5=if((b20/4)=13.......)
b20=hours scheduled, goal is 1 unit every 4 hours, b20 will always be
divisable by 4.

I do have another question related to this new method though, can I use auto
fill to move down the column without the b20 reference not increasing.

Then set CF to look for the word goal.

Thank you again,
IAn