Solved

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

Posted on 2014-10-10
4
169 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
4 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 167 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 167 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 166 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now