Change Formula to Include Different Tabs

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,480
Office Version
  1. 365
Platform
  1. Windows
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
201/03/2120212AdminAdminMgmt900700001 - Straight Time $ 1,955.00
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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top