Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 24

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

  1. #1
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location

    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?
    Attached Files Attached Files
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,787
    Location
    I get a #REF error for TblCount

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

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    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.
    Attached Files Attached Files
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    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.
    Be as you wish to seem

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    On it, as you post...
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    Right I think this would be more helpful for those wanting to assist.
    Attached Files Attached Files
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    Something like this?
    Attached Files Attached Files
    Be as you wish to seem

  9. #9
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    Thank you Aflatoon.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,787
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    That was hardly a long formula...
    Be as you wish to seem

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,787
    Location
    =IF(C6="~~","",FILTER(ProductsTable,((TRIM(ProductsTable[Treat with Supers on?])<>"No")+(C6="No"))*(ProductsTable[Product name]<>C8)))
    Was to me
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  13. #13
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  14. #14
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,787
    Location
    Not sure I understand the last part, but here's a VBA approach
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  16. #16
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    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?
    Attached Files Attached Files
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  17. #17
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,787
    Location
    this 'should' be close

    I marked changes with <<<< so check that I'm understanding
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  18. #18
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  19. #19
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,787
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  20. #20
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,234
    Location
    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?
    Attached Files Attached Files
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •