another MS excel question?

Question

what exactly does this mean:

"You could have a hidden column that you could use as the sort criteria - i.e. Red = 1, blue = 2, yellow = 3, etc. Pull your sort off the hidden column. This is the only way that I know to make that work."

i know about hidden columns and everything...but i cant figure out the other part about making red = 1 and the sort criteria stuff.

anything to get me on the right track would be awesome.

Thanks.

Paul

that works! but is there a way to make it automatically replace, say...a red square (with a red 1)when i colour a square red?

Answer 1

Now if you can't easily go through all of your data and type in 1 or 2 or 3, etc., you can do Edit-Replace, and replace all the colors with their respective numbers, so you then sort on the numbers.

1) First, if there is data in those colored cells that you don't want to lose, insert a new column and copy and paste just the formats from the column that you want to sort, to the new column (Edit - Copy, and Edit -Paste Special - Formats)

2) Replace colors with numbers. For example to replace red with 1:
- Select the new column

- Edit - Replace

- Find what: leave empty, but click on Options, and pick the format of the red cells (Patterns tab, red color)

- Replace with: put the number 1, and you can also set any format you want. In fact, if you didn't want the numbers to be visible, you can leave the Pattern as red, and also set the Font color to red, so it will blend in.

3) Once you're done putting numbers where the colors are, do the sort:
- Select all of your data
- Data - Sort, and pick the column to sort by (the new column)

I hope this helps, but add more details to your question, or e-mail through my profile, if you need more info.

Good luck.
______
EDIT:
No, it can't set a value based on the color, but...

You can set it so that if you type a 1, it will color the background red (and color the font red if you like):

Go to the cell you want to format this way:
- Format - Conditional Formatting
- Cell Value Is - equal to - 1
- click Format, and pick Font: red, and Patterns: red
- click the Add button to add conditions for other numbers/colors
- you can have up to three different conditions; if none of the conditions are met, then the format will default to whatever it was set to without the conditional formatting.
- you can highlight a whole range of sales and format it this way, or copy and paste special (formats) to copy the conditional formatting to other cells.
.

Answer 2

Say you have items in your list that you want to sort by their color. If the item is red, you would put "1" in the hidden column. If the item is blue, you would put a "2" in the column. If yellow, use a "3". Then you could sort the hidden column to make all the items sort by "color".

Answer 3

it is been a on a similar time as considering the fact that I did that variety of stuff yet i think of you're proper related to the citation marks ... get rid of them. i think of then the = sign is the place you're going incorrect, attempt removing them too and see what happens.