• 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.

Dynamic Dashboard Population

B

bexcelusr

New Member
I'm attempting to have MSA data (referenced in column C of the 'Lookup Sheet' tab) auto populate based on the criteria selected in the drop-downs i have created in rows 36-56 in the template tab. The MSA data would display on the template tab starting in cell D60. Example.) I want all MSA's to display that have population between 108,245-132,300 and have % HH Income $100K-$149K between 7.9%-9.7% and have % of White Population between 65.6%-80.2% and have % of age 20-34 between 16.7%-20.4% and have % of Bachelor Degrees between 11.7%-14.3%

The problem i have is when i click and change a drop down. My list has to react to whatever the criteria is. Example) if I change my population criteria from 'total population' to '% of female population' I need my list to dynamically change and fit to the parameters I outlined in each of the search criteria(s). All data is located on the 'lookup sheet' tab.
 

Attachments

  • sample v1.xlsx
    492.6 KB · Views: 3
Hi ,

With such a small set of data , it is difficult to verify , especially given that there are two levels of lookup involved.

See if this is OK.

Narayan
 

Attachments

  • sample v1.xlsx
    494.9 KB · Views: 13
I think this will work, I wanted to include all my data but the file was too large to upload. My data is wingdings....how do i get back to numbers?
 
It Means that on the 'Lookup Sheet' Tab the data appears as such.


upload_2017-5-4_13-13-59.png


If the solution is as simple as copy and pasting data over this, I will do so. But I wont do so if by copying and pasting, I interfere with your original formula
 
Selecting Arial has solved the text issue. Thank you very much. My last question. I need expand the arrays to include additional data. It looks as though you've labeled the arrays as 'data' and 'msa'. How do I expand the arrays? Thank you for your assistance, this has been very helpful.
 
I'm still having issues pulling data based on my criteria. I've expanded the array and added all of my data. Nothing seems to be pulling. Any ideas?
 

Attachments

  • Copy of Copy of sample v1_New MTM.xlsx
    957.8 KB · Views: 5
Hi ,

The problem is not the formula , though that may be wrong.

The problem is that the criteria are being framed in such a manner that there is no data which satisfies all of them.

To check this out , I switched on the Auto Filter in the Lookup Sheet tab , and introduced Custom Filters for each of the items in D36 , D41 , D46 , D51 and D56 in the Template tab , where the first condition was :

Greater than or equal to the -10% value of that item

and the second condition was :

Less than or equal to the +10% value of that item

For example , for the first item Total Population , the criteria was all those rows which satisfied the following criteria :

Greater than or equal to 572682.60

AND

Less than or equal to 699945.40

Only 15 out of 929 records satisfied these conditions.

Now , add the next item , which is % HH Income 50,000 - 74,999.

When the criteria for this are introduced , only 6 records satisfied both conditions.

Ultimately , when all items are included , you find that there are no records which satisfy all the criteria.

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