Link to home
Start Free TrialLog in
Avatar of itsmevic
itsmevicFlag for United States of America

asked on

Excel 2010: Drop-down Lists Not Populating in Charts

As the Title says, my drop-down lists are not populating into my charts.

I have five worksheets, in my work book.  A Summary, High, Med, Low and Enter Keyword worksheets that comprise my entire workbook.  My worksheets i.e. High, Medium and  Low all have drop down lists.  My last worksheet enables the user to enter in "keywords into a Hidden worksheet that acts as sort of a database if you will of these keywords.  The whole purpose of this is so the user add their own keywords to the drop-list for the High, Medium and Low worksheets (It populates those worksheets simultaneously with the new keyword in the drop-down list.)

The problem...

I noticed on my Summary worksheet which is nothing more than my Dashboard that contains several charts based off of the data in the three worksheets (High, Med and Low)that some of the categories (keywords) are not populating like they should in my charts on my Summary page.  They will only partially populate and I can figure out why.  I have to keep deleting the charts and the worksheet assigned to that chart and rebuilding the chart and worksheet in order for it to capture the items I'd like it to capture in the drop-down list.  I'm curious as to why the charts aren't reflecting some of the keywords in the drop-down list?  Any help is GREAT APPRECIATED as I've been trying to work this delima for days now with no resolve.
Avatar of Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Without seeing a copy of the offending workbook I can only guess:

1) Try using dynamic ranges for your charts using =Offset() for the chart ranges with CountA in there to shrink and grow your chart data. : Youtube Chart Dynamic Offset Ranges

2) Try converting your data ranges (lists and data) to a Table and then reference the table rather than cell references.
Table range uses the Table reference rather than the offset method to create dynamic range.

I am guessing here, but one of these should fix your issue.
Avatar of itsmevic


I converted my keywords on my keword worksheet to a table, by simply selecting the range then clicking on the "Table" button in the ribbon.  This appeared to change them into a blue/white/blue/white type of pattern which I assume is indicitive to a table.  I'm hoping it will capture all off the keywords and reflect them in the drop-down list now.  Need to test it.
You would then need to 'link' any tables to the required portions.
Also re-calling any useage from those tables too.
If you could post a parred down version of the file could have a look and provide viable example.
I could.  Let me see if I can provide you a simplified example.  Should have it posted in a little bit.  Thanks.
Hi Barman,

    I just finished putting together an exact replica of my workbook (well not exact but about 98%, it took a wee-bit to do, I apologize for the delay)

    On my master workbook, I've converted everything to tables just as you recommended; the example I'll post a little later on reflects tables as well.

: )

Issue 1:

When entering keywords in on the "Keyword Entry" worksheet it will populate the "Keywords-Hidden" worksheet fine, but doesn't update the drop-down lists on the "High, Medium and Low" worksheets.

Issue 2:

Charts do not dynamically populate with some of the keywords that are chosen via the drop-down lists.

I think that's about it.  Just two issues really that have plagued me while working on this since the beginning.   Again, thank you for your help.
Here is the example file...
OK, I have converted the Keywords to a table and referenced it in Formula>Named Ranges.
I have then pointed the validation rule at the named range "KeyWords".
This will make sure your validation list grows with the table.

Also I have combined all data from the three tabs into one "data table" tab
It is best to have data in one table rather than many.
This one table can then be used to create the graphs (have done first one for you as example)
I am sure you can see what the first graph does and replicate it for the others.
Then just hit refresh all after a table update to update the graphs.
Have also added a little bit extra to the summary sheet.

This should give you a good idea for a direction to take which may be best in the long run.
This will enable you (in time) to change the data to a Database table and then set up a simple front-end to back-end system.

If you need further info or have to have the multiple tabs, let me know and we can work on it. But I assure you that a single table will better suit your needs in future. If adding data to the table requires a 'form' there are plenty of experts here to help.

Anyway, this should get you moving forward again.
That is a fabulous example, thank you for providing that.  I was talking to a co-worker and although I like your approach better (1 table to capture ALL data), the multiple tab approach ie. "Summary", "High", "Medium", "Low" and "Keyword Entry" seems to work better for us because it's cleaner and wouldn't be as busy as having ONE table.  Hopefully that makes sense.
Avatar of Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Barman,

   Is it possible to send you the completed spreadsheet to your email so you can review? I would rather the finish product be for your eyes only.  I feel confident once you see it in this cleaned up version you'll be able to spot any last minute changes and we can put this puppy to bed once and for all.  : )
That should be fine, see file attached.
Sent.  Again, thanks for your help with this.
OK, are you working towards a single data sheet with the three Pivot sheets for the charts?
Or are you still after the three seperate tabs with multiple tables on them?

I can only advise that a single data sheet is far better for the future.
But the choice is yours.
After further consideration, your suggestion with the one table is better so we'll go with that with three pivot sheets for the charts.
I have done it (mostly) a couple of chart titles to modify is all.

I hope it meets your needs.

You should notice a lot less hidden sheets (one now).
This will help with the visibility and function of the workbook.
Updating via Refresh all will be all that is required.
Absolutely SUPERB!  Thank you for your expertise!