Updating Formulas for a New Sheet

[jsnelson]

I have my main sheet that has all the data for 12 months of the year. Each Column is a month and the data is below. What I want to do is set up a new sheets with each month and pull data from each column. I created the formulas on one Month (April) that pull from my original data. I then copied the sheet to create a new month, May. Now on May's sheet, the cells are still referencing April's data. Is there an easier way to update the formulas to advance to the next column of data from the original worksheet instead of updating each manually?

Thanks

 

[Smitty]

Welcome to the Board!

Can you post an example of the formula(s) you're using?

In the meantime, you might want to look up INDIRECT in the helpfile.

And if you follow the HTML Maker link in my sig you can post a shot of your sheet(s).

 

[jsnelson]

Well I think I found a solution that works better than manual. I can highlight the column with the formulas I want to change and do a Find and replace advanced to replace the next letter in the formulas.

For example a cell in April is:

=Sheet1!G188 G is the April column on the original sheet

For May

=Sheet1!H188 H is May

I just did a find and replace in the formulas.

There might still be a better way.

 

[Smitty]

VLOOKUP with a MATCH criteria might be a good dynamic option, as could be INDEX/MATCH, which is dynamic as well.

Here are some examples.

HTH,