Link to spreadsheet based on cell content

**japper** · 09-26-2007, 03:13 PM

Hi,

I'm sure this has been answered but I can't think of a good way of wording the search!

I'm setting up some master / slave spreadsheets but the names of the spreadsheets change quite regularly (don't you love high staff turnover?) so rather than update all the links I want to set up the master spreadsheet to look up the spreadheet name from a cell.

Eg

Spreadhseet names:
Dave, John, Sarah

In the Master, rather than:
='John.xls'!A10

I want:
='A1.xls'!A10

So that all I have to do is change the name in A1 of the new spreadsheet I want it to link to (eg John leaves and the team gets a new rep called Keith. I save a new spreadsheet call Keith and type Keith into A1 of the Master and it links to that spreadhseet rather than John)

It's late and I've been doing various spreadsheets for 9 hours now, so excuse my poor attempt at an explanation!

thanks

NOTE:I've edited this to add the .xls extension to the example

**VBA Noob** · 09-26-2007, 03:17 PM

Does this help

=INDIRECT(A1&"!A10")

Where A1 = a Sheet tab name

VBA Noob

**japper** · 09-27-2007, 08:20 AM

Not quite- that would work if it was a worksheet but this is linking to seperate workbooks. John.xls; sarah.xls etc.

**VBA Noob** · 09-27-2007, 09:25 AM

There is no mention of workbooks in your inital post.

VBA Noob

**japper** · 09-27-2007, 09:28 AM

After your post I realised I'd called them 'Spreadsheets' rather than 'Workbooks' so I edited it to show the xls. extension and put a note at the bottom.

As I said- it had been a long day- apologies for the confusion.