Excel Formula for dynamic columns

Question

I'll try to make sense of this the best I can. I'm pretty horrible at making things clear. :) So...here it goes....

I have a spread sheet that is a list of seeds I have for growing peppers. Here are the columns I have, and I will explain more after.

Crop | Color | Generation | Species | Source | Scoville | Flavor | Heat | Notes | 2012 Type | 2012 Name 1 | 2012 Name 2 | 2013 Type | 2013 Name 1 | 2013 Name 2 | etc.

Ok. So "2012 Type" is a list that will contain either a blank, "N", "O", or "M". All I care about is whether it is blank or not for this question. If it is NOT blank I want to highlight the Crop name with a green background to show that I have grown this crop. To do that through the Conditional Formatting is a no brainer. However, my issue begins when I, as usual make things more complicated. When I add "2013 Type", "2014 Type", etc. I want to check for those also. So then my formula becomes an OR; is there a value in 2012 or 2013 or 2014, etc. Part of question is that. How do I write a formula that is dynamic enough to pickup the new fields I add each year...2015 Type, 2016 Type...etc.?

I also have a ton of other stuff going on, but I think I may have that figured out...sort of.

Thank you for your help, appreciate it.

Edit: Okay. I feel I need to explain how I am trying to develop my whole worksheet, one to check my thinking, and two to clear up the goals.

In addition to what I have explained above, I have tabs that read 2012, 2013, grown, not-grown, MasterList, and Criteria.

MasterList is what we are talking about here.

Criteria is what I am using for an advanced filter to copy crops that to the appropriate sheets. So, if I have a a crop marked in column "2012 Type", then it will be copied to the 2012 sheet. It will also be listed under the tab Grown. Same if it were marked under "2013 Type" etc.

Basically, I am using tabs to filter out specifics from my MasterList so that I can find what I'm looking for quickly, or view everything as a whole.

Once again, I am great at making things over complicated. Couple that with the fact that I am still fairly new to Excel coding...and you have a disaster. :)

Thank you all for listening. :)

Answer 1

You might consider reformatting your data so that it only grows in the row direction and not the column direction. I'm thinking

Crop | Color | Generation | Species | Source | Scoville | Flavor | Heat | Notes | Year | Type | Name 1 | Name 2 

Then you could use pivot tables, formulas, and array formulas to present the data in various ways.

Answer 2

Try using a COUNTIFS formula in conditional formatting, e.g. this formula

=COUNTIFS($1:$1,"*Type*",2:2,"<>")

....will count the number of cells in row 2 which have some value....and where row 1 has "type" contained in the header row for the same column.

For your condition you want to know whether the result of the COUNTIFS formula is zero or not (zero signifying no entries in any of the type columns for that row) so for green use

=COUNTIFS($1:$1,"*Type*",2:2,"<>")>0

Note: COUNTIFS is only available in Excel 2007 and later, for earlier versions you can use this formula

=SUM(ISNUMBER(SEARCH("type",$1:$1))*(2:2<>""))>0

Answer 3

I would create a worker column(you can always hide it later) that concatenates all your Type columns together into one column:

=CONCATENATE(K2,N2,Q2,T2,W2,Z2,AC2,AF2,AI2,AL2,AO2,AR2,AU2,AX2)

=$A$2:$ZZ$10000

=IF($A2="",FALSE,TRUE)

It's important that you enter the formulas and conditional format formulas and applied to range accurately - with the correct syntax(notice the use of $).

Good Luck.

Answer 4

Actually for an annual exercise I would just go for amending the conditional formatting as part of your procedure when you add the columns with the OR in it as you mention it. The condition should be (as you probably already know):

=OR(ISBLANK($J2),ISBLANK($M2))

I'll have another thought about making it smarter but I have a feeling already that will involve some more coding and such, making the effort for this bigger then the benefit for the annual updates.