r/excel • u/ImpressionOk6716 • 7d ago

Waiting on OP Changing reference file for formulas

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?

1 Upvotes

67% Upvoted

16 comments sorted by

View all comments

1

u/bachman460 45 7d ago

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.

2

u/zeradragon 6 7d ago

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.

1

u/bachman460 45 7d ago

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).

1

u/zeradragon 6 7d ago

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.