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.
Using Google Sheets Formulas
, you can do these steps in order to achieve what you'd like to do.
Report Data
sheet that would look for Product
in California
.It would look like this:
This is the formula I've used for G1
of the Report Data
sheet:
=QUERY(A2:E, "Select A where E = 'California'")
Something like this:
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: