Link to another workbook - reference

[Cam12]

Hi

Im trying to figure ut if it's a way so solve the above but have not yet found a way.

if we look at a normal cell reference to another workbook it can look like this.

=COUNTIF('[test.xls]Data'!$F$6:$F$65536;'Failures  Data'!D12

But what i now want to do is to not have to have the text "test.xls" in the formula, instead i want to link that information to another cell where i can write down the name of the file it should look for, the reason is that the test.xls file will be updated regularly so it will be version 1 and then version 2 and so on and i don´t want to have to change all the formulas in the different cells every time.

In my head simply putted it would be something like

=COUNTIF('[B1]Data'!$F$6:$F$65536;'Failures  Data'!D12

Hopefully someone might understand what i mean

Many Thanks for any help i can get.

// Cam12

 

[Jerry Sullivan]

Hi Cam12, Using a cell reference for a filename can be done with the INDIRECT formula function, however INDIRECT only works if the referenced external workbook is open.

Two VBA UDF's (User Defined Functions) that are workarounds are getvalue from John Walkenbach, or the Pull function from Harlan Grove.

A simpler technique that doesn't require VBA would be to do a Find-Replace in your workbook
Find what: "test version 01.xls"
Replace with: "test version 02.xls"

Alternatively, you can update the link references in all your formulas in one step through Edit Links > Change Source...

 

[Cam12]

Hi Cam12, Using a cell reference for a filename can be done with the INDIRECT formula function, however INDIRECT only works if the referenced external workbook is open.

Two VBA UDF's (User Defined Functions) that are workarounds are getvalue from John Walkenbach, or the Pull function from Harlan Grove.

A simpler technique that doesn't require VBA would be to do a Find-Replace in your workbook
Find what: "test version 01.xls"
Replace with: "test version 02.xls"

Alternatively, you can update the link references in all your formulas in one step through Edit Links > Change Source...

Thanks for the reply, it really helps me in the correct direction.
I already have an VBA that opens the reference workbook so that problems is solved, I'm a bit unsure do of how to use INDIRECT in my COUNTIFS. What i tried is as bellow and just get #REF so something is not correct with what i do.

=COUNTIFS(INDIRECT("'"&$C$1&"'!Data'!$F$6:$F$65536");'Failures Data'!D12;INDIRECT("'"&$C$1&"'!Data'!$V$6:$V$65536");'Failures Data'!$A$6)

Any ide? Thanks!

// Tobias

 

[Jerry Sullivan]

Tobias,

The formula would need to be slightly different depending on whether the value in $C$1 is like: "test1.xlsx" or "[test1.xlsx]" or "test1".

Assuming that the value is like "test1.xlsx", this should work...

=COUNTIFS(INDIRECT("'[" &C1 & "]Data'!$F$6:$F$65536");'Failures Data'!E12;
    INDIRECT("'[" &C1 & "]Data'!$V$6:$V$65536");'Failures Data'!A6)

 

[Cam12]

Tobias,

The formula would need to be slightly different depending on whether the value in $C$1 is like: "test1.xlsx" or "[test1.xlsx]" or "test1".

Assuming that the value is like "test1.xlsx", this should work...

=COUNTIFS(INDIRECT("'[" &C1 & "]Data'!$F$6:$F$65536");'Failures Data'!E12;
    INDIRECT("'[" &C1 & "]Data'!$V$6:$V$65536");'Failures Data'!A6)

Forgott to thank you, it worked great, many thanks!

// Tobias