By far the most popular post of all time on the blog is my post on the use of the dollar sign to fix rows and columns in Excel:
EXCEL TIP: The dollar sign ($) in a formula - Fixing cell references
Another much read post is my explanation of the IF statement which is one of the most useful functions to grasp in Excel:
EXCEL TIP: The IF Statement made simple
A more recent post that is proving popular is my assorted tips on tidying up text:
EXCEL TIP: Simple tips for tidying up text in Excel
When you are dealing with imperfect data, it is often handy to be able to deal with errors that make your reports look messy, this post deals with an approach to never needing to see an error message on your reports again:
EXCEL TIP: Eliminating #DIV/0! and other errors automatically using ISERROR
Finally, this post gives a simple tip for learning any new function that you want:
EXCEL TIP: Use any Excel function in seconds
I hope there's something new to learn there for you.
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".
Hi Glen,
ReplyDeleteWe arrived at a VBA / Versus formula debate a while ago. Today I discovered a little challenge. Colleagues of mine are using Excel 2007 which delivers filtering by colour.
Unfortunately my team/corporate group standard does not allow the use of 2007 only 2003.
Are there any formulas I can use for filtering a set of 600 rows by there cell background colour??
Not to tease ....but I do have a VBA solution for us.
Hope all is going well for you and look forward to our resulting communications !
Cheeers.. please keep tips coming .....some are real gems.
Thanks again
Kind Regards
Peter Lewson
Peter
ReplyDeleteI couldn't think of anything myself but came across the following solution from Ponsonby on the Mr Excel website
"Provided that the colours are not the result of conditional formatting, it could be done by adding a column to contain a formula that returns the colour codes and then filtering by that column :-
1. Go to Insert>Names>Define.
2. In the NamesInWorkbook box type Clr
3. In the RefersTo box type =GET.CELL(38,INDIRECT("rc[1]",FALSE))
4. Click OK
5. Insert a column immediately before the column with the colours
6. In the first cell of the inserted column type =Clr
7. Fill down as far as required. This returns the colour codes.
8. Filter by the inserted column.
NB :
Do not copy/paste to a different worksheet any cells that contain the formula =Clr , since it will cause XL to crash."
I've tried this and it works, however if the colours change after entering =Clr in the cells, the number will not update until next time the spreadsheet recalculates, so it is safer to do an F9 before you filter.
If the colours are the result of Conditional Formatting, then just create an IF or VLOOKUP using the same rules as for the conditional formatting and filter on that.
Glen not worked for me. what is "rc[1" ? is this create problems ?
ReplyDeleteRgds,
sharif
This is a reference to the cell to the right of the current cell written in R1C1 style. It means current row (r), one column to the right (c[1]). There must be square brackets around the 1 and quotes round the full reference, i.e.
Delete"rc[1]"
It should work.
These are BRILLIANT tips Glen, I will tet this live at work tomorrow, but in theory it lookGREAT
DeleteThanks very much
VBA allows me to write a function , save the xlsm spreadsheet as an XLA then utilse this XLA as an Add-in,
Not straightforward.
VERY VERY Effective when running
Thanks Again
Peter L