10-19-2019 08:48 AM
10-19-2019 08:48 AM
I am trying to create a report using vlookup with three dropdowns to filter that dated data. One would be the customer name, second would be the date and final would be project no. So if a Customer is selected from the drop down, and 10/13/2019 and 75686 are chosen for the project# the report would populate with the selected criteria.
If the date was changed to 10/25/2019 the report would change to that data.
I have youtubed the heck out of my scenerio and cannot get the drop downs to sync Help!
10-19-2019 09:24 AM
For a certain Date, Project Name and Customer Name, do you have one unique row of data on your data source?
Try searching on Youtube by "VLOOKUP with multiple criteria." There are many solutions available. The one that fits your case will depend on how you have your data laid out and what you need to extract. You may need to use some techniques that do not involve the VLOOKUP formula.
Let me know if you found something that solves your problem.
10-19-2019 09:51 AM - edited 10-19-2019 09:52 AM
IF there is a possibility of more than one records as per the criteria you select, look for Advanced Filter.
It needs some setup to make it work but once properly setup can be automated as well so that you change the criteria and it would return the different set of records.
If you need help to achieve that, please upload a sample file and I will help to set it up for you.
Another option other than Advanced Filter and the formulas is, you can convert your data into an Excel Table and then insert three slicers for Customer Name, Date and Project# and then you can select the criteria from slicers and the data in your table will be filtered in place and will show you the records that meet the criteria selected and I hope this would be the easiest for you.
10-19-2019 04:59 PM
Here is the template I am working with. On Report tab I want to be able to 1) select customer then 2) select date and then 3) the Project#.
The report auto-completes with the information retrieved from the vlookup.
I can get the customer vlookup to work but not the second and third criteria.
Any help would be appreciated
10-19-2019 05:59 PM
Do you want to populate C3:M3 only if all the three criteria are selected from B1:B3?
If so, please find the attached with all the formulas in the range C3:M3 so that once all the criteria i.e. Customer, Report Date and Project are selected from B1:B3 respectively, the C3:M3 will be populated with the relevant record else they will be blank.
I have inserted the dynamic named ranges for all the fields on the Data tab and I have also tweaked the formulas used in the Report area.
Let me know if this is what you were trying to achieve.
10-20-2019 05:55 PM
@KAB525 Here's an altogether different solution to your problem, one using the seldom used (but quite powerful) database functions within Excel. There's a whole set of functions that begin with the letter D, such things as DSUM, DAVERAGE, DMIN, DMAX..... this uses only the very simple DGET to retrieve the data elements you want from your database . You asked for three criteria, so this is set up with those three possible, but so long as you have a unique Customer name (i.e., only used once) or unique Project Number, you really need enter only that one field. But if you have two rows for the same customer, then you'll need to differentiate it by using a project number. There's a text box on the sheet that gives a little more complete explanation, but feel free to write back if you have questions.
I use a spreadsheet like this, with many many rows, to create invoices for consulting, with separate rows (a variable number of them in any given invoice) for different types of consulting, and with DSUM to total hours by category....... and then factor in a per hour billing rate to arrive at a final invoice. It works like a charm.
by Bwellsys1 on January 18, 2020