asked on

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?

patrickab

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

DomShaw

ASKER

Thanks Patrick - I'll see whether or not the grunts can cope...!

patrickab

DomShaw - How have the grunts responded to the challenge? Still grunting or whooping for joy? Do let me know - patrickab

DomShaw

ASKER

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!

ASKER CERTIFIED SOLUTION

Paul_Lautman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DomShaw

ASKER

Thanks Paul - a much tidier solution!