×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Linking Spreadsheet Data
3

Linking Spreadsheet Data

Linking Spreadsheet Data

(OP)
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

RE: Linking Spreadsheet Data

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

RE: Linking Spreadsheet Data

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

RE: Linking Spreadsheet Data

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.

RE: Linking Spreadsheet Data

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.

RE: Linking Spreadsheet Data

(OP)
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

RE: Linking Spreadsheet Data

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

RE: Linking Spreadsheet Data

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.

RE: Linking Spreadsheet Data

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

RE: Linking Spreadsheet Data

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources