Auto Increment sheet number ni formulas

[hwaghulde]

Hello experts,
I am looking for auto increment or auto fill the different sheet number Sheet1, Sheet2, Sheet3........Sheet300. PLease help to get the following in aauto fill mode. BTW i am using EXCEL-2007.

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]=Sheet1!D$6
[/TD]
[TD]=Sheet1!D$9
[/TD]
[TD]=Sheet1!D$15
[/TD]
[/TR]
[TR]
[TD]=Sheet2!D$6
[/TD]
[TD]=Sheet2!D$9
[/TD]
[TD]=Sheet2!D$15
[/TD]
[/TR]
[TR]
[TD]=Sheet3!D$6
[/TD]
[TD]=Sheet3!D$9
[/TD]
[TD]=Sheet3!D$15
[/TD]
[/TR]
[TR]
[TD]=Sheet4!D$6
[/TD]
[TD]=Sheet4!D$9
[/TD]
[TD]=Sheet4!D$15
[/TD]
[/TR]
[TR]
[TD]=Sheet5!D$6
[/TD]
[TD]=Sheet5!D$9
[/TD]
[TD]=Sheet5!D$15
[/TD]
[/TR]
[TR]
[TD]=Sheet6!D$6
[/TD]
[TD]=Sheet6!D$9
[/TD]
[TD]=Sheet6!D$15
[/TD]
[/TR]
[TR]
[TD]=Sheet6!D$6
[/TD]
[TD]=Sheet7!D$9
[/TD]
[TD]=Sheet7!D$15
[/TD]
[/TR]
[TR]
[TD]=Sheet8!D$6
[/TD]
[TD]=Sheet8!D$9
[/TD]
[TD]=Sheet8!D$15
[/TD]
[/TR]
[TR]
[TD]=Sheet9!D$6
[/TD]
[TD]=Sheet9!D$9
[/TD]
[TD]=Sheet9!D$15
[/TD]
[/TR]
[TR]
[TD]=Sheet10!D$6
[/TD]
[TD]=Sheet10!D$9
[/TD]
[TD]=Sheet10!D$15
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use eg:

=INDIRECT("'Sheet" & ROWS(A$1:A1) & "'!D6")

but the INDIRECT function is volatile so you may find issues with speed.

 

[ukmikeb]

Hi

Perhaps :-

=INDIRECT("Sheet"&ROW()&"!D$6")

and dragged down.

Adjust ROW() to reflect accordingly.

hth

 

[hwaghulde]

Hi

Perhaps :-

=INDIRECT("Sheet"&ROW()&"!D$6")

and dragged down.

Adjust ROW() to reflect accordingly.

hth

======================================X=====================================

Hello.......... thats a reason i called experts,..............................Hats off to you all......Thank you very much for the reply......

I tried it & working prefectly........

Thank you all once again.......

mrexcel forum will help me a lot in comming future.

Hemant Waghulde
INtools Designer,
Borouge, Abudhabi, UAE

Issues resolved

 

[ukmikeb]

Hi

Thanks for the feedback.

Andrew's will work also!

Good luck with your project.