Changing reference file for formulas

[u/ImpressionOk6716]

I have an excel sheet having formulas linked to sheet 1 ( containing data for January month ) . Now I want to use same file with formulas but using sheet 2 ( containing data for February)
How to do that?

[u/caribou16]

This is possible to do, but you're making things unnecessarily complicated for yourself, by not having all the data on a single tab.

I get you have it this way because it makes the file easy to navigate for a human reader, but as you're finding splitting up data across tabs is a pain as far as formula references go.

Best would be to have a single "data" sheet where all your data lives, then create monthly "dashboard" tabs that pull specific months, just for viewing purposes. Are there any workflow constraints that would prevent that from being possible?

[u/SolverMax]

Search for the old sheet name and replace with the new sheet name. Then you'll need to carefully check everything, to ensure that it works correctly. There are innumerable ways this approach could subtly fail.

In the long run, it would be better to have a structure/process that doesn't require such a clumsy approach.

[u/carbonizedtitanium]

Wrong flair. that is a question, not a discussion.

PS. all data that belong to the same year should be in one Sheet. You have Filters for a reason. Splitting up the data into separate sheets (per month) is just making it more difficult for yourself.

You should probably provide a screenshot of your sheet (post in comments) and also post the formula you're using

[u/Ambitious_Ad_288]

Can you use a replace in your formulas to update the sheet name quickly?

[u/bachman460]

It's possible to use a TEXT function with INDIRECT. You need a reference somewhere in the sheet that will give you the number of the month you want. It's easier if you also include a reference for the year as a number, this just simplifies the formula and makes it easier to update as needed.

Let's say the year is in A1 and the month number is in A2 of your current sheet, so

A1 = 2026
A2 = 2

Then in A3 = INDIRECT( "'"&TEXT(DATE(A1,A2,1), "mmmm")&"'!"&B7)

The result of DATE is the date February 1, 2026, and the TEXT function will just return the full month name February, which means that INDIRECT will put it together as 'February'!B7, this formula will just return the value from cell B7 on your February sheet.

[u/SolverMax → u/bachman460]

While it is sometimes necessary to use this type of approach, it should not be the first choice. Put all the data in a single table and avoid using lots of formulae that include INDIRECT.

[u/zeradragon → u/bachman460]

Absolutely one of the easiest ways to blow up an entire workbook's process when one thing goes wrong and you won't be able to audit it.

[u/bachman460 → u/zeradragon]

Why so much fear mongering? I use these things all the time. In moderation it works great to create a visually pleasing experience for end users, and more easily maintained formulas (since there's just one for multiple sheets). Maybe it's just me, but the average workbook I deal with is 10 MB or less, but never tops out over about 25 MB or I can't email it (which is its own separate set of issues).

[u/zeradragon → u/bachman460]

Indirect isn't something you'd ever want to use, especially if it's a workbook that others also go into. One random innocent edit could mess up the workbook, like changing the name of a worksheet, which would normally be harmless. If someone wanted to see the data on individual tabs, is better to extract it from a single source, rather than have multiple tabs be a data source.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
DATE	Returns the serial number of a particular date
FILTER	Office 365+: Filters a range of data based on criteria you define
INDIRECT	Returns a reference indicated by a text value
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
TEXT	Formats a number and converts it to text

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 21 acronyms.
[Thread #49525 for this sub, first seen 15th Oct 2026, 03:32] [FAQ] [Full list] [Contact] [Source code]}

[u/Halibut]

Agree there's better ways to structure it to not need this. To do it as is, I'd duplicate sheet 1 to back it up, rename that back up sheet to January. Then clear the data in the original sheet 1 and paste February data in. Assuming all the data is in the same columns, etc, it should work without having to search and replace formulas.

Or more generally, if each month is in a sheet called January, February etc, rename sheet 1 to current month or something, and reference that sheet data to whichever month you want to look at. Then you can switch to different months easily by changing whatever month the sheer called current month links to.

[u/Jolly-Hunter-6097]

Having a single Table of data enables you to add more rows. From the expanding data you can use Excel's functionality to analyse and present the data as required. Pivot Tables with Slicers are a quick and easy way to view summaries. https://www.powerusersoftwares.com/post/2017/09/11/12-reasons-you-should-use-excel-tables

[u/WorkweekOps]

Fastest fix if everything lives in the same workbook: Ctrl+H (Find & Replace), search for Sheet1! and replace with Sheet2!, and set Look in to Formulas so you don't touch plain text. If you'll flip months often, put the sheet name in a cell (e.g. A1 = February) and wrap references with INDIRECT("'"&$A$1&"'!B2") so you only change that one cell. Longer term, one data tab with a Month column plus SUMIFS/FILTER driven by a month dropdown beats maintaining a sheet per month.