OK Bob, Now that I have the formula, with your help and minor changes to your
formula I was able to have it show what I was looking for.
Thank you!
But I have another issue now;
When I copy the formula to the rest of the cells in the same or diferent
column the row number does not change automatically.
I have to manually change every number for every cell. It is very time
consuming when I have to work with thousand or more cells per column.
I know it can be done because my other formula use to do this.
I am sure it's a simple but I am stuck!

Thanks

Bebz

Some place in that formula, you have a cell reference like: $A$1.

Those dollar signs tell excel to not adjust that portion of the address when the
cell is copied.

If you remove the $ in front of the row (so it looks like: $A1), then the row
portion will adjust when you drag the formula down the column.

Same thing if you remove the $ in front of the column letter, then that column
portion of the address will adjust if you copy the cell to the right (or left).

bebz wrote:

OK Bob, Now that I have the formula, with your help and minor changes to your
formula I was able to have it show what I was looking for.
Thank you!
But I have another issue now;
When I copy the formula to the rest of the cells in the same or diferent
column the row number does not change automatically.
I have to manually change every number for every cell. It is very time
consuming when I have to work with thousand or more cells per column.
I know it can be done because my other formula use to do this.
I am sure it's a simple but I am stuck!

Thanks

Bebz

--

Dave Peterson

Guys you are unbelievable!!!
You don't know how much you have helped with these formulas.
Thanks to all who have replied, I appreciate it so much and also thanks to
Microsoft for generating this website.
Now for the next issue I have regarding the cells I'm working with;
I would like to be able to add the days open and also how many capa's!
This would be using the same cells.
At first I thought I would save both formulas, add & record days open and
then copy paste formula for number of capa's.
But of course that would not work because I reference the total of days in a
seperate cell and when I copy the formula for the number of capa's it changes
the total. Which I am sure you knew this without me telling you!

Now I think if I color code the cells with number of days and be able to sum
the color coded cells this should give me number of capa's per column.

Am I right? And if I am how do I do this??

Thanks to all

Bebz


"Dave Peterson" wrote:

Some place in that formula, you have a cell reference like: $A$1.

Those dollar signs tell excel to not adjust that portion of the address when the
cell is copied.

If you remove the $ in front of the row (so it looks like: $A1), then the row
portion will adjust when you drag the formula down the column.

Same thing if you remove the $ in front of the column letter, then that column
portion of the address will adjust if you copy the cell to the right (or left).

bebz wrote:

OK Bob, Now that I have the formula, with your help and minor changes to your
formula I was able to have it show what I was looking for.
Thank you!
But I have another issue now;
When I copy the formula to the rest of the cells in the same or diferent
column the row number does not change automatically.
I have to manually change every number for every cell. It is very time
consuming when I have to work with thousand or more cells per column.
I know it can be done because my other formula use to do this.
I am sure it's a simple but I am stuck!

Thanks

Bebz

--

Dave Peterson

First, it's usually lots easier to use helper cells that contain a
value--instead of trying to use the colors of the cells.

In fact, you'd probably end up with some of your own functions (written in VBA)
to count/sum anything based on the color of the cell.

I think I'd use a formula that returned some kind of indicator (yes/no,
X/(blank)) and then maybe use =countif() or =sumif() to count/add a range based
on that indicator.

But I don't understand what you're really doing.

You may want to post more details about what a capa is and where you get the
days open count.

bebz wrote:

Guys you are unbelievable!!!
You don't know how much you have helped with these formulas.
Thanks to all who have replied, I appreciate it so much and also thanks to
Microsoft for generating this website.
Now for the next issue I have regarding the cells I'm working with;
I would like to be able to add the days open and also how many capa's!
This would be using the same cells.
At first I thought I would save both formulas, add & record days open and
then copy paste formula for number of capa's.
But of course that would not work because I reference the total of days in a
seperate cell and when I copy the formula for the number of capa's it changes
the total. Which I am sure you knew this without me telling you!

Now I think if I color code the cells with number of days and be able to sum
the color coded cells this should give me number of capa's per column.

Am I right? And if I am how do I do this??

Thanks to all

Bebz

"Dave Peterson" wrote:

Some place in that formula, you have a cell reference like: $A$1.

Those dollar signs tell excel to not adjust that portion of the address when the
cell is copied.

If you remove the $ in front of the row (so it looks like: $A1), then the row
portion will adjust when you drag the formula down the column.

Same thing if you remove the $ in front of the column letter, then that column
portion of the address will adjust if you copy the cell to the right (or left).

bebz wrote:

OK Bob, Now that I have the formula, with your help and minor changes to your
formula I was able to have it show what I was looking for.
Thank you!
But I have another issue now;
When I copy the formula to the rest of the cells in the same or diferent
column the row number does not change automatically.
I have to manually change every number for every cell. It is very time
consuming when I have to work with thousand or more cells per column.
I know it can be done because my other formula use to do this.
I am sure it's a simple but I am stuck!

Thanks

Bebz

--

Dave Peterson


--

Dave Peterson