Solved

Excel report generation based on drop down list selection

Posted on 2015-01-30
18
458 Views
Last Modified: 2016-02-11
Hello,

I would like to create 2 reports on one worksheet based on data from another worksheet and the selection of a drop down box.  I can easily create these manually from the raw data but I need this somewhat automated for someone else to use.  They need to be able to choose an option from the drop down menu and the report is generated based on the selection criteria.

For the "Attendee Training Classes" report on the Dashboard_and_Data Entry worksheet, I would like to:
1. Choose an attendee name from the drop down list (already created using Name Manager)
2. Pull the Course Name and Date Attended data from the POST_Data worksheet based on the selection from the drop down menu
3. Report a scrolling list of the results in the "Attendee Training Classes" report area (sorted by date with the most recent at top)

For the "Training Classes by Date" I would like to do the same as above but show Attendee name and Date Attended results for the Course Name chosen in the drop down.

Your help is much appreciated.
Test-Data.xlsm
0
Comment
Question by:Michelle M
  • 10
  • 8
18 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40581573
I have been working on your workbook for almost 2 hours and something I never saw in my life before !!! the sheet
DashBoard_and_Data Entry

Although exist physically in the workbook, in fact it does not exist. As when you go to VBA it does not show at all. How did you create it as it is behaving very curiously first it was showing formulas and not results then I had to change setting in Excel options now when a formula changes a value it does not recalculate automatically although the setting is set to Automatic Calculations.

My estimation is that this sheet got corrupted somehow and we need to delete it and re-construct it.

But will first wait for your comment as to how you created it and if something special hiding this sheet.

gowflow
0
 

Author Comment

by:Michelle M
ID: 40581591
Well, I had a full working spreadsheet including a GUI I made for data entry but I deleted it and modified the data to take out confidential data.  Perhaps something happened when I made those modifications.  I will start over from the working one and make minimal changes.  Thanks for your time spent.
0
 

Author Comment

by:Michelle M
ID: 40581611
I have recreated the test worksheet.  This time without the Form I had before that was probably causing the issues after I made the modifications.  Thanks again for your help!
Test-Data-2.xlsx
0
 

Author Comment

by:Michelle M
ID: 40581820
I have made some progress but still getting some errors.  I added the drop down list straight to a cell so I could easily reference it without having to use a form control.  I have created named ranges for the data and am telling the cells to provide other data (course name, date attended, etc) based on the value chosen in the drop down list.  It is working for 1 of the cells but not the other 4 even though when I look at the calculation being evaluated, it looks correct (i.e. is pointing to the correct cell of data on the data worksheet that it should) but it's still giving me a "#REF!" error.  

After this is working, I need to somehow show all the corresponding data (multiple rows) associated with the choice in the drop down.  Since the number of rows of data reported will vary based on the selection, I will need this to be dynamic but I have no idea how to do this.

I have attached an updated spreadsheet.

Thank you.
Test-Data-2.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40581837
Let's agree on one thing here !!!

We cannot be 2 working on the same thing. Also cannot start dissecting what you are doing and answering your issues.

I will answer the main question if you are interested in getting an answer. The issue that you require is much more complex than a simple MATCH / INDEX or VLOOKUP.

I already have the first part (Attendee Training Classes) Covered but need your clarification on the second part as it is not very clear to me

For Training Classes by Date:

1) Is it linked to the results found in the first part or totally independent Like if you choose an Attendee in the first part you want it reflected in the second part or it is a different search ?

2) In what sheet shall we look for results in
Dropdown_Data_Fields

or

POST_Data

Waiting for your answer and confirmation that you will just HOLD your horses on trials and wait for my solution then I will be glad to answer your questions (without it being a course or training !)

gowflow
0
 

Author Comment

by:Michelle M
ID: 40581848
Wow, sorry to have offended.  I thought it would be helpful if I actually worked on my problem and then posted progress.  My mistake being a first time poster here.  To answer your question:
Training classes by date is independent of the other report (Attendee training classes).  Both reports rely on data from the POST_Data worksheet.
Thanks.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40581881
ok fine no offence don't worry. Just to make things clear. As your are first timer here you will very quickly notice that this site is highly professional and you will get answers you cannot usually get on other sites and this due to the high expertise of people giving solutions reason why when you ask a question better to wait for answers and not try to find it yourself (this should be done prior to asking the question and not while asking it)

Will close the parhentisi here to back to our issue.

I have covered the first part but still working on getting your data SORTED by latest date which I am having a challenge as you can see you have created Name Ranges attributed to Sheet Dropdown_Data_Fields but as we are looking in Sheet POST_Data we cannot use these Named Ranges reason why we need to use the real location of the data.

