Help! Data Validation/Filters......

[genuinegal]

Hi all,

Ok, rather than me filtering text, or certain amounts, can i filter 'COLOUR'?

Thanks!

GG

 

[GlennUK]

How are the cells coloured? Did you fill in each one individually? If so, was there any rationale behind your choices? Or was it done via Conditional Formatting? Is so, what are the conditions?

 

[genuinegal]

Hi Glenn,

I remember you from last time and you were very helpful!

Ok, Ive got a colum called 'Suppliers' and each of the cells below the column are either coloured, 'Blue' or 'Red'. There are a list of different suppliers listed under this column heading, and the ones that are highlighted blue are 'Non-Contracted' and the ones that are highlighted 'Red' are 'Contracted' suppliers. Say for eg, i wish to filter the Red.....

Now, having done a search on this board, someone suggested that i look at this link:

http://www.cpearson.com/excel/SortByColor.htm

Now im not an expert with VB, but ive managed to copy and paste the formulae into a new VB window. Ive got as far as this paragraph on the page:

"Of course, change the reference A1 to the first cell in the range. Use Edit, Fill, Down to fill this formula down to the entire range of data you want to sort."

It's saying that that in these cells, i'll see numbers between 1 and 56, but i dont know what he's going on about? Plus, how do i sort out the Colour Index?

Your help is appreciated once again!

Thanking you!

GG

 

[GlennUK]

That paragraph starts off with the words "Then, in the newly created column ..." , so, are you entering the new formula in your new column?

Once you've got this working, you'll see numbers as the results of the formulae, the ColorIndex number for the cell being tested. One of the numbers will match Red, and another will match Blue ... wait and see, honest!

 

[genuinegal]

hellloooo,


Ok, i created a new column next to the column i was telling you about the list of suppliers.

I click into the cell (below where the heading goes in the new column)then copied and pasted the following formulae into the formulae bar at the top:

=ColorIndexOfCell(A1,FALSE,TRUE)

I changed the 'A1' in the formulae, to 'U17' - as this is the cell reference where the first supplier is listed in the column headed 'Suppliers'. (god, this must sound so confusing!).

In the new column to the right of the original list, i clicked and dragged the formulae down to the entire range of the data i want to sort.

Now, the confusing bit is this '1-56' colour index chart. Which cell do i click in to see the numbers 1-56?

Told you i was thick....hahah

I really need a dummies guide.....(

 

[GlennUK]

So, what are the results in the new column then?

 

[genuinegal]

Can you bare with me 5 mins? Im going to try and take a screen print of the spreadsheet and upload it on the net, which i will then give you the link, is that ok?

Thanks!

 

[GlennUK]

That screenprint has a resolution too low to be able to see the formula ... and if I squint, I think I can see a result of #NAME? in your new column.

If you are getting #NAME? it means that the formula you are using has something unrecognizable in it ... for example the new function you should have created. Tell us all the formula you are using, and what you did to create your new function in the VBE.

The recommended tool for generating screen-shots is Colo's HTML maker as mentioned all over the place in this forum.