Vlookup with 2 pick lists and data to be displayed in a chart

DarrenK

New Member
Joined
Aug 5, 2017
Messages
33
Not certain how to ask for it. But I'm looking to setup a spreadsheet so that an employee name is chosen from a data validation list (in B7) along with a particular metric header (in C7) - for instance, 'Break Time' is one of the items in the metric list. Once those 2 items are chosen, I have other worksheets named by their date (11-11, 11-12, 11-13, etc). I would like to vlookup the data in the appropriate column (in this case, 'Break Time' - which happens to be column S) within the appropriate worksheet. And then display it along Row 3 so that the latest 10 days worth of values will display using a revolving date.

For example: (B7) John Smith / (C7) Lunch Time is chosen. D3 will display the 10th oldest date lunch time taken. And so on and so on until D3:M3 have data from the latest 10 business days. Then, the data from D3:M3 will be used to display a chart in Row 10. Probably either a line/bar graph. Either is fine. And then the chart would automatically update if a different agent name or metric title was chosen.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

DarrenK

New Member
Joined
Aug 5, 2017
Messages
33
=VLOOKUP(B7,'11-18'!B:V,MATCH(C7,'11-18'!1:1,0),TRUE)

OK So this is what I have so far and it's not working accurately. B7 is the data validation list of reps. '11-18' is the particular sheet I'm pulling data from. B:V is the range within '11-18' that I am searching for rep name (B actually is the column in which the reps' names appear.

For my MATCH formula, C7 is the cell next to the rep name which shows the metric itself (Break, Lunch, etc). I am searching for it within sheet '11-18' row 1 which are the headers. It doesn't appear to be pulling up the correct information. It is retrieving data but from the wrong columns.

Also, is there a way to add something in the formula so that it auto cycles the last 10 business days?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top