Linking Spreadsheet Data 3

[dik]

I have several excel sheets with section property information.

I want to be able to access this data using VLOOKUP or something similar by linking the data to a new spreadsheet. Any suggestions?

thanks, Dik

 

[ivymike]

I suggest you don't link to external spreadsheets... it seems like it always ends poorly.

 

[chichuck]

I agree with ivymike. Many years ago, when spreadsheet file size was an issue (this was in the days of Lotus 123 and DOS!) it was not possible to put a large data table into the same spreadsheet you're working on/in because the file got too big. Nowadays though its not an issue.

I use that type of thing for structural steel wide flange properties. I just put the table on a different page in my worksheet. I tried once to use a separate spreadsheet with the table. Its hard to keep dragging that extra file along to every directory and subfolder that your worksheet goes into. And the references to the file always seeme to get tangled up and end up with error messages about a missing file reference.

If the table is not huge, put it into the spreadsheet your working on.


regards,


chichuck

 

[electricpete]

If you want to create a formula using a function (like vlookup) which accesses a value in another spreadsheet, then open both spreadsheets, type the beginning of the function "+vlookup('lookupstring'," and then use window menu to switch to the other sheet, place cursor to highlight the range you you want, and place enter... this puts you back into the first sheet with a cell reading something like "+vlookup(lookupstring,'C:\Path2\File2.XLS'!$B$9:$Q$19'". Then you can finish it off by adding the column number for example "+vlookup(lookupstring,'C:\Path2\File2.XLS'!$B$9:$Q$19',3,false)"

(note all my double-quotes are to set off from my text and would not appear on your screen)


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

I have quite a few spreadsheets linked to other spreadsheets. I have no problems as long as I don't move the files. If I want to move the files, then I need to fix the link using the "update links" menu. Maybe there is an easier way to delete the path and put all files in the same directory... I'm not sure.

I find this construction very helpful. Each individual spreadsheet can be a logical grouping of data with many spreadsheets. Then I link to a master spreadsheet to extract summary information from the individual spreadsheets. Lumping everything into one huge spreadsheet would make it much more difficult to oragnize/navigate.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[dik]

Good replies from people that know spreadsheets... I'll try linking them and if the only problem is moving files... I'll set up a repository for all linked files. If there are other gremlins lurking... we'll find out. I've had problems with programs not using DDE and OLE properly before.

Dik

 

[melone]

Isn't this exactly what Access (or other database programs) handle VERY well?

 

[gbam]

Yes, dbase progs were specificlly set up to handle data as described.
But many of us prefer to use a spreadsheet. I have many "Linked" spreadsheets and all work well. I even link to Microstation, although only once. However, I slightly recall using a pivot table to help with the link issues of the past. I cannot fully remember why what or how but that may still be an option for linking diff. files.

Good Luck with it all! Moving files can be a pain.

 

[schwarz]

Zip all needed files together with path info. This will ensure a 'good move'.
br/

 

[dik]

Further to the database qualities, I'm using the data for calculations... Dik