Importing Data From Another Spreadsheet

[SamuelT]

Hi all (again),

So, this continues on from my last query, hopefully in a slightly more
simplified manner.

Is there a means, beyond simply copying and pasting, of displaying a
number of rows and columns from one spreadsheet to another with the use
of a formula.

For example, in the spreadsheet 'Report Template', I want to display
A1:F20 from spreadsheets 'Report1', 'Report2' and 'Report3'. The
clincher, is that on some days I will not get sent 'Report2' (for
instance), but I still want to present the other two reports without
having a mass of blank cells in the middle. Is there a formula where I
can specify from which spreadsheet I want to display data from (as one
might using SUMPRODUCT, perhaps)?

Any ideas?

TIA,

SamuelT

 

[Max]

.. Is there a formula where I can specify from which spreadsheet
I want to display data from ...

For example, in the spreadsheet 'Report Template', I want to display
A1:F20 from spreadsheets 'Report1', 'Report2' and 'Report3'

One way / interp based on the above lines ..

Sample file at:
http://www.savefile.com/files/2356967
Displaying_Data_From_Selectable_Sheet_SamuelT

In sheet: Report Template
--------------------
Assuming the report name will be entered* in A1, e.g.: Report1
*or selectable from a DV droplist (as set-up in the sample file)

Put in A2:
=IF(OR($A$1="",ISERROR(INDIRECT("'"&$A$1&"'!A1"))),"",
IF(OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROW(A1)-1,COLUMN(A1)-1,)=0,
"",OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROW(A1)-1,COLUMN(A1)-1,)))

Copy A2 across to F2, fill down to F21
(i.e. cover the required extent)

A2:F21 will display the desired results from the sheetname
entered / selected in A1

 

[SamuelT]

Many thanks Max - not exactly what I was after, but I can use it, fo
sure!

Samuel

 

[Max]

You're welcome !
Thanks for the feedback ..

 

[SamuelT]

Hey Max (and any others),

With the formula above, what would I need to change for the informatio
to come from another spreadsheet (e.g. select cells A1:C30 in
spreadsheet named 'External Data') as opposed to simply anothe
worksheet within the same document?

TIA,

Samuel

 

[Max]

With the formula above, what would I need to change for the information
to come from another spreadsheet (e.g. select cells A1:C30 in a
spreadsheet named 'External Data') as opposed to simply another
worksheet within the same document?

Extending the sample file's set-up ..

Assuming the reports are in Book1.xls which is open simultaneously (this is
needed for the INDIRECT to work), then the simplest way here is to change
the DV source in sheet: DV to include the book name besides the sheetname
(No change required to the formulae in sheet: Report Template)

In sheet: DV,
Put instead in A1: [Book1]Report1
Copy A1 down to A10

Then in sheet: Report Template
just select the report from the droplist in A1 as before

 

[SamuelT]

Perfect! Thanks Max - you've made my Monday morning bearable!

Sam

 

[Max]

You're welcome, Sam !