Multiple chart combined into one

[Aussiebear]

Rather than visually clog up a worksheet with multiple charts, is it possible for one chart to reflect different sets of data depending on the selection made from a Data Validation cell?

In the attached workbook "Varroa 2.xlsx" Im trying to show the different temperature bands that each of the chemicals may be safely used in, compared to the actual day by day temperatures recorded. In these charts provided (one for Formic Pro, the other ApiGuard), they share the same common Dates and Actual Max & Min temps, but differ with regards to the safe temp bands. Formic Pro uses Columns D:E whilst APiGuard uses F:G.

In the end there may be 6 or 7 Chemicals with different safe operating bands, so rather than 6 or 7 charts can it be combined into one super chart depending on the selection?

[June7]

Certainly. And might not even need VBA. Review this tutorial https://www.automateexcel.com/charts/interactive-dynamic/

[Aussiebear]

This is running on a Mac so i'm limited somewhat compared to Microsoft systems. However its very informative the link you provided. I must admit I was starting to think I had the guru's stumped on this one.

[georgiboy]

Attached is a method using the function CHOOSECOLS

I have:
converted your range A:G into a table object
Put the columns to choose in column J
Wrote a small formula in cell AD2

[Aussiebear]

Not quite what I'm chasing Georgiboy. If possible I'd prefer to select the chemical and it adjusts the secondary axes.

[georgiboy]

I couldn't see your chart on the original attachment as one of the axes returned a #REF error ad the chart was blank.

On what I offered if you select Formic Pro, the chart shows the data for A:E, if you select ApiGuard then it shows the data for A:C and F:G

[Aussiebear]

Yes, that was my mistake. Please find attached "Varroa 3". Here's what I'm thinking. There is a data validation cell at "I2", while it currently only exhibits two choices now, later on it may have 6 or more. With each different value the secondary axes data should change. That is, the safe temp range as indicated by the "Apricot" colour should be replaced by the one defined by its values in the relevant columns.

[georgiboy]

Here is another way of doing it, a simple method, you would just create a little table that is currently behind the chart/ graph. The title of the chart/ graph will also update.

[Aussiebear]

Well gosh darn it Gerogiboy.... I don't know what to say. Did I mention that this is meant to run on a Mac?

[georgiboy]

Does that last attachment not run on MAC?

[Aussiebear]

Does now.... can't explain it. When I downloaded it the first time it showed functions that dont work on a Mac (scroll bars, drop boxes etc), and the sheet was simply dead in the water. Saved it my desktop and still nothing. This time clicked on the Data validation cell and up pops a Group box which wont go away. Data validation works and single chart changes as it should. Going to close down the system, go to bed and hopefully all is well in the morning.

Thank you Georgiboy for your assistance.

[georgiboy]

Here is a file with all of the bits added from the different threads that contain the same spreadsheet:
Select date ranges (more can be added)
Multiple chemicals on one chart (More can be added)
Highlight + or - and editable variance of degrees

No VBA and no Power Query in the file

[Aussiebear]

Excellent work Georgiboy. Thank you