Data Validation/Formula

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
I'm hoping someone will be able to help me figure this one out. Ok I have a workbook with two tabs, the first tab "Query" is an Access query that pulls in call data from our phone systems. The second tab "Report" is a userform/report. This will be used by numerous people and has to be as userfriendly as possibly. Because our clients want the data sent this way I don't have a lot of room in changing the report itself.

Let me paint the picture, I have it so the user is able to select the month from a validation list I created in B1, when the month is selected is autopopulates each work day of that month in cells A13:A35. There are several (28 total) reports from different phone lines we have. So I have another validation list in cell B3 that has the name of each report we have. So in theory the user should select the month and report and the information will fill in and they can print that specific report off. The issue is this. I had used the formula =SUMPRODUCT(--(Query!A5:A25004=Report!A13),--(Query!B5:B25004=Aban_08),Query!E5:E25004). I'm trying to find total calls for specific lines for specific dates. The "Report" tab has information from January to current with all 80 different call lines (all named differently such as CDN_5700, CDN_5701). One report such as "Aban_08" actually pulls information from several call lines which is bringing me to my issue, I need one tab for the report, and be able to have the user select the different report, but each report pulls from multiple call line names. Again, I need (for instance) B13 (cell A13 is the date June 2) to pull all calls from the report tab for the date of June 2 with (for example) the names "CDN_5700","CDN_5701","CDN_5702". Does anyone know how I can accomplish this?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I am not sure that this is a data validation question. Sounds like you want something else.

We will probably need an example xls to help. You can post the link to one from a site like media.fire if you like. Trim the file down to just want you need help with and obfuscate sensitive data. Add a note sheet explaining what you need or showing before and after examples.
 
Upvote 0
Ok I have to get help on this so let me try to explain as well as I can, If anyone can this of a good way to approach this I would appreciate it. On the first tab I have a Microsoft Access Query I created. It pulls all of the data for the report I want to create. Column A has the date, Column B is the phone line name, and Column E is the data I will need to grab. Here is what it looks like (this is only 29 rows down):
test.xls
ABCDE
1DateStampApplicationCallsAbandonedCallsAnsweredAbdDelayLe14
22-JunACD_DN_Application010
32-JunADPEmpExcluEng010
42-JunAIGAdmin000
52-JunAIGEnroll000
62-JunAIGMbrSvcApp000
72-JunAIGPHCSApp010
82-JunAIGSales030
92-JunAgelityPharmacy1161
102-JunAmericanProfileApp000
112-JunCCAVSPEngApp5661
122-JunCDN_570052174212
132-JunCDN_57016760
142-JunCDN_57025871
152-JunCDN_5703390
162-JunCDN_5704000
172-JunCDN_5705000
182-JunCDN_57065231
192-JunCDN_5707000
202-JunCDN_57081160
212-JunCDN_5709010
222-JunCDN_5710000
232-JunCDN_5711000
242-JunCDN_57120110
252-JunCDN_5713000
262-JunCDN_57173201
272-JunCDN_57184671
282-JunCDN_5719000
292-JunCDN_5720000
Sheet1


here is the report I want to create, I am showing it in pieces and hopefully someone can help me put it all together:
test.xls
ABCD
2Report:Aban_08
3
4Abandoned14Sec
5
62-Jun#N/A
73-Jun
84-Jun
95-Jun
106-Jun
119-Jun
1210-Jun
1311-Jun
1412-Jun
1513-Jun
1616-Jun
1717-Jun
1818-Jun
1919-Jun
2020-Jun
2123-Jun
2224-Jun
2325-Jun
2426-Jun
2527-Jun
2630-Jun
Report


and here is a sample of some specific reports I need to be able to choose from (the report name is the first cell which is also the names in the validation list in B2, all the names under each each report name is all the phone line names that need to be calculated with this report):
test.xls
ACADAEAF
1Aban_08ADPEmpAban-08AdvCard-08Agelity-08
2ACD_DN_ApplicationADPEmpExcluEngCDN_5703AgelityPharmacy
3ADPEmpExcluEng
4AIGMbrSvcApp
5AgelityPharmacy
6CCAVSPEngApp
7CDN_5701
8CDN_5702
9CDN_5704
10CDN_5705
11CDN_5706
12CDN_5707
13CDN_5708
14CDN_5709
15CDN_5710
16CDN_5711
17CDN_5712
18CDN_5713
19CDN_5716
20CDN_5717
21CDN_5718
22CDN_5719
23CDN_5720
24CDN_5721
25CDN_5722
26CDN_5723
27CDN_5724
28CDN_5725
29CDN_5732
Report


Ok, as you can see from the Access Query report there are alot of different phone lines (78 total). On the Report tab I need to be able to select the Report from the validation list in cell B2. So take for instance I select the report "Aban_08". I need in cell B6 (if I can get the first cell to work I can handle the rest) for it to be able to tell me what the total of June 2 is. Meaning it would have to match A6 with Sheet 1 A2:A25004, then check and match up all the names under "Aban_08" with the names in B2:B25004 and total what is in E2:E25004.

I tried using:
=SUMPRODUCT(--(Sheet1!A2:A21195=Report!A6)--(Sheet1!B2:B1195=$B$2),(Sheet1!E2:E1195))

Bit unfortunately this did not work. If anyone can help me work through this I would appreciate it, I've searched all weekend for other posts to help but not really getting anywhere.
 
Upvote 0
Sounds to me (if all the data you need is linear and in the first tab) like you could do this with pivot tables, at least I would. It seems to me that they would do everything you are asking, the drop downs (or validation lists you are referring to) are built into the pivot and you would be able to slice the data by any single or multiple variable you could think of. Have you thought about using a Pivot Table for this project?

How dyamic is the data, better asked, how often is it updated?
 
Upvote 0
well the report is updated daily and the query is from Jan 1 2008 to Jan 1 2009, and there is actually other data and is on the report, I figured if I could figure out how to get this one column of data in I could get the rest the same way.
 
Upvote 0
I'm trying to format this in a pivot table but not having any luck, I need this to be user-friendly, I see how I can check off which phone lines I want it to count, but I need it to automatically do that for the user, be able to check them off by a report name and not just check all the phone line names. There are a total of 28 different reports that grab from different phone lines.
 
Upvote 0
So, does the user that you are trying to limit by only have certain phone lines that they will need to view? If so, there are some quick ways that you can assign that data to your (Sheet 1) that could make this very user-friendly from a PT point of view.
 
Upvote 0
Well basically, from the Sheet 1 above all the data in Column B is different phone lines. We have to run certain reports (which are all the same report just for different phone lines) for different clients. So one report can be for 3 phone lines and another report can be for 55 phone lines. That is why I am having issues making one report that you can select from a dropdown box which report you want. I've been frying my brain to figure the best way to do this.
 
Upvote 0
Why not add more columns, one for each report that will need to be viewed? Then from the Pivot you could limit by the report and have all the data you need... if more than one line would need to be viewed in more than one report... that would work. Just use an IF/THEN type of statement for each column (or report), leaving the ones that you don't need with a "" or something.

There is a way to get there Pivot Tables, you just may need to think about how you have the data formated a bit more...
 
Upvote 0
I just had a thought, I could also do each report as a query from Access, but can I create a Drop Down box that allows me to select a different Query?
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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
Back
Top