To make our life easier would you mind is we Sort the Sheet POST_Data (I don't like to resort to such alternatives but as the formula is complex let's see if we can get around it easily then will break our head to find the sorting regardless if sorted or not.

Will wait to know if ok to have POST_Data sorted this would mean each and every time you add data to it you need to make sure you first sort it descending by date prior to using the dashboard or else the results will not be sorted descending by date.

Any problem with that ?
gowflow
0
 

Author Comment

by:Michelle M
ID: 40581898
Understood.

The sort function is pretty important here because one of the main purposes of the report is to see the most recent training (and by whom) so that a decision can be made regarding priority of training dollars spent and to whom.  If it is done in two parts, like a macro button that a user could press that sorts the data on the report after it is populated then I think that would be OK.

Thanks!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40581915
OK I don't giveup easily, don't worry I will find you a neat solution I have sorted the sheet manually just for now to evaluate and try and see if this is what your looking for and I will continue to look for a solution for the sort.

Pls advise your feedback if possible quick as we are surely in different time zones and here close to 2AM and need to get some rest as kids tomorrow dragging me on the ski slopes early !!

PS
You will notice that the formula that drive all this are Array formulas (between { ... } ) this means that if you just edit a formula you will need to PRESS
CTRL SHIFT ENTER so it put the brackets again or else you will never get the correct values of the formulas.

gowflow
Test-Data-V01.xlsm
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Michelle M
ID: 40581953
Wow!  I am impressed.  This is exactly what I need (with the sort function of course).  Thank you for the time you have spent on this so far.
0
 

Author Comment

by:Michelle M
ID: 40581954
One more question.  Did you use VBA for this?  I'm just trying to follow what you did so I know how to incorporate into the spreadsheet with the real data.

Nevermind - it looks like you used functions embedded which will be easier for me to work with.  

I see that you have specified the range for the data such as $A$1:$A$100 to cover future inputs.  Could I use something like OFFSET(POST_Data!$A$2,0,0,COUNTA($A:$A),1) to make it dynamic?

Thanks again for your expertise!
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40582203
ok tks your suggestion. I have to admit that I am not a fan of formulas I am more a VBA guy but here wanted to pull a challenge and I guess was good as learned a lot from this.

Here is what I finally got:

1) I created 3 Named Ranges to make the formula much easier to read and also to help your integration better
POSTTable
=OFFSET(POST_Data!$A$2,0,0,COUNTA(POST_Data!$A:$A)-1,4)

POSTAttendee
=OFFSET(POST_Data!$C$2,0,0,COUNTA(POST_Data!$C:$C)-1,1)

POSTCourse
=OFFSET(POST_Data!$A$2,0,0,COUNTA(POST_Data!$A:$A)-1,1)

2) The Array Formulas have been adjusted to reflect these Named Ranges.

3) For the sorting I resorted to VBA I created 2 Sub each one is linked to a combo. I linked the Attendee Name to following sub

Sub SortPOSTDATACourses()
Dim WS As Worksheet

Set WS = Sheets("POST_Data")

WS.UsedRange.Sort Key1:=WS.Range("D1"), order1:=xlDescending, Key2:=WS.Range("A1"), order2:=xlAscending, Header:=xlYes

End Sub

Open in new window


and linked the Course Combo to following Sub

Sub SortPOSTDATAAttendee()
Dim WS As Worksheet

Set WS = Sheets("POST_Data")

WS.UsedRange.Sort Key1:=WS.Range("D1"), order1:=xlDescending, Key2:=WS.Range("C1"), order2:=xlAscending, Header:=xlYes

End Sub

Open in new window


Both Sub will Sort the POST_Data sheet by Date Descending and then respectively by Attendee/Course Ascending as second Key. This way if you have say for a same day 10 Attendee for the same Course they will be listed in that same day alphabetically. and vice versa for the Course.

4) Last but not Least when you want to Integrate into your production workbook you need to be very careful to the following:
Underneith Each combo you will see that there are values in C7, D7 and I7,J7 these are used in the Array formula so if you change their location in your production workbook you will need to change the array formulas that point to them.

Also if you right click on the Combo and choose Format Control you will see the C7,I7 at the Link cell which is the cell you need to set if you change it in your production workbook. I took the cell next to it just for convenience as it is hidden behind the combo

In D7,J7 you have the following formula:
D7   =INDEX(AttendeeNames,$C$7,0)
J7     =INDEX(CourseNames,$I$7,0)

5) One Last point re the ArrayFormulas if and when you need to change a formula always change the first one ie on row 10 and once done while still in edit press CTRL SHIFT ENTER so it convert it to Array with brackets then you drag it downward till row 22 in this case. Then you move to next column etc...

I think with all this you should be set.

Pls verify this version and let me know if any comment.
gowflow
Test-Data-V02.xlsm
0
 

Author Closing Comment

by:Michelle M
ID: 40582532
The result is exactly what I needed.  I know goflow spent a lot of time on this as it was a complicated problem.  I am beyond appreciative.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40582577
Thank you very much for your appreciation. Pls feel free to post a link in here for any other question you may need help with I will keep this question monitored.
gowflow
0
 

Author Comment

by:Michelle M
ID: 40582634
I have a follow up question about the conversion into my production workbook.  I enter the first formula and enter using CTRL+SHIFT_ENTER and get the curly brackets as you say and the correct value pops up, however when I go to drag this down to the bottom of my report it shows the same value as the first cell of the reported value (row 10) even though the Row value increments as it should and it maintains its array properties (curly brackets).  Is there some trick I am missing?  

Also, it does not seem to change the report values when I choose a new attendee in the drop down and I have verified that I am referencing the correct cell link.

Thank you!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40582834
Well I told you first that you had a problem in the worksheet you posted I created a total new one as I feel it was corrupted and formula were not calculating.

I suggest you take the Dashbord worksheet that I posted and put it in your production workbook and then change all values and formulas to fit and make sure you create the range names and all what I pointed to before and see.

If you already posted a question pls put the link in here.
gowflow
0
 

Author Comment

by:Michelle M
ID: 40582960
Hello again.  That is just what I decided to do.  I was avoiding it at first because I had some forms and modules tied to my original but I'm working on pulling that into your version now and that is going OK.  I just have some formatting to clean up but that is all that is left.  You really saved me.  Thank you very much.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40583651
Your welcome and as already said pls feel free to post a link in here for any question you may need help with I will keep this question monitored.

I usually do this for the question that I feel work was nicely done and users nice to keep providing assistance to !!! :)

gowflow
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now