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

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?

I get a #REF error for TblCount

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

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.

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

Code:

---

=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.

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.

On it, as you post...

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

Something like this?

Thank you Aflatoon.

Are you sure you want to mark this SOLVED?

Some of your requirements were not satisfied

Quote:

---

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

That was hardly a long formula... ;)

Code:

---

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

---

Was to me :beerchug:

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.

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

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.

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 "~~".

Code:

---

'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?

this 'should' be close

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

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".

Code:

---

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.

Code:

---

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.

Try this version

Quote:

---

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

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?