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.
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.
ASKER
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.
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.
ASKER
I could. Let me see if I can provide you a simplified example. Should have it posted in a little bit. Thanks.
ASKER
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.
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.
ASKER
Here is the example file...
ExampleReport1.xlsm
ExampleReport1.xlsm
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.
ExampleReport1.xlsm
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.
ExampleReport1.xlsm
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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. : )
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.
Detail.txt
Detail.txt
ASKER
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.
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.
ASKER
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.
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.
ASKER
Absolutely SUPERB! Thank you for your expertise!
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.
http://www.youtube.com/watch?v=7le-m8YRP6M : 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.