Increment Index for Multiple Sheets in Excel

Question

I have an Excel formula which iterates twice over 2 separate columns for different sheets.

Basically it matches two index values for rows and columns of these sheets and stores the two values that are side-by-side (93x16 Matrix).

The formula for doing this is:

=INDEX(Sheet1!$B$3:$Q$96,MATCH(Formatted!$A3,Sheet1!$A$3:$A$96,0),MATCH(Formatted!$B3,Sheet1!$B$1:$Q$1,0))

Where the MASTER sheet is "MATCH" and the sheets go from 1-52.

I want to iterate over all the sheets by copying this formula across 52 columns but I want the sheets to iterate sheet(n+1) times (n=0,1,2,3,.....51)...

How can I iterate this formula or just add 1 to each formula's Sheet value?

Answer 1

I'm not quite sure to understand your wording here but I guess you are looking for INDIRECT() and COLUMN() as in following example:

=INDEX(INDIRECT("Sheet"&COLUMN()&"!A1:O24"),RANDBETWEEN(2,24),RANDBETWEEN(2,15))

Where "A1:O24" is the range for each Sheet(n) You'll have to adapt the COLUMN() criteria depending on where you put the formula (e.g. if you strat in column "B" in sheet Master, you'll have to put COLUMN()-1.