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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.