Sorry bit of a newb

I have a relitively large spreadsheet at work that I am trying to basically update the way things are done. Throughout the day we update (color cordinate) this document. Essentually this is what each line consists of:
model:order#:line1:line2:line3:line4:line5:line6:l ine7:line8:line9:line10:total
We would check the order# and see the progress of the line# and color cordinate that respectively.
The question/problem I am having is creating a function/formula that will lookup all the lines with a specific model ID and give me the sum values from the line#s. Essentially the results would look something like
model:total:totalBLANK:total:GREEN:total:YELLOW:to talBLUE
I have created a few that essentially do this but more as a whole of the document and looking up specific items. I have created one that basically did this. The problem is the document changes often and I can't just assign a section of the document to be the lookup area as it can/will change.
Basically I need it to a half dozen or so sum values based on the condition of the lookup range that is dependent on the model ID of that row.

hope that makes sense and that someone has an idea of how I can do this.

Hi walkingmac. Try Chip Pearson's site for information on summing or
counting based on colors: http://www.cpearson.com/excel/colors.htm. You
could also sort based on the model # and then use the subtotal function from
the data menu, if I'm understanding what you're trying to do. HTH
--
Sincerely, Michael Colvin


"walkingmac" wrote:


Sorry bit of a newb

I have a relitively large spreadsheet at work that I am trying to
basically update the way things are done. Throughout the day we update
(color cordinate) this document. Essentually this is what each line
consists of:
model:order#:line1:line2:line3:line4:line5:line6:l ine7:line8:line9:line10:total
We would check the order# and see the progress of the line# and color
cordinate that respectively.
The question/problem I am having is creating a function/formula that
will lookup all the lines with a specific model ID and give me the sum
values from the line#s. Essentially the results would look something
like
model:total:totalBLANK:total:GREEN:total:YELLOW:to talBLUE
I have created a few that essentially do this but more as a whole of
the document and looking up specific items. I have created one that
basically did this. The problem is the document changes often and I
can't just assign a section of the document to be the lookup area as it
can/will change.
Basically I need it to a half dozen or so sum values based on the
condition of the lookup range that is dependent on the model ID of that
row.

hope that makes sense and that someone has an idea of how I can do
this.


--
walkingmac

Thanx for the reply. Actually, I used that site to get to my current state. The problem I am having (and let me try and clearify waht it is I am trying to do with the setup we have running) is that with wach of the order numbers I have 10 line numbers. Each line is running independently and at different levels of completion. What we do is track their pergression throughout the day and color ordinate based on how far they are. Additionally there is anywhere from 1-10 orders per model each of which having 1-10 line #'s each. What I am wanting XL to do for example is have all cells in a range of colums that are blue and a specific model number in its repective row sum'd on a seperate workbook. In other words, I have 8 models we are working on. One model is i830 we'll say. I have 8 orders and each order has 10 line #'s. That leaves me with 80 total orders and each at their own different level of completion. I am wanting to to create a function/formula that will automatically see and calculate all 5 levels of job completion based on the colors assigned to them for that model and the rest of the models respectively. I hope that makes a little more sense. I have created peices like I said based on what I found on Chip's site, however that only gives me calculations of all models listed, not individually. And since this document changes so conistantly and by many people, I can't just assign a range and have it calculate that. If this is making sense to anyone can you help me get this funtion?