• 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

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

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