+ Reply to Thread
Results 1 to 16 of 16

Trying to have charts populate depending on drop down

  1. #1
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Trying to have charts populate depending on drop down

    Hi all,

    I am trying to figure this one out. I have a sheet which has a list of drop down of various codes. These codes are all linked to a store. These stores all belong to a certain district. Note i have the mapping all done in the back end. I want to create a chart so when i click on the drop down and select a store, depending on which district its in the relevant stores display in the graph a long with the store i selected.

    For example if i select a store in NY i want all the stores in the NY area to display along with the store select so i can see how it compare to its relevant area.

    how would i go about doing this?

    store cid.PNG

    ranking.PNG

    Thanks!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Trying to have charts populate depending on drop down

    You can probably do this either with a pivot table or overlaying the data with named dynamic ranges. I can't say much more until I see what kind of data you have and how it is organized.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: Trying to have charts populate depending on drop down

    Hi

    I have attached the file with some data. As you can see in the RAW data tab there are multiple distrcits (L) on the main page say if i select dealer X, then i would want the charts on the below row 31-80 to show case the relevant dealers in that district along with the data. Not sure if that makes sense.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Trying to have charts populate depending on drop down

    Here is what I have been able to discern.

    The Center ID is the dealer number. The District will be looked up based on this ID.

    You have 4 blocks of data: VSA Rankings, T&W, GAP and TAC. I assume you want a chart for each.

    There are three metrics you want in each chart: National Average, Regional Average and something I can't make out because the picture is too small.

    I'll make allowances for a variable number of rows, but the columns must remain fixed. Will this work for you?

    I may be making the appropriate block of data into an Excel Table - mainly because tables know how big they are and they always reference the exact amount of data you need. There is no need to overguess the number of rows you need. In fact if you made all the columns contiguous, then you could take advantage of another table feature: they automatically copy formulas down as you add rows.

    Let me know if I'm on the right track or what I have to change.

  5. #5
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: Trying to have charts populate depending on drop down

    Thanks for the help!

    Yes your on the right path. The 4 blocks which contain the VSA, T&W, GAP and TAC are the charts i want built below based off either contracts or I will add a yoy growth rate to display.

    The fixed columns fill work for me! Everything else you said was spot on. Thanks again

    Best

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Trying to have charts populate depending on drop down

    I'll get started, but I need to ask some more questions: is this format (with all the empty columns between the data the way you receive the data? Is this data typed in or copied and pasted from somewhere else.?

  7. #7
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: Trying to have charts populate depending on drop down

    that empty block i was wanting to insert spark lines to show a 12 month trend. thanks!

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Trying to have charts populate depending on drop down

    OK, I think I have it.

    I converted the dealers data to an Excel table and changed the formulas that do lookups and data validation to reference this table. One of the advantages of excel tables is that they know how big they are, so formulas that reference them don't have to change when the number of rows changes.

    I also converted the table where the averages are computed to a table. I added a couple of helper columns that can be hidden. Somehow this table has to be tied to a CID. I did this in column AH. I used lookups in columns AI and AJ. Another advantage of tables is that they copy down formulas automatically. So if you copy/paste data into this table, the last three columns will fill in automatically.

    I took the formulas for averages and moved them to an averages sheet, so that they are all in one spot for charting. I used the AVERAGESIF function to compute the regional and market values.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: Trying to have charts populate depending on drop down

    Hey there thanks for this.

    Right concept but wrong data. So you got it exactly to change depending on drop down but the data that should be in there is not the national, regional or market ranking but rather the other dealers in the same district as them. Essentially if you goto the RAW data tab and goto column L you can see different districts. If i was to select from the drop down a dealer thats in District 3A for example, I would want to see that dealer compared to the other dealers in district 3A in regards to contracts.

    So you can see how a store is doing relative to the same district if that make sense.

    Hope thats not to confusing!

    thanks

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Trying to have charts populate depending on drop down

    What's confusing is the difference in names between what's on the KPI Overview sheet, Raw Data and Dealers.

    So I need a map Cell B5 on the KPI sheet is what column on the Raw Data Sheet and what column on the Dealer's sheet. I need the same information for Cell B8 and cell C8.

    Then I need to know the real names. Cell B8 is labeled "District" on the KPI sheet, but on the Raw Data sheet, "District" contains only two letters and on the dealer sheet there is no district at all.

  11. #11
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: Trying to have charts populate depending on drop down

    I understand

    So B5 on the KPI overview would be column A on the Dealers sheet and column A on the RAW data tab

    B8 would be column C on the dealer tab
    C8 would be column B on the dealer tab

    District from B8 on the overview come from Column L on the RAW data tab. I simply merged the district which is the numbers with a district owners hence the names attached to it. A concatenate of sorts.

    Let me know if this helps. Thanks!

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Trying to have charts populate depending on drop down

    I think this might get it. If it doesn't then walk me through a selection and what the results should look like.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: Trying to have charts populate depending on drop down

    So the charts below should display the other dealers in the same district as well, not just the regional and national averages.

    So lets say district 2B has dealers U,V,X,Y, and Z in its district. So what i would like to happen is if I select dealer X from the drop down then in the charts we should be able to see the X along with U,V,Y, and Z for comparison purpose. Something similar to the below screenshot
    Capture.PNG

    thanks!

  14. #14
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Trying to have charts populate depending on drop down

    I had to resort to VB to set a pivot table filter
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: Trying to have charts populate depending on drop down

    This is exactly what I needed! Just need to figure out how to implement it to my final file now which is a lot larger! So you used a pivot table filter via VB for this? I will need to learn how to do this myself going forward. Thanks!

  16. #16
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Trying to have charts populate depending on drop down

    Pivot table filters can be complex. But if you just want to filter on a single string value you can take a short cut. Used VB to write the value you want to the filter.

    I did some another trick. I have a lot of helper columns on the charting column and these are "overlaid" with named dynamic ranges. In the real world, you may want to extend these formulas down (Cols G:K) to cover data this is likely to be returned by the pivot table. The dynamic ranges will adjust to cover.

    I used the dynamic ranges as the sources for the charts.

    Here are a couple wikis that explain what I did:
    http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
    http://www.utteraccess.com/wiki/Dynamic_Charting

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 16
    Last Post: 01-25-2018, 12:18 PM
  2. Replies: 3
    Last Post: 06-14-2016, 12:43 PM
  3. Auto populate a second sheet depending on drop down result
    By rlacombe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-10-2015, 10:27 AM
  4. [SOLVED] Using a drop down list to populate cells underneath the drop down.
    By jfgay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2013, 04:07 PM
  5. 2 Drop down lists depending on 1 other drop down list.
    By giomath in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-14-2013, 10:48 AM
  6. Replies: 3
    Last Post: 01-31-2013, 06:22 PM
  7. Replies: 8
    Last Post: 09-22-2011, 03:47 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1