Search code examples
google-sheetsdrop-down-menudropdownspreadsheet

Pull records from a report and show them in drop down menu

I was asked to open this question again using an example spreadsheet of what I am aiming to achieve.

Here it is: Exercise

I have already used SUMIFs and COUNTIFs in my exercise sheet as I have no issues getting data from my master report (whether that's a sum or count of a particular value).

However, I was wondering if I can take it to a next level an pull the actual records in a drop down menu and then based on what I choose in the drop down menue, other fields fill out automatically by pulling the relevant data from the sheet. It's important to note that my master report updates every 4 hours via the Salesforce connector. So the drop down menu would need to be flexible and pull in only that what can be found for the particular search value.

Please check out the exercise, I think it should be easier to understand what I mean.

Solution

  • RECOMMENDED SOLUTION

    Using Google Sheets Formulas, you can do these steps in order to achieve what you'd like to do.

    1. Set up a helper column in the Report Data sheet that would look for Product in California.

    It would look like this:

    STEP1

    This is the formula I've used for G1 of the Report Data sheet:

    =QUERY(A2:E, "Select A where E = 'California'")
    1. Create a data validation dropdown that's pointed to the helper column you've created.

    Something like this:

    STEP2

    In cell B14:

    ='Report Data'!$G$1:$G

    To get the ID, Price, and Date Sold of what you'll select in the dropdown of B14, you can put this formula on C14:

    =IFERROR({VLOOKUP(B14, 'Report Data'!A2:D, 2), VLOOKUP(B14, 'Report Data'!A2:D, 3), VLOOKUP(B14, 'Report Data'!A2:D, 4)}, "")

    In the dropdown, selecting an option looks like this:

    OUTPUT

    REFERENCES