Solved

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

635 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