Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"