PDA

View Full Version : Keeping Cells that are Conditional Formated when Sorting Data



Panda
12-06-2010, 10:36 AM
Hi All,

I have a spreadsheet with a recovery plan section towards the bottom which looks at how many components are needed to complete a unit. Each of the cells that are coloured RED have had conditional formatting assigned to them, so that it will turn green only when the correct about of parts have been allocated to completed the unit. (The User inputs this info.)

When I come to sort the data in Ascending order of Total Parts Required to Complete PCB, for some reason all the cells are kept in order apart from the cells that I have applied the conditional formatting to. Can any body help me?

I have attached a copy of the spreadsheet that I am trying to get to work.

Thanks

Phil

Bob Phillips
12-06-2010, 11:13 AM
Your CF doesn't make sense. As I read it, it should always be red or green as you test <, = and >, so it must fit one, but some are gray. And there are no values in M38 etc. to compare against M$8.

Panda
12-07-2010, 04:26 AM
Thanks for your reply XLD, Basically cells B38 to O59 are user entry fields where they will decide how many of each component they are going to assign to a unit. If the assign to little the cell should remain red, if they assign the exact amount they require as detailed within cells B8 to O29 then the cell will change to green, and if they assign to more components then is required for the unit the cell will remain red as they are using more stock then is required.

Eg in E42 if the user enters 1 cell = green, if user enters 2 cell = red, if user enters nothing or a 0 cell = red as E12 says that 1 ‘component 1’ is needed.

Once all of this is complete I want to be able to sort the data by ‘Total Parts Required to Complete PCB’ column, however this is where I loose all my conditional formatting.

Attached is the up to date spreadsheet without the major errors in (Namely the component names contained within the Recovery Plan)

Any help will be greatly appreciated =:)

Phil

Bob Phillips
12-07-2010, 05:04 AM
Well, two things. Why does E42 have CF and E41 not have CF? Why do you not have a CF to format the cell as gray if empty?

Panda
12-07-2010, 06:16 AM
I haven't set a CF for the grey cells becuase I needed the three conditions,

- If the number in cell is greater then what is required = red
- If the cell is = to the number that is required = green
- If the cell is Less then what is required = red

The grey cells are there to indicate to the user that that particular unit does not have that particular component fitted to it.

Have I gone about this the wrong way?

Panda
12-07-2010, 08:56 AM
Bump....Can anyone help?

Bob Phillips
12-14-2010, 02:48 AM
If you were to set an initial CF of gray when the cell is empty, then green if equal, red if not, that makes more sense to me.

Panda
12-14-2010, 03:03 AM
By doing this, will it allow me to to sort the cells so that they all stay together rather then becoming mixed up. (If you try to sort the sample spreadsheet i attached you will see what I mean)

Thanks

Phil

Bob Phillips
12-14-2010, 03:16 AM
I think we are back to what I didn't understand initially Phil.

Your workbook has no values in the CF cells, so I cannot assess the impact of sorting. Explain the procedure that I can enact to show how it soirts incorrectly.

Panda
12-14-2010, 03:58 AM
I cant seem to attached a better worked example, but I did repost the question earleir this week which can be found at the link below

http://www.vbaexpress.com/forum/showthread.php?t=35334

Thanks again for helping me

Phil