asked on

How do I alter a link in excel depending upon a cell value

Hi,

I have a link that works fine:
='K:\Accounts\[1.xls]Sheet1'!F74

but I now want to change the referenced workbook based on a cell value...

I have tried:
='K:\Accounts\["&A1&".xls]Sheet1'!F74
but this returns #REF where A1 =1

Can you please advise? I am using Excel 2010

Many Thanks

SOLUTION

Shanan212

membership

This solution is only available to members.

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

Start Free Trial

martinibbo

ASKER

The file must be closed as I have many links....

Can we encorporate the index function somehow??

Thanks

SOLUTION

Saqib Husain

membership

This solution is only available to members.

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

Start Free Trial

martinibbo

ASKER

Briefly doing some research into morefunc.xla, there appears to be some compatability issues with Excel 2010 (upto 2007 supported) and some people experiencing the program to crash.

As multiple computers would be accessing this workbook - would I need to install the addin onto each computer?

Also can you recommend a "safe" website to download the addin from?

The link to: http://www.get-digital-help.com/2010/05/12/extracting-unique-distinct-text-values-from-a-closed-workbook-in-excel-formula/ provides an interesting result close to what I want. Can this be adapted to suit my formula?

Many Thanks

SOLUTION

Saqib Husain

membership

This solution is only available to members.

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

Start Free Trial

ASKER CERTIFIED SOLUTION

martinibbo

membership

This solution is only available to members.

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

Start Free Trial

martinibbo

ASKER

Most suitable, without installing addins and without compromising performance