Accessing different sheets using "fill down"

[cobra boy]

Hi, I'm new to this forum and this is my first question. I would like to be able to write a formula on sheet 1 that would access the same cell in each subsequent sheet. If I type =(sheet2!$K$6) in a cell on sheet 1 and then use the fill down command, sheet 2 never changes to sheet 3,4,5 and so on. Once again when I use fill down on sheet 1, I want to change sheets but reference the same cell on each sheet. I have like 60+ sheets and don't want to have to enter 60+ data manually on to sheet 1. Maybe I won't be able to do this using the fill down method. I appreciate any help out there. Thanks

[dredwolf]

looks like you need to use something like this...will only work if your using the default sheets, you'd have to adjust for custom named sheets...
assuming start cell is a2:
=INDIRECT("Sheet"&ROWS($A$2:A2)+1&"!$K$6")
copy down

Hope this helps

[cobra boy]

looks like you need to use something like this...will only work if your using the default sheets, you'd have to adjust for custom named sheets...
assuming start cell is a2:
=INDIRECT("Sheet"&ROWS($A$2:A2)+1&"!$K$6")
copy down

Hope this helps

Dredwolf..............you da man!! That worked perfectly. Thanks very much

Cobra Boy

[dredwolf]

You are welcome !

And as my friend Sixth suggested, I reallly should have made it this:

Please Login or Register  to view this content.

just to make sure that if spaces were in the sheet name, it would still work, not a problem with the default sheets, but could be with renamed sheets


Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

[dredwolf]

Just uploading a sample Workbook with what is (in my opinion) a better Solution : AutoIncrSheet.Sol1.xlsx
This one populates a list of worksheets, then uses Index to return the sheetname for the Indirect
As a bonus, the sheets can be renamed, and it still works