1

I'm trying to make a simple database that returns a value based on a number of different criteria.

Here is the Search Page they'll use to access the data:

User search page

They choose from one or more criteria in row 2 (text types, up to three tags, or reading level); all other cells will be protected. I want their results to appear in a list from A3 down (I've already set up the sheet to populate the rest of the rows based on the data in the A column).

Here is the Database so far. I want the Search page to draw from column A, based on the criteria in the text types, tags, and/or reading level columns.

Database sheet

Any assistance would be greatly appreciated!

1
  • Welcome, Sean. My significant other teaches literacy in an Australian school, so these values are well known to me. You say They choose from one or more criteria [...]text types, up to three tags, or reading level. Would you please clarify, do you want the search to display the titles that have ANY of the search terms, or only those titles that have ALL of the search terms - in short, "ANY" or "ALL"? It would be helpful if you were to provide an example of a successful outcome based on your sample data.
    – Tedinoz
    Dec 12, 2020 at 23:45

1 Answer 1

0

You want a search screen that allows you to select titles based on certain criteria, including Text Type, 3xTags and Reading Level.

It is likely that your question could have several answers. Please consider this as one possible solution.

Dropdown cells

These need to be built with Data Validation. Since these are displayed in the screenshot of your question, we can assume that you have done these already.

  • Text-Type: Cell C2
  • Tags: Cells D2, E2 and F2
  • Reading Level: G2

Query

Insert this formula in cell A5.
Note, the query assumes that the raw data and the enquiry screen are on different sheets. The raw data is assumed to be on Sheet="data" - edit this sheet name according to your own setup.


=iferror(QUERY({data!A2:H7},"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 
     where 
     (" & IF($C$2="All", "1=1", "Col3 contains '" & $C$2 &"'")& ") 
     and 
     (" & IF($G$2="All", "1=1", "Col7 contains '" & $G$2 &"'")& ") 
     and 
     (" & IF($D$2="All", "1=1", "(Col4 contains '" & $D$2 &"' or Col5 contains '" & $D$2 &"' or Col6 contains '" & $D$2 &"')")& " 
   OR " & IF($E$2="All", "1=1", "(Col4 contains '" & $E$2 &"' or Col5 contains '" & $E$2 &"' or Col6 contains '" & $E$2 &"')")& " 
   OR " & IF($F$2="All", "1=1", "(Col4 contains '" & $F$2 &"' or Col5 contains '" & $F$2 &"' or Col6 contains '" & $F$2 &"')")& "
     )
    ")
,"No titles meet these criteria.")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.