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?
ROWS function and
INDIRECT. Let's assume you put the first formula in cell
C3 then use this version
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.
C3s depending on start cell