software

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

Bebzquot;Dave Petersonquot; wrote:

gt; Some place in that formula, you have a cell reference like: $A$1.
gt;
gt; Those dollar signs tell excel to not adjust that portion of the address when the
gt; cell is copied.
gt;
gt; If you remove the $ in front of the row (so it looks like: $A1), then the row
gt; portion will adjust when you drag the formula down the column.
gt;
gt; Same thing if you remove the $ in front of the column letter, then that column
gt; portion of the address will adjust if you copy the cell to the right (or left).
gt;
gt; bebz wrote:
gt; gt;
gt; gt; OK Bob, Now that I have the formula, with your help and minor changes to your
gt; gt; formula I was able to have it show what I was looking for.
gt; gt; Thank you!
gt; gt; But I have another issue now;
gt; gt; When I copy the formula to the rest of the cells in the same or diferent
gt; gt; column the row number does not change automatically.
gt; gt; I have to manually change every number for every cell. It is very time
gt; gt; consuming when I have to work with thousand or more cells per column.
gt; gt; I know it can be done because my other formula use to do this.
gt; gt; I am sure it's a simple but I am stuck!
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Bebz
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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:
gt;
gt; Guys you are unbelievable!!!
gt; You don't know how much you have helped with these formulas.
gt; Thanks to all who have replied, I appreciate it so much and also thanks to
gt; Microsoft for generating this website.
gt; Now for the next issue I have regarding the cells I'm working with;
gt; I would like to be able to add the days open and also how many capa's!
gt; This would be using the same cells.
gt; At first I thought I would save both formulas, add amp; record days open and
gt; then copy paste formula for number of capa's.
gt; But of course that would not work because I reference the total of days in a
gt; seperate cell and when I copy the formula for the number of capa's it changes
gt; the total. Which I am sure you knew this without me telling you!
gt;
gt; Now I think if I color code the cells with number of days and be able to sum
gt; the color coded cells this should give me number of capa's per column.
gt;
gt; Am I right? And if I am how do I do this??
gt;
gt; Thanks to all
gt;
gt; Bebz
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Some place in that formula, you have a cell reference like: $A$1.
gt; gt;
gt; gt; Those dollar signs tell excel to not adjust that portion of the address when the
gt; gt; cell is copied.
gt; gt;
gt; gt; If you remove the $ in front of the row (so it looks like: $A1), then the row
gt; gt; portion will adjust when you drag the formula down the column.
gt; gt;
gt; gt; Same thing if you remove the $ in front of the column letter, then that column
gt; gt; portion of the address will adjust if you copy the cell to the right (or left).
gt; gt;
gt; gt; bebz wrote:
gt; gt; gt;
gt; gt; gt; OK Bob, Now that I have the formula, with your help and minor changes to your
gt; gt; gt; formula I was able to have it show what I was looking for.
gt; gt; gt; Thank you!
gt; gt; gt; But I have another issue now;
gt; gt; gt; When I copy the formula to the rest of the cells in the same or diferent
gt; gt; gt; column the row number does not change automatically.
gt; gt; gt; I have to manually change every number for every cell. It is very time
gt; gt; gt; consuming when I have to work with thousand or more cells per column.
gt; gt; gt; I know it can be done because my other formula use to do this.
gt; gt; gt; I am sure it's a simple but I am stuck!
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; Bebz
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson