How to enable data to be filtered based of values in Data valuation cells

[Aussiebear]

In the attached workbook, I am looking for a method to assist in making a decision on treatment option for bee colonies.

Part 1: On Sheet 1, there 4 basic Questions in cells B2, B4, B6 & B8, with respective Answers to be provided in cells C2, C4, C6 & C8. I am requiring a automated response in cell E4 based on being able to use the answers to Questions 1 & 2 by looking up the value in the TblCount on Sheet 2.

Part 2: Also on Sheet 1, I would like the option of displaying (triggered by cell E4 value is "Treat") the various Chemical Products (from TblProducts) somewhere on Sheet 1, providing it meets the conditions determined by Questions 2, 3 & 4.

Is this possible?

[Paul_Hossler]

I get a #REF error for TblCount

Not clear how (Colony Phase) AND (Mite Count) --> Recommendations

[Aussiebear]

Thank you for looking at this issue for me Paul. Not sure why the TblCount went missing but is now available in the latest version.

The first part of this workbook is just about getting the Cell E4 to show either "Monitor" or "Treat". There are 4 phases in a Colony and when compared to the Mite % will determine if the hive needs to be treated or not. I've tried Index/Match and XLookup but get errors. If Cell E4 shows "Monitor" then Questions 3 & 4 don't need to be answered. Greyed out or not visible perhaps. Can anyone show me how to do this correctly please?

The second part is where I'd like to show relevant data somewhere to right of Column E (I'm still thinking about how this might be done), any chemical options that are available to the beekeeper from TblProducts. I had hoped to simply filter certain columns out depending on whether the chemical may be used or not.

For example Question 3 asks if supers are on, and if the answer is "No" then all chemicals may be shown. However if the answer is "Yes" then Chemicals (Apitraz, Apivar, Apistan & Apiguard) should not be shown.

And to add a further kicker to this.... If the answer to Q4 is anything other than "None", then that particular chemical that was previously used may not be shown as an option. We are not allowed to use any chemical in a sequential treatment as a method of slowing resistance to treatments. If you are a beekeeper you will understand the last bit.

[Aussiebear]

Partial success. I've managed to find a solution for the first part by using XLOOKUP in the following format


=XLOOKUP(1,(TblPhase[Phase]=C2)*(TblPhase[Mcount]=C4),TblPhase[Action])


Still need help in working out how to display only those chemicals that are available whether you have supers on or not.

[Aflatoon]

That workbook is very confusing. You have named ranges starting with "Tbl" that aren't tables. TblPhase is one but its source data is missing.

For your second part, it would be easier I think if your products table were transposed - assuming you have 365 and can use FILTER.

[Aussiebear]

On it, as you post...

[Aussiebear]

Right I think this would be more helpful for those wanting to assist.

[Aflatoon]

Something like this?

[Aussiebear]

Thank you Aflatoon.

[Paul_Hossler]

Are you sure you want to mark this SOLVED?

Some of your requirements were not satisfied


1. On opening the workbook, Cells (C2, C4, C6, C8 & C10 need to be blank.



I was thinking of a pure VBA solution (since that's the way my brain works) and my VERY personal style is against long nested WS formulas if I can avoid them

[Aflatoon]

That was hardly a long formula... ;)

[Paul_Hossler]

=IF(C6="~~","",FILTER(ProductsTable,((TRIM(ProductsTable[Treat with Supers on?])<>"No")+(C6="No"))*(ProductsTable[Product name]<>C8)))

Was to me :beerchug:

[Aussiebear]

It was to me too, but that's an age thing I guess. In hindsight I may have jumped the gun a little bit. Seems that I also need to rather than deselect the individual chemical chosen from cell C8, I need to deselect any chemical which has the same mode of action.

The 5 Groups are Bayvarol & Apistan, Formic Pro, Apivar & Apitraz, Apiguard, Api-Bioxal & Aluen Cap.

[Paul_Hossler]

Not sure I understand the last part, but here's a VBA approach

[Aussiebear]

The last part is simply that these groups have the same Mode of Action (How it attacks the varroa mite). Under current legislation we cannot use products with the same MOA sequentially, as they believe this will lead to increased levels of resistance building in the mites. So for example if you were to choose in Cell C8 the Chemical "Bayvarol", you then could not use "Apistan" because it has the same MOA.

Part of the reasoning for the colouring in the charts is to be able to visually see the different MOA groups. The groupings are:
1. Bayvarol & Apistan
2. Formic Pro
3. Apivar & Apitraz
4. ApiGuard
5. Api-Bioxal & Aluen Cap.

[Aussiebear]

Sorry Paul but I've made a couple of changes to the concept based on feed back from a couple of other beekeepers.

1. Have removed the visibility of Headers for Columns & Rows, and removed Gridlines from "Questionaire" sheet

2. Have changed extraneous data from Sheet 1 "Questionaire" to Sheet 2 "Data". Have changed the references in your code, and it appears to be working. Could you review to see if I have done this correctly?

3. Have added a new section into rows 13:17 so reflect "mechanical" options available to the beekeeper for them to consider using. These rows are to remain hidden if rAction value equals "Monitor", but the be unhidden if the rActions value does not equal "Monitor" or "~~".



'2. After selecting answers to Questions 1 & 2, Cell C10 should show a recommendation
Case rMites.Address
If rPhase.Value = None Then GoTo NiceExit
rAction.Value = getAction(rPhase.Value, rMites.Value)
'If rAction = "Monitor" then continue to hide rows 13:17, if rAction = "Monitor Closely" or "Treat A.S.A.P" or "Treat Urgently" then unhide Rows 13:17
Case rAction.Address
Select Case rAction.Value
Case "Monitor", "~~"
Me.Rows("14:17").Hidden = True
Case "Monitor Closely", "Treat", "Treat A.S.A.P.", "Treat Urgently"
Me.Rows("14:17").Hidden = False
End Select

I tried to follow your example but I think my efforts are wrong.

4. Have shifted the "Chemical Options section down the "Questionaire" sheet and changed the references in the code. Not sure if this is correct. Could you check please?

[Paul_Hossler]

this 'should' be close

I marked changes with <<<< so check that I'm understanding

[Aussiebear]

For some reason the logic is not allowing the rChemicals to be shown if aryActions value is either "Treat", Treat A.S.A.P." or "Treat Urgently".



Case rAction.Address
Select Case rAction.Value
Case "Monitor"
rOptions.Hidden = True
Case "Monitor Closely", "Treat", "Treat A.S.A.P.", "Treat Urgently"
rOptions.Hidden = False
rChemicals.Hidden = False. <--- Added this but it didn't have any effect
End Select

I changed the following to false because if cell C6 value is Yes then they need to be shown.


Case "Yes" ' <<<< Changed these to False
Me.Rows("21").Hidden = False
Me.Rows("23").Hidden = False
Me.Rows("27:28").Hidden = False
End Select

I'm obviously looking at the problem with one or more eyes closed.

[Paul_Hossler]

Try this version


For some reason the logic is not allowing the rChemicals to be shown if aryActions value is either "Treat", Treat A.S.A.P." or "Treat Urgently".

I didn't understand the original requirement

[Aussiebear]

Very nice touch Paul, however the logic still seems a little astray.

Opening the workbook is perfect. I've changed a little bit of the code so that if a User selects an rMites value to enables the showing of either or both tables, and then changes the rMites value back to a lower value then it rehides the relevant tables.

An issue that I've picked up on is if one selects "Yes" or "No" for Question 3. it overrides the selection for Question 2. For example if a User selects say Dormant brood, then say 2% it correctly shows both tables. However if you then answer Question 3 as "Yes" it doesn't hide the chemicals ApiStan, Apiguard, Apivar & Apitraz (Rows 23, 25:27). Is there way to get around this? Do I need to link the rMites and rSupers in a multiple Case Select event?

[Paul_Hossler]

I'm have some difficulty following your business rules (no background in beekeeping), but I this this captures all of them

[Aussiebear]

Thank you Paul. I think this should satisfy them. Your patience with the "business rules" is remarkable.

The current Australian Honeybee Council, has been putting out a number of advisory notes which is a little fluid given that we haven't been in this situation before with Varroa Mites.


Thank you also to Aflatoon for the early non VBA method.

[Paul_Hossler]

fun project

i learned a little bit about bees

i ended up restructuring the code and made it more modular so that you can keep up with new requirements

[Aussiebear]

Your efforts are very much appreciated in this matter.