Taking Certian Data only from 2 Spreadsheets
 

Hi All,

I have 39 Excel Spreadsheets & there are only certain figures i would
like in each spreadsheet & they are all in the same Coloumn & row. I
would like these figures to go into another spreadsheet i have created
how can i do this?? Is it possible? Can i transfer them over into a new
spreadsheet so within this new sheet i then can caculate them all. Would
appreciate all the help.

Cheers
Will


--
william4444
------------------------------------------------------------------------
william4444's Profile: http://www.excelforum.com/member.php...o&userid=33991
View this thread: http://www.excelforum.com/showthread...hreadid=546459

Taking Certian Data only from 2 Spreadsheets
 

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 will resolve 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
---
"william4444" wrote:

Hi All,

I have 39 Excel Spreadsheets & there are only certain figures i would
like in each spreadsheet & they are all in the same Coloumn & row. I
would like these figures to go into another spreadsheet i have created
how can i do this?? Is it possible? Can i transfer them over into a new
spreadsheet so within this new sheet i then can caculate them all. Would
appreciate all the help.

Cheers
Will


--
william4444
------------------------------------------------------------------------
william4444's Profile: http://www.excelforum.com/member.php...o&userid=33991
View this thread: http://www.excelforum.com/showthread...hreadid=546459

Taking Certian Data only from 2 Spreadsheets
 

A sample construct is available at:
http://www.savefile.com/files/5536280
Indirect_Taking_certain data from 2 sheets.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---