Skipping Cells Based on Other Cells

Microsoft Office excel 2007 home & stude...
July 9, 2010 at 15:18:54
Specs: Windows XP
I have a table that I am trying to consolidate as it changes rapidly. I want to skip cells and ignore columns. I am using the colorfunction to denote good, neutral, bad and very bad. For example, if I have:[not every cell has information]

Food yes; Schools good
Bars ; Technology
Retail no; Pop Growth steady
Office no;
Beach ;
Crime ;
Location good; Airport 5 mi

I want to be able to roll it up into columns labeled good, neutral, bad and very bad. Using the colorfunction won't be a problem. But I want it to look like this:


Pop Growth


Can anyone please help me? Its a lot of data and this would be huge. Thank you in advance for any ideas you have.


See More: Skipping Cells Based on Other Cells

July 9, 2010 at 19:56:29
Let's start with the things I don't understand about your question:

1 - What is this colorfunction you speak of? That is something I have never heard of.

2 - Your subject line says "Skipping Cells Based on Other Cells". I don't see how that relates to what you want to do. Care to explain?

3 - re: I want to skip cells and ignore columns. Care to tells us what cells you want to skip and what columns you want to ignore? How are formulas and/or a macro going to know what to skip/ignore?

OK, so assuming you can clear those things up...

You example output uses categories that don't exist in your input. e.g. I don't see Neutral or Bad in your input and I don't see No or Yes in your output.

I assume that there is some correlation between the words in your input data and the categories in your output data. Would you like to explain those to us?

Finally, we're going to need a little more specifics as far as the layout of your input. You said that [not every cell has information] but how do we know which cells do and which cells don't?

Report •

July 13, 2010 at 07:09:35
The colorfunction lets you recognize fill color and either sum or count the cells based on the color.

I have multiple columns. The first column is a category. The second ("fill column") is how the location performs in that category and a fill color whether it is good, neutral, bad or very bad. Then I have a dummy column to perform another operation. Then the columns start over in this same order with more categories. Some categories do not apply to every location so the "fill column" has no data or no fill color.

So on another tab ("roll-up tab") I want the categories next to certain fill colors to show up in a column for only that color- all good together, all bad together, etc. I want to skip the fill cells that have no color because they do not apply to this location. So on the "roll-up tab" I want a list of these categories without spaces which I have gotten trying several formulas. Other wise I get a category and then several spaces or error message - depending on how I tried it- until I get to that fill color and then it will give me the category. I want to skip these spaces and other fill colors so that my columns look like a list of consecutive items.

Thank you. I am not versed in the terminology so thanks for trying to understand.

Report •

July 13, 2010 at 11:15:01
I'll assume the ColorFunction is the one found at Dave Hawley's site,

If that's the case, I don't see how these 2 statements go together:

The colorfunction lets you recognize fill color and either sum or count the cells based on the color.

I am using the colorfunction to denote good, neutral, bad and very bad.

Since ColorFunction sums or counts cells based on the color, how are you using it to "denote" a particular status?

Is this your real question:

"Sheet1 has cells that contain values. Some of these cells have colors next to them.

I want to place each color in Row 1 of another sheet and then list all of the values that correspond to that color in the respective column."

Report •

Related Solutions

July 13, 2010 at 14:13:16
Yes, the colorfunction is from Dave Hawley's site,

I haven't exactly figured out how I am going to use the colorfunction. Right not it is in the dummy column (ie column 3, 6, 9 etc.). It is basically a nested if statement that determines if the cell is a certain color.


[Column A is category, Column B is information about the category with a fill color and Column C is the dummy column with the nested if statement.] It reads that if cell B4 is the same color as A33 then it counts it (false means count, true mean sum). If it counts it then 1=1 and the number 4 is entered. Same thing for the next 3 colors. And if there is no fill then it enters a 0.

So I have to use it in this manner to have it recognize the fill color because yes the colorfunction counts and sums cells based on the fill color. The dummy column formula works fine. It was the only way I knew to try and get the categories in column A, D, G etc. to rollup onto another tab without an even crazier nest if statement. [It was going to be part of the process that I have yet to figure out.]

All I want is the categories (column A) in a new column based on the fill color in column B on the same row. And I don't want spaces in between where there were other colors or blanks.

Thanks for all the help.

Report •

July 14, 2010 at 05:19:41
This code will read down Sheet1 Column A and pick out the color from Column B for each item.

It will then check the colors in Sheet2 A1:B1 (4 colors in this example) looking for the matching color.

If it finds a matching color it will determine the next empty row in that column and place the current item from Sheet1 in that cell.

The code could be modified to clear all of the items in Sheet2 first, so that each time you ran it would provide an updated item listing. As written, it will add items to the bottom of the existing lists each time it is run.

Option Explicit
Sub ListByColor()
Dim myItem As Range
Dim nxt1_Row, nxt2_Row, lastA_Row As Integer
Dim sht2_Colors, sht1_Colors As Integer
'Find last used row  in Sheet1 Column A
 lastA_Row = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
'Loop through Items in Column A
  For Each myItem In Sheets(1).Range("A1:A" & lastA_Row)
'Determine color for each item from Column B
  sht1_Colors = myItem.Offset(0, 1).Interior.ColorIndex
'Loop through colors in Sheet2 Row 1
   For sht2_Colors = 1 To 4
'If color from Column B matches Column in Row 1, place
'the item in the next empty row in that column
    If sht1_Colors = Sheets(2).Cells(1, sht2_Colors).Interior.ColorIndex Then
     nxt2_Row = Sheets(2).Cells(Rows.Count, sht2_Colors).End(xlUp).Row + 1
     Sheets(2).Cells(nxt2_Row, sht2_Colors) = myItem
     Exit For
    End If
End Sub

Report •

July 14, 2010 at 12:44:50
That is amazing. You are a genius. It works perfectly for two columns- one category and one color. Can you tell me how to modify it so that it reads the colors from multiple columns on the same sheet while excluding the region outside the range? For example,
Column A- Category
Column B- Color
Column C- Category
Column D- Color

Because I have things below the columns.

Also, how would you modify it so that it clears a certain part of sheet 2 while leaving other parts? Because I noticed it started at add items to the bottom like you said it would. But I don't want it to. Thank you.

Report •

Ask Question