Avatar of itsmevic
itsmevic
Flag 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.
Microsoft ExcelMicrosoft OfficeSpreadsheets

Avatar of undefined
Last Comment
itsmevic

8/22/2022 - Mon
Steve

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

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

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
itsmevic

ASKER
I could.  Let me see if I can provide you a simplified example.  Should have it posted in a little bit.  Thanks.
itsmevic

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

ASKER
Here is the example file...
ExampleReport1.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve

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
itsmevic

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
Steve

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
itsmevic

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.  : )
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Steve

That should be fine, see file attached.
Detail.txt
itsmevic

ASKER
Sent.  Again, thanks for your help with this.
Steve

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
itsmevic

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

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

ASKER
Absolutely SUPERB!  Thank you for your expertise!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23