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?
1 Answer
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