Linking to external workbooks with changing sheet names

  • Thread starter Thread starter Sh0t2bts
  • Start date Start date
S

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
 
=Indirect("'[John Smith.xls]"&A1&"'!$A$2")

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

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


Cheers

Mark



Tom Ogilvy said:
=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


Sh0t2bts said:
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
Click to expand...
Click to expand...
 
Not unless you use a macro and build a fixed formula in the cell.

--
Regards,
Tom Ogilvy

Sh0t2bts said:
Excelant Tom,

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


Cheers

Mark



Tom Ogilvy said:
=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


Sh0t2bts said:
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top