• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using VLOOKUP or MATCH With a Drop Down List?

J

jtocon63

New Member
I have a dashboard with many data sheets and several summary sheets. on the main summary sheet the formulas currently point to a specific cell on some other sheet. What I want to do is add a drop down list to the summary sheet (a period of time, like month or year)and let the user pull in the resulting corresponding measures from that month.

Cell C1 contains the drop down list (months, named exactly as the column headings on other sheets). I then have the measure headings (ex. new patients, charges, adjustments, payments, etc.) in A3, B3, A5, B5 (etc.) and the corresponding data result in A4, B4, A6, B6 (etc.)

My target data is on another sheet where the rows are the measure headings and columns are the month.

I want the user (for example) to be able to select Feb-12 on the summary sheet and have all the correct data pull into A4, B4, A6, B6 (etc.)

VLOOKUP is not helping me by itself.I tried to use INDEX & MATCH as well. I'm assuming VLOOKUP isn't working because the corresponding column changes depending upon the month selected in the drop down?

I know I am probably making it harder than it needs to be.
 
Hi jtocon63,


Welcome to the forums, Kindly upload a sample file, hopefully the issue will be resolved.


Thanks,

Faseeh
 
Sorry, took me a bit to create a sample file of the data and figure out how to share the file.


https://docs.google.com/open?id=0B648I7rbmMF7N3VQQW9TVTlUMjY0WHhMLVc5Z1RpUQ
 
Hi Jtocon63,


There are no. of sheets that are present in this workbook. The dashboard refers to other sheets that are linked to the other thus goes the cycle. In order to make thing work from drop-down, you either need to work from the very database or need to configure the reports that are linked to the dashboard in similar fashion, mean if i select Feb, all other sheets must have data of February and that appears not to be case with this sheet? Is't it so?
 
Faseeh - Yes, there a large number of sheets. I only am concerned right now about the dashboard sheet pulling data from the practice data sheet, not any of the other sheets. I should have deleted them for this sample. I will do that now. Thanks for pointing out my error.
 
Hi jtocon63,


Well, there are still some problem. Can you delete the unnecessary sheets from the uploaded file because when i tried to do so i found that rest of the sheets are also linked to the dashboard! Should i leave those cells unchanged that are linked to other sheets & stick only to the 'Practice Data'?


Secondly few cells like i remember payment that has value something $269000 are hard-coded for two months: March and Feb and there is no space for rest of months? How will you handle that?


Regards,

Faseeh
 
I havent looked at your sample sheet (at work and not supposed to) but have you tried using a pivot table? then the user can use the slicer tool to select the date?


again, i may not be following exactly what you're needing but sounded like a possible solution.
 
Hi Jason,


When we intend to have a dashboard, data has to be organized. Unfortunately that is not the case with this sheet. Lets take example of 'Finance & Trade'! Almost entire sheet is calculated by taking percentage of Feb over march. If i add (somehow) a drop down menu, it will ask you for comparison of lets say April over march, and how you are going to do that because i can't find data of April in your sheet! This is just one example. Actually this sheet has too many tables and is not meant for a dashboard (Plz pardon me but it is what i have concluded). I hope i have explained my self. If data in this sheet had been a bit more organized or it had bee just one database-type sheet from which you had made so many other sheets then a dashboard was a bit more feasible.


Regards,

Faseeh
 
I'll get another sample up...for now all I am trying to do is get a formula so the data in cell A4, A8, A12 changes to the proper month from the practice data sheet and that month is determined by the drop down in C1...forget about the other formulas and sheets for now.

Thanks
 
Hi jtocon63,


Nice to read your feedback!! Lets take these cells one by one..


A4: Traced to B4 on Practise Data & configured.

A8: Shows '='FINANCE & TRENDS'!B22', when i go to this cell, the value is sum of...

[pre]
Code: 
Medical Fees	 $264,483.61
Refunds	         $2,710.59
Clinical Trials	 $5,353.19
Wicher SPH	 $-
Other	         $2,659.35
[/pre]
...so it could be a comparison between only Feb and March. There is no relevant data on 'Practice Data'!


A12: Refers to '='FINANCE & TRENDS'!B7' that is equivalent to B19 and this manually entered!!


... So what remains then, I just told you: A two month comparison. I will certainly help if you provide me with a better example and thanks for feedback! :)


Regards,

Faseeh
 
Faseeh - I can see how my request and spreadsheet could be confusing. Let's try this one more time using a much condensed version. As you can see, what I am really trying to do is build something that will be used going forward as I add historical data to both month and year.


https://docs.google.com/open?id=0B648I7rbmMF7N2RvU0EwQ1BSWUd1NHdVb3VsTTdRZw
 
Hi jtocon63,


Glad to see your feedback, I have downloaded the file and revert soon hopefully with a solution :)


Regards,

Faseeh
 
Hi jtocon63,


I finished up with first blue colored cells and now they are working. What do you want with cells in 'brown' color? Do you want compare two months? Actually i can't access my dropbox from office otherwise would have uploaded the file so please tell me about brown cells and i will upload file by today eve.


Thanks,

Faseeh
 
Hi jtocon63,


I just reached home so here is your file:


http://dl.dropbox.com/u/60644346/SampleII%20Dashboard%202012_Reworked.xlsx


What do you need in 'commented area'? Waiting...


Faseeh
 
Ahhh...OFFSET!!!!! Genius, thanks Faseeh. In the other column I want YTD for whatever period, so if it is March i want Jan-March of the year selected.
 
Nice to see you back jtocon63! See this file for YTD solution:


http://dl.dropbox.com/u/60644346/SampleII%20Dashboard%202012_Reworked_Final%20Workout.xlsx


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top