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.