Dynamic chart with changing dates and N/A

Hello all,

New member and first-time poster so bear with me. please.
I have a fairly large piece of training data. The end goal I have is to create a dynamic chart where I could insert two dates in cells for the corresponding time frame I’d like to investigate and then choose with a ticked box what metric to see.
There a log date corresponding to exercise done on that day. Usually there are many exercises per day, so the date column isn’t consistent.

Now the information I’d like to see in the chart is in many columns but I’m trying to get one thing to work first and then expand…
Let’s say I want a view of my development for my Bench estimated one rep max or e1RM (column DE). The e1RM column is a formula as to filter out the rest of the movements per week.
What would be the best way to make a chart where I could with two cell inputs create the range I want to see for the development of my e1RM?
I though a dynamic chart would be the answer and stumbled upon:

Youtube:
Excel Dynamic Chart Range based on Cell (drop-down) Value for dashboards

but when I get to making the index function for the e1RM column I get #N/A as a result… it doesn’t help if I change the formula in column DE N/A=>”” since it seems that the index function doesn’t like functions.
What should I do? How should I change my chart to be able to do this? Is my thinking completely wrong and I should be doing this a different way?
I apologize for the long post, but I didn’t know how to explain it any shorter. Any help is highly appreciated.

Pascal

Hi and welcome
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hello,

OK, hopefully I can make my goals a little bit clearer this time.

I have a lot of data from many months and years. I'd like to have have chart which gets its X-value from two cells which are dates so I can see the progress within a specific time frame.
The progress could be e.g. column AU (e1RM).

The main goal being one chart which has all e1RMs (=AU, BC, BK, BS) which I can disable and able with tick boxes so I can see one or multiple e1RMs in one chart within a specific time frame.

BR
Pascal

Oh man! Sorry. I mended it now...

Whoa... Have to do some pivot table learning. Thank you I'll try this!

Attachment 618665

This is great!!

Now that I got it going...

1) Can I somehow get into the same chart multiple moves? If I try to just add from the slicer it for some reason doesn't show all data?

2) Can I have many sum values which I can choose from? They do not have to be active the same time... E.g. Tonnage and Volume... I can of course go into the pivot table fields and change them manually but if there's a way to do it cooler?

Thank You!

PS
Spreadsheeting is a legitimate hobby!!

... oh yes... one thing to add...

I had some data between those columns in the example. For some reason the pivot table didn't work until I put them aside.

"The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

Any idea what this means? These were coulumns with calculations. Some where grouped and merged but I tried testing different variations but couldn't come up with why some columns made an error.

Hi JeteMc,

Thank you for your answers. I think the attachment was just a picture from the PivotTable error... no biggie.

1) OK. Noticed that the date slicer affects the Move slicer. Was hoping for a workaround... perhaps possible with VBA but that's where I draw the line when it comes to excel...

2) Jupjup... I can get Volume and e1RM to show on the same chart which is great. Was maybe hoping to put a slicer sort of thing to the Values so I can more easily choose them. Oh, well you cannot have everything.

Thank you for all your help!

BR
Pascal