INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas

[k3yn0t3]

Hi everyone. I have two sheets: (A) master sheet and (B) single store sheet (examples below).

I'd greatly appreciate your help with two things:

1. A Formula that dynamically populates rows 2-15 of Col B on "Store 1 Sheet"
   1. I tried this but it's off by one row... I think it's treating the "Store Number" hardcode as the row # input in the array it's pulling data from in the MasterSheet I think?

      =+INDEX('MasterSheet'!$A$6:$O$125,MATCH('Store1Sheet'!$B$5,'MasterSheet'!$A$7:$A$108,0),MATCH($A6,'MasterSheet'!$A$6:$O$6,0))

2. A Macro that duplicates this sheet for all stores in the excel master sheet (showing 3 below but there are 125 stores).
   1. For example, it would make a copy of "Store 1 Sheet" but update hardcoded cell B1 to be 2, 3, 4, etc. all the way to Store 125
   2. Names of sheets generated would be "Store 1", "Store 2", etc.
   3. Note: there are many more formulas on "Store 1 Sheet" below the first ~15 rows I show below...how do I make sure all of the dynamic formulas are copied, as well?

Thank you in advance for any help!

EXCEL SHEET SAMPLES​

*Note: the add-in isn't working for me despite lots of troubleshooting, please pardon use of crude tables below

(A) MASTER SHEET

Store Number

Status

Open Date

Close Date

State

Region

Ownership

Main SQFT

Patio SQFT

Total SQFT

Current Term Expiration

Lease Term w/ Extensions

Build Cost

1

Open

10/1/1991

na

Georgia

South

Leased

8,000

-

8,000

12/31/2025

12/31/2040

800

2

Open

2/1/1992

na

Georgia

South

Leased

9,000

-

9,000

12/31/2025

12/31/2040

505

3

Open

3/1/1997

na

Texas

South

Leased

9,000

1,000

10,000

12/31/2025

12/31/2040

12

(B) STORE 1 SHEET

Store Number	1​
Status	*formula here*​
Open Date	*formula here*
Close Date	*formula here*
State	*formula here*
Region	*formula here*
Format	*formula here*
Building Type	*formula here*
Ownership	*formula here*
Main SQFT	*formula here*​
Patio SQFT	*formula here*​
Total SQFT	*formula here*​
Current Term Expiration	*formula here*​
Lease Term w/ Extensions	*formula here*​
Total Upfront CapEx	*formula here* ​

 

[duggie33]

It is likely off by one row because your index starts in row 6 and match starts in row 7. For cases like this, it is common to put "-1" behind the row match.

Hope that helps,

Doug

 

[k3yn0t3]

Massive thank you, Doug. I updated the index to start one row down as it was previously (and erroneously) including the header row.

 

[duggie33]

Glad to help,

Doug

 

[Alex Blakenburg]

Taking this 1 step further.

I would expect the match to cover the same rows as the Index Range. I would change this:

=+INDEX('MasterSheet'!$A$6:$O$125,MATCH('Store1Sheet'!$B$5,'MasterSheet'!$A$7:$A$108,0),MATCH($A6,'MasterSheet'!$A$6:$O$6,0))

To this:

=INDEX('MasterSheet'!$A$7:$O$125,MATCH($B$5,'MasterSheet'!$A$7:$A$125,0),MATCH($A6,'MasterSheet'!$A$6:$O$6,0))

• The 2 ranges match
• Assuming the formula is on the sheet Store1 then you don't want Store1Sheet hard coded in the formula.
  • It makes to formula more cluttered and harder to read.​
  • When you copy the sheet it will keep pointing to Store1Sheet instead of the copied sheet ie Store2, Store3 etc.
    ​