# Change Formula to Include Different Tabs

#### Justinian

##### Well-known Member
I have a workbook with a master tab and then 52 tabs (one for each payroll week of the year with the pay date ending on Sunday). The sheets are named 2021 (1), 2021 (2), through 2021 (52). I have an INDEX MATCH formula that needs to look at the week ending date (Sunday) and then pull data from that week's tab so for example, this formula is running on the first tab, named 2021 (1),

=INDEX('2021 (1)'!\$D\$8:\$AE\$22,MATCH(Master!H2107,'2021 (1)'!\$B\$8:\$B\$22,0),MATCH(Master!G2107,'2021 (1)'!\$D\$5:\$AF\$5,0))

How can I alter this formula so I can drag and drop the abovementioned formula so it accounts for each tab change?

Excel Report.xlsx
ABCDEFGHI
1Week EndingYearWeek NumberAccountDept NameTypeAccount #Category Amount
301/10/2120213SERVICESApartment Preparation CleanersUnion900761212 - Birthday \$ -
401/17/2120214UPGRADEGarden - RepipeMgmt900732300 - Allowances \$ -
501/24/2120215Floating HelpersFloating HelpersUnion900606200 - Bereavement \$ -
601/31/2120216Floating HelpersFloating HelpersUnion900606022 - Vacation \$ -
Master
Cell Formulas
RangeFormula
C2:C6C2=WEEKNUM(A2,1)
I2I2=INDEX('2021 (2)'!\$D\$8:\$AE\$22,MATCH(Master!H2,'2021 (2)'!\$B\$8:\$B\$22,0),MATCH(Master!G2,'2021 (2)'!\$D\$5:\$AF\$5,0))
I3I3=INDEX('2021 (3)'!\$D\$8:\$AE\$22,MATCH(Master!H3,'2021 (3)'!\$B\$8:\$B\$22,0),MATCH(Master!G3,'2021 (3)'!\$D\$5:\$AF\$5,0))
I4I4=INDEX('2021 (4)'!\$D\$8:\$AE\$22,MATCH(Master!H4,'2021 (4)'!\$B\$8:\$B\$22,0),MATCH(Master!G4,'2021 (4)'!\$D\$5:\$AF\$5,0))
I5I5=INDEX('2021 (5)'!\$D\$8:\$AE\$22,MATCH(Master!H5,'2021 (5)'!\$B\$8:\$B\$22,0),MATCH(Master!G5,'2021 (5)'!\$D\$5:\$AF\$5,0))
I6I6=INDEX('2021 (6)'!\$D\$8:\$AE\$22,MATCH(Master!H6,'2021 (6)'!\$B\$8:\$B\$22,0),MATCH(Master!G6,'2021 (6)'!\$D\$5:\$AF\$5,0))

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Justinian

##### Well-known Member
Never mind, I figured it out.

Replies
5
Views
110
Replies
1
Views
19
Replies
3
Views
43
Replies
2
Views
93
Replies
1
Views
85

1,141,715
Messages
5,708,038
Members
421,540
Latest member
quocbinh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back