Scatter Plot Data Copy

Question

I have multiple sheets open under one workbook. Each sheet has different data, but all of the relevant totals that I would like to use for a scatter plot are in the same range. For example, I have the years in N1, P1, R1, T1, V1, X1, Z1, AB1, AD1, AF1, (x values). and my totals alternated, so on one graph the y values will be quantity's, O3, Q3, S3, U3, W3, Y3, AA3. AC3, AE3 and on the other graph is dollar amounts, N3, P3, R3, T3, V3, X3, Z3, AB3. AD3, AF3. I have different amounts for around 140 individual sheets. Is there a way to copy the dataset as a formula? So, each time I go to copy and paste the graph, it automatically loads the new data for that specific sheet. I hope this makes sense.

Thank you,

Answer 1

Your file link need organization account. Could you share it as attachment in private message.

Answer 2

Attached is a link for the sample workbook.

Sample Graph 20230823.xlsx

I figured out that I can take out the Sheet1! designation in the graph x value data set, but I cannot take out the worksheet designation from the y values. If I could do that and just keep the $Q$3...etc. then that should resolve the issue. See the sample below.

Answer 3

Could you share a test workbook without sensitive information? You may use formula to get the data you want to another cell range. Then each time you copy the graph, you just need select the data range.

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.