Excel Noobery
March 9, 2010 9:15 AM   Subscribe

ExcelFilter: I need to write something pretty simple in Excel. I have big spreadsheet of data. Some rows are fill-colored, and some columns are fill-colored. I need something that will show me (hopefully in a table or something) the cells that have their entire column colored AND have their entire row colored. Returning the value of the cell would be helpful too. (I have a feeling this is an embarassingly easy question to answer)
posted by demagogue to Computers & Internet (5 answers total)
 
This is not very easy to do at all and requires some VBA. For a simple explanation,
try this.
posted by jckll at 9:20 AM on March 9, 2010


Yes, cell colors are hard to work with from inside a formula.
posted by alms at 9:31 AM on March 9, 2010


Only if you have Excel 2007: This isn't an elegant solution but do you should be able to filter by color. Once you set up the filter you can create a column that contains the index stating if the item fits the abovementioned criteria. You should then be able to identify the cell in questions using DGET or similar function.
posted by alrightokay at 9:41 AM on March 9, 2010


Demagogue I have an idea but need to know a few things first:
A few questions, are all the cells 'coloured' in, the same format?
Is this a "You'll Never Do It Again" thing, or one you want to repeat a few times?

The 'simple' part is once you have a list of addresses.
Returning the value of the cell would be helpful too.
This can be achieved using an indirect formula.

Slight aside: I highly dislike Excels formating options for exactly this sort of reason. It just doesn't work well as inputs to a formula. Though the conditional formating in 2007 is great.
posted by 92_elements at 12:51 PM on March 9, 2010


Doing things conditional on format is not very easy; most solutions will use vba. Much easier is to do formatting conditional on highlighting.

Excel just isn't really designed to use highlighting as a form of data storage. Your best bet is to convert it into something more useable first.

This might not work in your particular situation, but this would be my suggestion. Add a row and a column to the top and left of your table (you can group them out later to hide them.) Mark a 1 above every highlighted column and to the left of every highlighted Row. (Yes this might be a little annoying if you have a lot of data, but it should be pretty fast and mindless: one finger on enter one finger on 1)

Select everything and set up conditional formatting so that it does the proper sort of row and column based highlighting and also does a third kind of highlighting for things in both a column and a row. The conditional format will be based on whether or not the row or column has a 1 in the new cells you added (make sure you know how to use $ to set up the right formulas for the formatting). (After you set up the conditional format, fill all the cells with no-fill so that only the conditional formatting remains)
posted by vegetableagony at 8:33 PM on March 11, 2010


« Older Album names that contain the name of the band?   |   Buy Or Rent a Home Newer »
This thread is closed to new comments.