Linking 123 Workbooks

Posted on 2003-03-28
Medium Priority
Last Modified: 2010-04-22

I have a workbook with 2 sheets (Details and Volume) - I want to dynamically link the second sheet to data held in another workbook the location of which is defined by the user.

I can get this to work as follows :-

The user types in a file location in Detail:A1 eg c:\mydata\budget.123.
I create a workbook reference as text in Details:B1 eg +"<<"&a1&">>" - this then shows <<c:\mydata\budget.123>>. The first cell I need to reference in budget.123 is A:A1, so my formula is @@(Details:A2&"A:A1"). This works, but I have to copy the formula to the other 14,249 cells, and I can't find a way to do it without typing in the column and row reference.

Any ideas?

Question by:DomShaw
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 45

Expert Comment

ID: 8225130
Get your user to edit file name in the first cell in the range which contains the following formula:


and then copy that formula down the column. It auto-references to the next line in the source worksheet.

This formula should do what you are doing but in one step.

Hope that helps

Author Comment

ID: 8225435
Thanks Patrick - I'll see whether or not the grunts can cope...!
LVL 45

Expert Comment

ID: 8246930
DomShaw - How have the grunts responded to the challenge? Still grunting or whooping for joy? Do let me know - patrickab
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 8259882
Hi Patrick.

Too much like hard work, I'm afraid - they have problems following simple instructions!

I've got it working by creating a sheet that is full of cell references (A1='A1, A2='A2, B1='B1 B2=B2 etc) and changing the formula I was using from
@@(Details:A2&"A:A1") to
When I copy the formula to the other cells it automatically updates the reference to sheet B, which then links to the correct cell in the other workbook. Not very tidy, but it works!

Thanks for your help!

Accepted Solution

Paul_Lautman earned 300 total points
ID: 8260479
I have 3 possible solutions for you depending on the exact circumstances of your problem. Both solutions work without the need for the extra sheet of references.

If you are using the same sheet reference in both workbooks, then you can use the formula:
in cell A1. This'll copy down and accross incrementing the column and row automagically.

If you are not using the same sheet reference then you have 2 alternatives. you can either use combinations of @MID and @FIND to remove the sheet letter or replace @cell("coord";A1) with the slightly more complicated formula:

using the last example you can add or subtract numbers to offset the cells as you wish.

Does this help?

Author Comment

ID: 8268953
Thanks Paul - a much tidier solution!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question