Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|