Link to home
Start Free TrialLog in
Avatar of Michelle M
Michelle M

asked on

Excel report generation based on drop down list selection

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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

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
Avatar of Michelle M
Michelle M

ASKER

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.
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
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
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
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.
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
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!
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
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.
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!
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
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!
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
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.
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