Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto "copy and paste" individual cells from various sheets into one sheet ??
Hi,
I am using Excel 2002 and work on a workbook. Inside the workbook, there are 3 sheets: Sheet 1, Sheet 2, Sheet3 For each of the sheet, Cell A1 and A3 (two cells) both contain the data that I want. Question: I want to have Sheet 1: Cell A1 and A3, Sheet 2: Cell A1 and A3, Sheet 3: A1 and A3 data shown on a new sheet, say Sheet 4. 1) Is there an automatic way that all these 3 sheets can be "exported" to Sheet 4? 2) Any other method than marking " = Sheet1!A1 " etc in Sheet 4? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto "copy and paste" individual cells from various sheets into one sheet ??
One way would be via using INDIRECT
Perhaps a simple example to illustrate .. In Sheet4, Suppose we list sheetnames across in B1:D1, eg: Sheet1, Sheet2, Sheet3 and we have the cell refs of interest listed down in A2:A3, eg: A1, A3 Then, we could put in B2: =INDIRECT("'" & B$1 & "'!" & $A2) and copy B2 across & down to D3 .. B2:D2 will return the same as the link formulas: =Sheet1!A1, =Sheet2!A1, etc B3:D3 will return the same as the link formulas: =Sheet1!A3, =Sheet2!A3, etc INDIRECT resolves the concatenation of the sheetname and cell ref text strings to return the results from the particular sheet and cell listed in B1:D1, and in A2:A3. So we could design the layout and define / change the sheetnames and cell ref text strings in the header row/col to suit the purpose. And for a neater look, we could also suppress the display of "extraneous" zeros in Sheet4 via clicking Tools Options View tab Uncheck "Zero values" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... Hi, I am using Excel 2002 and work on a workbook. Inside the workbook, there are 3 sheets: Sheet 1, Sheet 2, Sheet3 For each of the sheet, Cell A1 and A3 (two cells) both contain the data that I want. Question: I want to have Sheet 1: Cell A1 and A3, Sheet 2: Cell A1 and A3, Sheet 3: A1 and A3 data shown on a new sheet, say Sheet 4. 1) Is there an automatic way that all these 3 sheets can be "exported" to Sheet 4? 2) Any other method than marking " = Sheet1!A1 " etc in Sheet 4? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto "copy and paste" individual cells from various sheets into one sheet ??
Here's a sample file to illustrate:
http://cjoint.com/?dbevEkGhlc Intro Extracting data into summary using INDIRECT.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|