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

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?

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

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


"Kim" wrote:

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?

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

Kim Wrote:
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?
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


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=520846

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

"Kim" wrote:
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?

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")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---