?
Solved

How do I make a chart based on multiple radio buttons.

Posted on 2014-10-10
4
Medium Priority
?
178 Views
Last Modified: 2014-11-04
I need to make the chart of the attached spreadsheet dynamic based on the 2 different sent of radio buttons.  I have been able to figure out how to make chart based on Tab M. What I need is for when A, J,M ect are selected the chart will changed based on the respective tab. Also need to take the Mid-month and end of month options into consideration. I also have a counter that changes based on number of months.  The chart is on the summary tab.
ExpertE.xlsx
0
Comment
Question by:onaled777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 668 total points
ID: 40373035
That's a lot of requests.

In summary, my suggestion is that you have a series of spreadsheets like M1 for A, J, O, and then have another spreadsheet which draws the relevant one based on the radio button.

You have already linked M1!Q6 to the radio button, so you count have a CHOOSE function to draw in the relevant values.
0
 

Author Comment

by:onaled777
ID: 40373281
Thanks or your response, looking @ it I would have to use a combination of the  CHOOSE and Vlookup functions.
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 668 total points
ID: 40373485
@ onaled777:  I'm still reviewing the functionality - and I think your requests are possible, but I first wanted to say that is may be one of the best non-VBA dashboards I've seen.  Really great use of dynamic range names to change display options.

To solve your first question - changing the sheet source (A,J,O,M,N,P) - I'll see if the "M1" sheet can be generalized to see the selected source sheet.  If not, individual sheets may need to be created.

-Glenn
0
 
LVL 3

Assisted Solution

by:byronwall
byronwall earned 664 total points
ID: 40414713
Saw this was abandoned, but it should be possible if you are still interested.

It will be a bear to update.  The trick is to use the INDIRECT function in all of the spots were you are explicitly referencing sheet "M".  INDIRECT turns text into an Excel reference for formulas to consume.

You need to first get the sheet name for the radio values.  Fortunately, you are using 1-6 goes to A-P.  You can create a lookup table to get from number to letter:

1      A
2      J
3      O
4      M
5      N
6      P

Then you need to take a singe cell as the "master" for which sheet to use. Do a simple VLOOKUP into the table above with the value of M1!Q6 which is the radio selection.  Put this value in "master".

From there, you can concatenate this sheet name into the formulas were M! appears.  An example of the change for sheet M1!B3 is:

=+M!C$13

Open in new window


becomes

=INDIRECT(master&"!C$13")

Open in new window


Note that I removed the M and replaced it with the new lookup value called "master" which holds a sheet name.  From there you have to make the rest of it a string in order for INDIRECT to work.  If you change all of your data cells to this style, it should work. I'm not going to test it, but it does pull in the new value based on the radio selection for that one cell.

Having said all that, this will create a spreadsheet that is impossible to maintain and vulnerable to all sorts of issues because the cell references are now strings instead of proper references.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question