How do I pick the same cell in sheet 1!, Sheet2! and just drag

K

Kim

I am trying to list from 100s of sheets a number so I go to lets say F10 on
sheet 2 (sheet2!f10), (sheet3!f10), (sheet4!f10). How can I drag and copy so
sheets change?
 
E

Elkar

Let's say you're displaying your results in a column starting with row 1,
then you could use this:

=INDIRECT("sheet"&row()&"!F10")

If you wanted to start on row 5, then just subtract 4 from the row() function:

=INDIRECT("sheet"&row()-4&"!F10")

Or, if you wanted to display the results in a single row, then use the
column() function rather than the row() function.

HTH,
Elkar
 
D

davesexcel

Kim said:
I am trying to list from 100s of sheets a number so I go to lets say F10
on
sheet 2 (sheet2!f10), (sheet3!f10), (sheet4!f10). How can I drag and
copy so
sheets change?
Click to expand...
You can group the sheets together, click on the first sheet tab, then
click on the last sheet tab, this should group them all together, so
now whatever you do to sheet 1 will take effect to all the sheets,
right click on any of the tabs to ungroup, I have no idea if this works
for hundreds of sheets but it is worth a try
 
M

Max

Kim said:
I am trying to list from 100s of sheets a number
so I go to lets say F10 on sheet 2 (sheet2!f10), (sheet3!f10), (sheet4!f10).
How can I drag and copy so sheets change?
Click to expand...

One way ..

Put in say, Sheet1's B2:
=INDIRECT("'Sheet"&COLUMN(A1)+1&"'!F10")
copy B2 across

B2, C2, ... will return the contents of: Sheet2!F10, Sheet3!F10, ...

And if we want to increment it for copying down instead of across,
just change COLUMN to ROW, i.e. use:
=INDIRECT("'Sheet"&ROW(A1)+1&"'!F10")
 
Top