Easier Way to Adjust Formula?

[M_Easty]

I have an excel file that includes several tabs of data as well as 20 charts.

On a monthly basis I must add the final results from the previous month then print the new charts based on a rolling 13 months.

This is the reference data for the charts, to update them, each month I must change the references in red to the new ones based on the new time frame. For example, D and P became E and Q this month.

My question is, is there an easier way than just manually change it, to accomplish this since I have to do it 20 times, once for each slide?

=SERIES(DATA1!$C$7,DATA1!$D$3:$P$3,DATA1!$D$7:$P$7,1)

Thanks in advance for any help. ME

[Sam Capricci]

have you tried find and replace all?

[M_Easty]

have you tried find and replace all?

I've thought about it but in this case I'd have to worry about both the D in my reference as well as the word DATA, also Find and Replace doesn't seem to work if you are in the formula/reference bar.

[Sam Capricci]

what you would do is use find $D$3 and replace all with $E$3 or find $D and replace with $E, that should be specific enough, you can try it for one cell first using find next and hit replace, see if it changes what you want but nothing else, then hit replace all and it should be done. Give it a try on some samples to see.

[M_Easty]

I tried, find and replace is no active once you click into the formula bar while in a chart. thank you for trying though

[protonLeah]

You need to create dynamic named ranges for your chart. Post a sample workbook.
(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!