I have an index sheet that I need to get data into from the other sheets in the spreadsheet. The formula I have set up is ='I101'!$B$8 where the I101 is the sheet to get data from and the fixed cell B8. However when I drag down this formula in the index sheet I need the 101 to increase by one and so as I drag the formula down. However it stays fixed and I have to manually edit which is time consuming when you have the get data from 268 sheets. How in this formula can i tell the next line down from I001 to skip to next sheet?

Try using `ROWS`

function and `INDIRECT`

. Let's assume you put the first formula in cell `C3`

then use this version

`=INDIRECT("'I"&ROWS(C$3:C3)+100&"'!B8")`

as you drag down, the `ROWS`

function increments and increases the number within your sheet name - that's more robust than using `ROW`

function because this approach will still work if you add or delete rows above the formula.

Change the `C3s`

depending on start cell