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

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
onaled777Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
onaled777Author Commented:
Thanks or your response, looking @ it I would have to use a combination of the  CHOOSE and Vlookup functions.
0
Glenn RayExcel VBA DeveloperCommented:
@ 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
byronwallCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.