Forum Discussion
Vlookup help With three indirect drop downs
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.
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
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.
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.
YES!!!! Thank you kind stranger - that is exactly what I wanted to achieve. Thank you so much
I will certainly be back = )
kab5251 wrote:YES!!!! Thank you kind stranger - that is exactly what I wanted to achieve. Thank you so much
I will certainly be back = )
You're welcome! Glad I could help.
