+ Reply to Thread
Results 1 to 7 of 7

Select data range to chart based on a set cell value

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    15

    Select data range to chart based on a set cell value

    Hi all. I have run into a problem that I can't get through, but I'm sure someone here will manage it"

    I want make a visual presentation of important data for each month. For example a pie chart of the sales split between Sweden and Denmark this month. (Months in the columns and Countries in the rows. Please see attached file.)

    My problem is that the month is changing every month Now I have a pie chart of January but when it turns to February, i don't want to have to go into "mark data" and change cells. Rather, I would just like to have a cell that I change from "Jan" to "Feb" and the data selection changes.

    How do I do this?

    I hope I made myself clear. If not, please ask!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Select data range to chart based on a set cell value

    Here you go

    https://www.youtube.com/watch?v=lCsvSdk4Tro
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-04-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Select data range to chart based on a set cell value

    Thanks Shareez. That solution definitely does the work, but it is quite some work to set up as well. Is there any other solutions to think of?

    I am thinking about something like if I can write a range in a cell (for example "B3:B28") and then refer to that cell in my data selection? (I have a lot of diagrams and then I can change the data range by just one cell.)

  4. #4
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: Select data range to chart based on a set cell value

    I've attached a suggested solution that might provide some help. I hope you can adapt it to your needs.

    The headers, dates in your original data, are text entered in cells B1:M1. I changed them to be actual Excel serial dates, the first day of each month. The cells are formatted to display only the month.

    I set up a data validation dropdown box in cell B5 for the user to select the month. The allowed values are the date headers, B1:M1. The number format for this validated cell is "mmmm".

    The data to chart is selected by using INDEX+MATCH+MATCH formulas. I matched the row, the country, with the first match subformula, and the second match subformula finds the column, the date.

    The chart title changes with the selected month. The title is constructed in cell B9 with the formula: =TEXT($B$5, "mmmm yyyy") & " Sales"

    To tie the chart title to this formula, select the title text box in the chart. Type an equal sign, =. Then click on the cell (B9, in this case) that you want displayed as the title and press Enter. The results will appear as the chart title, and the formula bar will contain =SuggestedSolution!$B$9. This formula will not work if the sheet name is deleted or omitted.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Select data range to chart based on a set cell value

    That's the best solution I have for you.
    But you can also use Slicers.

    Please find the attached sheet and revert me if you need more help.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: Select data range to chart based on a set cell value

    The solution I suggested could be modified to use the OFFSET function. More people would use OFFSET than would use INDEX+MATCH. Here's one tutorial: http://www.exceldashboardtemplates.c...cel-pie-chart/

    The same website posts a solution using an Excel table: http://www.exceldashboardtemplates.c...han-4-minutes/

  7. #7
    Registered User
    Join Date
    07-04-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Select data range to chart based on a set cell value

    Hi all. Thanks for a lot of great answers, this will definitely do it for me. (I have messed around with several of the options and will see what suits me best in the long run.)

+ 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. Chart: How to select Data Source (range) in an 'intelligent' way?
    By math8 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-13-2014, 11:00 PM
  2. Replies: 4
    Last Post: 06-17-2013, 05:21 AM
  3. Select a range based on a cell value
    By Thug in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-27-2013, 05:09 PM
  4. Replies: 2
    Last Post: 02-01-2013, 02:53 AM
  5. Select chart data based on last cell with a value >""
    By peet335 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-08-2012, 02:12 AM
  6. Select data range based on specific cell
    By cmb80 in forum Excel General
    Replies: 0
    Last Post: 01-22-2010, 06:36 AM
  7. select range based on a cell value
    By modytrane in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-19-2008, 04:31 AM

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