Solved

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

Posted on 2014-10-10
4
176 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 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

734 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