Conditional Formatting, Filtering & Quick Step

**Rob-UK** · 08-07-2012, 07:38 AM

Hello Experts!

I have a 3 pronged challenge for you! I have uploaded a spreadsheet we use in one of our vineyards and need some help with the "Data" and "Map - Sample Area July 2012" worksheets.

1) The "Map" shows the rows and vines in their relative positions in the vineyard. The "Data" shows the information we have logged against each individual vine. I would like the data to show on the "Map", as a coloured cell. So, for example, a vine with Red Leaf logged against it will be filled red on the map. A dead vine filled grey and so on. If I wanted to show a combination of things ( say a vine with a bent trunk and a trunk wound ) then I would need another colour, or some way of distinguishing the different issues. How could I , and is it possible to, do this?

2) When viewing the "Map" data once I have entered the correct formulas you lovely people are hopefully going to help with (!) , can I filter the view? So, if I only wanted to see "dead" vines on the map , or again a combination of "small trunks and trunk wounds" I have a feeling there are some kind of filters I can set up but do not know how flexible they are with conditional formatting.

3) Finally, if you are still reading, when we are out in the vineyard logging the data on paper, and then transferring it into the excel "data" sheet it seems it would be a lot more productive if I could enter the data directly into an Ipad. Is there a way to set the cells in "data" up so that I can touch the relevent cell to indicate a vine which has a particular issue, so it is marked and puts the code into the cell, rather than having to select the cell and type in the code?

Thanks for any help you can offer. I am getting nowhere fast and would appreciate some expertise.

**NBVC** · 08-07-2012, 09:32 AM

For part 1, if you have a colour for each possible combination, there will be far too many... would you want just one colour to indicate that there is some undefined combination and not just one single defect?, then have a specific colour if there is a single defect... so a total of 9 possible colours?

For part 2, you can filter by colour in XLSX sheets, but I think only by one colour per column....

For part 3, I am unsure how to accomplish....

**Rob-UK** · 08-07-2012, 11:17 AM

Originally Posted by NBVC

For part 1, if you have a colour for each possible combination, there will be far too many... would you want just one colour to indicate that there is some undefined combination and not just one single defect?, then have a specific colour if there is a single defect... so a total of 9 possible colours?

For part 2, you can filter by colour in XLSX sheets, but I think only by one colour per column....

For part 3, I am unsure how to accomplish....

Thanks for the reply,

I get what you mean by the colours. The Vineyard team would like to be able to look at a map and see if there are any patterns; i.e where Red leaf is present, there has also been trunk wounds, or maybe the way wind has bent trunks. I know I am asking a lot. I have faith in Excel to deliver but just lack the skills to make it real! I know I can filter the "data" sheet to just show where a particular issue is logged but guess I cannot translate this onto the map sheet then

**NBVC** · 08-07-2012, 11:36 AM

Just to check solely for the RF condition you would need to apply conditional format formula to A2:X45; =VLOOKUP(A$1&"-"&A2,Data,3,0)="RL"

and you would need to do that 7 more times for each sole condition

Then for each possible combination you need to combine VLOOKUPs within an AND(), eg. =AND(VLOOKUP(A$1&"-"&A2,Data,3,0)="RL",VLOOKUP(A$1&"-"&A2,Data,4,0)="D")

thats for RF and D only, you need to do this adding more VLOOKUPs for each combination which could be from 1 only to all 8 checked... that's a lot... just to get all possible combinations of 4 different items, you need 70 different conditions (=COMBIN(8,4))... if you want separate colour for each....