[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Linking 123 Workbooks

Hi.

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?


0
DomShaw
Asked:
DomShaw
  • 3
  • 2
1 Solution
 
patrickabCommented:
Get your user to edit file name in the first cell in the range which contains the following formula:

+<<j:\lotus\work\123\test1.123>>A:A1..A:A1

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
0
 
DomShawAuthor Commented:
Thanks Patrick - I'll see whether or not the grunts can cope...!
0
 
patrickabCommented:
DomShaw - How have the grunts responded to the challenge? Still grunting or whooping for joy? Do let me know - patrickab
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
DomShawAuthor Commented:
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
@@(Details:A2&B:A1)
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!
0
 
Paul_LautmanCommented:
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:
@@(Details:$A$2&@CELL("coord";A1))
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:
@@(Details:$A$2&@coord(1;@cell("column";A1);@cell("row";A1)))

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

Does this help?
0
 
DomShawAuthor Commented:
Thanks Paul - a much tidier solution!
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now