Linking to external workbooks with changing sheet names

[Sh0t2bts]

Hi All,

I have 10 workbooks that I need to check each day, the good news is I only
have to sheck one worksheet in each workbook, the worksheet is named the
previouse date.

This is how I would like it to work.

I have a new workbook with 11 worksheet one for each original workbook the
worksheets points at a given cell in a gived worksheet E.G ='[John
Smithl.xls]Wed 01st'!$A$2

The but I want the date part to change and pick this up from my current work
book E.G This bit stays the same:- ='[John Smith.xls]xxxxx'!$A$2

but the xxxxx part is picked up from cell al on the current worksheet sort
of like:- ='[John Smith.xls]&A1&'!$A$2

A1 Would = Wed 01st

Any Idea how I would do this???



Cheers

Mark

 

[Tom Ogilvy]

=Indirect("'[John Smith.xls]"&A1&"'!$A$2")

But the John Smith.xls workbook would need to be open for the formula to
work.

 

[Sh0t2bts]

Excelant Tom,

Now me been a lazy git, is there a way to do it without opening the John
Smith workbook???


Cheers

Mark

=Indirect("'[John Smith.xls]"&A1&"'!$A$2")

But the John Smith.xls workbook would need to be open for the formula to
work.

--
Regards,
Tom Ogilvy

Hi All,

I have 10 workbooks that I need to check each day, the good news is I only
have to sheck one worksheet in each workbook, the worksheet is named the
previouse date.

This is how I would like it to work.

I have a new workbook with 11 worksheet one for each original workbook the
worksheets points at a given cell in a gived worksheet E.G ='[John
Smithl.xls]Wed 01st'!$A$2

The but I want the date part to change and pick this up from my current work
book E.G This bit stays the same:- ='[John Smith.xls]xxxxx'!$A$2

but the xxxxx part is picked up from cell al on the current worksheet sort
of like:- ='[John Smith.xls]&A1&'!$A$2

A1 Would = Wed 01st

Any Idea how I would do this???



Cheers

Mark

 

[Tom Ogilvy]

Not unless you use a macro and build a fixed formula in the cell.

--
Regards,
Tom Ogilvy

Excelant Tom,

Now me been a lazy git, is there a way to do it without opening the John
Smith workbook???


Cheers

Mark

=Indirect("'[John Smith.xls]"&A1&"'!$A$2")

But the John Smith.xls workbook would need to be open for the formula to
work.

--
Regards,
Tom Ogilvy

Hi All,

I have 10 workbooks that I need to check each day, the good news is I only
have to sheck one worksheet in each workbook, the worksheet is named the
previouse date.

This is how I would like it to work.

I have a new workbook with 11 worksheet one for each original workbook the
worksheets points at a given cell in a gived worksheet E.G ='[John
Smithl.xls]Wed 01st'!$A$2

The but I want the date part to change and pick this up from my

current
work

book E.G This bit stays the same:- ='[John Smith.xls]xxxxx'!$A$2

but the xxxxx part is picked up from cell al on the current worksheet sort
of like:- ='[John Smith.xls]&A1&'!$A$2

A1 Would = Wed 01st

Any Idea how I would do this???



Cheers

Mark

 

[Harlan Grove]

Not unless you use a macro and build a fixed formula in the cell.

Incorrect. It can be done with udf or add-in functions that use separate Excel
application instances.

http://www.google.com/[email protected]