Summary sheet increment

[gingerexcel]

Hi,

I'm pretty new to the world of Excel and still very much finding my way. I could use some help on the following formula. I am creating a summary sheet that provides an overview of 80 other sheets. In the summary sheet I would like to take this formula and drag it down with the sheet numbers increasing, but when i drag it down the sheet number stays the same, leaving me to manually edit.

=IF(Sheet2!$B$6="","",Sheet2!$B$6)


When dragged down i would like it to read;

=IF(Sheet2!$B$6="","",Sheet2!$B$6)
=IF(Sheet3!$B$6="","",Sheet3!$B$6)
=IF(Sheet4!$B$6="","",Sheet4!$B$6)


Hope this makes sense to someone....

[tung74]

What you want to do is use a "helper cell column" with the values 1,2,3,4... in A1,A2,A3,A4...

From there, you will use the INDIRECT function

For example:

=IF(INDIRECT("Sheet" & A2 & "!$B$6")="","", INDIRECT("Sheet" & A2 & "!$B$6")

Let me know if that takes care of it

[AlKey]

This is with rolling sheet number:

=IF(INDIRECT("Sheet"&ROW(A2)&"!"&"B6")="","",INDIRECT("Sheet"&ROW(A2)&"!"&"B6"))

[gingerexcel]

Indirect function worked a treat...

Thanks guys..!

[AlKey]

You're Welcome. Don't forget to thank those who helped by clicking on Add Reputation *

[tung74]

You bet, welcome!