Cell references/linking between workbooks

[rickstaylor]

Hi All
Here's a doozy for you.

I have 2 workbooks, one an inventory overview of kit, the other a detailed version. Obviously some info is duplicated. I've made a straight forward link (see below), but it needs improvement,

What I want to achieve:
In the detailed inventory, I type in a row reference in for an updated entry in the overview workbook, and it populates the appropriate cells in the detailed version.
At the moment the link is this:
='[overview.xls]Sheet1'!$C$3
What I want is for the row reference (underlined, above), to be entered into a cell, then it gets included in the above formula in each of the calculated cells.

Hope it makes sense, comments/suggestions/ideas welcome!

Many thanks
Richard

 

[pplstuff]

I sounds like you want to use the indirect formula.

=indirect("'[overview.xls]Sheet1'!$C$"&a1)

With this, you'd enter a number in cell A1 and the indirect formula will go to that file/sheet/cell and return the value. (Double-check that it works when the other workbook is *closed*).

 

[Jonmo1]

Welcome to the board..

Try

=INDEX('[overview.xls]Sheet1'!$C:$C,A1)

A1 = 3

 

[pplstuff]

=INDEX('[overview.xls]Sheet1'!$C:$C,A1)

Very cool way to do it with a non-volatile function!

 

[Jonmo1]

Very cool way to do it with a non-volatile function!

Thanks.

It's also not a locked reference to column C (well, if you remove the $ anyway).
With indirect, the Column reference is locked, because it's just a text string.
So it won't incriment when dragged right/left

With index, it will incriment (without the $)
=INDEX('[overview.xls]Sheet1'!C:C,$A1)

 

[rickstaylor]

Guys, what a great response in such a short time. Much appreciated. the Indirect formula works a treat