Retrieve external data.

[tmarc]

I’m attempting to retrieve external data from one excel sheet to another excel sheet. -Data- -Get external Data- -New Database Query-, and highlight -Excel Files*- on the ‘Chose Data Source window’. Then by browsing or selecting -Ok-, chose the Excel file to connect. With the resulting message “This data source contains no visible tables” Why does the data source information need to be in a table? I am unable to enter the source sheet information into a table -Data-, -Table- ‘Row’, and or ‘Column’ input cell. With the resulting message “You must select a single rectangle that is more than one row high and more than one column wide”. So I do that and get a message “Input cell reference is not valid”. All I want is to automatically retrieve information from one sheet and place it in another… Seems simple. Lookup and Reference formula doesn’t seem to offer a solution either. Thanks, if you can assist.

Signed, Linux lover
This message was edited by tmarc on 2002-08-28 15:00

 

[XL-Dennis]

Hi "Linux Lover",

Welcome to the board

The table-structure is a basic request for XL to handle data in this context.

Secondly, XL needs some help to separate fieldnames (headlines in each columns) from postes (each row represent a post).

Therefore You need to prepare the table of data by inserting properly headlines (make them bold) in order for XL to get on with its duty.

Kind regards,
Dennis

Ps. I have tested several spreadsheetssoftware on the Linux-plattform and they also require a table-structured data

 

[Swamp Thing]

The method you have tried (data > get external data) is intended specifically for databases that are organized as a collection of tables. This is apparently not the case in your data file.

What you can try is for example, if you want to import data from Sheet2, cell B4 of the data file into Sheet1, cell c5 of the current file then type this formula into c5:

<pre>='C:WINDOWSDesktopaaa[datafile.xls]Sheet2'!B4</pre>

If you want to import a column, a row, or a rectangular region then you can type one cell and then drag down/across as usual.

See excel help on external workbook formulas/links.

 

[tmarc]

XL-Dennis, Swamp Thing, Thanks for the info.

Swamp Thing, The external formula/link will best serve my application. There is one hang-up. Importing a column, a row, or a rectangular region only links the upper right cell of the selected area.

=[source.xls]Sheet1!$A$2:$C$5

On the target.xls, only the contains of A2 is linked

Source.xls

column A column B column C
a3.................b3..............c3
a4.................b4..............c4
a5.................b5..............c5



Target.xls

column A



???

What do you get out of this deal... Great help for.. "Thanks", or should I buy a T-shirt from the XL store?

Linux lover
This message was edited by tmarc on 2002-08-29 10:55
This message was edited by tmarc on 2002-08-29 11:00
This message was edited by tmarc on 2002-08-29 11:02