easily change external references
 

Hi...

My question has probably been answered somewhere in the community; but
either I cant find it or I'm just confused.

I have 2 workbooks, Workbook A and Workbook B

Every week in Workbook A, new data is added to a new worksheet. In Workbook
B, I need to reference several cells from Workbook A. But with each new week,
I need to change the sheet name in all of the references to get the weeks
data. The only thing that will be changing in Workbook B is the sheet name in
each external reference.

Is there any way to easily change the sheet name in each external references
in Workbook B without doing anything to Workbook A?

easily change external references
 

Hi


ReplaceAll (OldSheetName NewSheetName)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"MikeyS" wrote in message
...
Hi...

My question has probably been answered somewhere in the community; but
either I cant find it or I'm just confused.

I have 2 workbooks, Workbook A and Workbook B

Every week in Workbook A, new data is added to a new worksheet. In
Workbook
B, I need to reference several cells from Workbook A. But with each new
week,
I need to change the sheet name in all of the references to get the weeks
data. The only thing that will be changing in Workbook B is the sheet name
in
each external reference.

Is there any way to easily change the sheet name in each external
references
in Workbook B without doing anything to Workbook A?

easily change external references
 

If you've got many references to change, the INDIRECT function sounds like
what you need. Use one cell for the name of the relevant sheet, & pick that
up in the link formulae.
--
David Biddulph

"MikeyS" wrote in message
...
Hi...

My question has probably been answered somewhere in the community; but
either I cant find it or I'm just confused.

I have 2 workbooks, Workbook A and Workbook B

Every week in Workbook A, new data is added to a new worksheet. In
Workbook
B, I need to reference several cells from Workbook A. But with each new
week,
I need to change the sheet name in all of the references to get the weeks
data. The only thing that will be changing in Workbook B is the sheet name
in
each external reference.

Is there any way to easily change the sheet name in each external
references
in Workbook B without doing anything to Workbook A?

easily change external references
 

Thanks...

That does work but it also has a lot of steps to it. What I'm looking for
is a bit more, I guess, automated and easily like a click of a button,
choosing which sheet and then the cells are updated. Do you know if this can
be done?

"Arvi Laanemets" wrote:

Hi


ReplaceAll (OldSheetName NewSheetName)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"MikeyS" wrote in message
...
Hi...

My question has probably been answered somewhere in the community; but
either I cant find it or I'm just confused.

I have 2 workbooks, Workbook A and Workbook B

Every week in Workbook A, new data is added to a new worksheet. In
Workbook
B, I need to reference several cells from Workbook A. But with each new
week,
I need to change the sheet name in all of the references to get the weeks
data. The only thing that will be changing in Workbook B is the sheet name
in
each external reference.

Is there any way to easily change the sheet name in each external
references
in Workbook B without doing anything to Workbook A?

easily change external references
 

Ok my question now is, how exactly do I do that? I've tried looking at the
information on INDIRECT, but don't get how to actually code it right with the
external reference

Here's an example of what I have

WORKBOOK A has data in
WORKSHEET 1 in cells:
A1 D1
A5 D5
A9 D9
A13 D13

WORKBOOK B need to bring in those values from WORKBOOK A into

A1 B1
A2 B2
A3 B3
A4 B4

and then in WORKBOOK B, I need to easily change the references from
WORKSHEET 1 to WORKSHEET 2 (then 3 then 4, etc every new week) of WORKBOOK A

Also is there a very easy "automated" way to do this too?

Thanks for all of your help!!!


"David Biddulph" wrote:

If you've got many references to change, the INDIRECT function sounds like
what you need. Use one cell for the name of the relevant sheet, & pick that
up in the link formulae.
--
David Biddulph

"MikeyS" wrote in message
...
Hi...

My question has probably been answered somewhere in the community; but
either I cant find it or I'm just confused.

I have 2 workbooks, Workbook A and Workbook B

Every week in Workbook A, new data is added to a new worksheet. In
Workbook
B, I need to reference several cells from Workbook A. But with each new
week,
I need to change the sheet name in all of the references to get the weeks
data. The only thing that will be changing in Workbook B is the sheet name
in
each external reference.

Is there any way to easily change the sheet name in each external
references
in Workbook B without doing anything to Workbook A?

easily change external references
 

Hi

It's possible for sure, but you have to wait for somebody else to help you
with it - I don't have the code ready, and I don't have enough of time for
it at moment.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"MikeyS" wrote in message
...
Thanks...

That does work but it also has a lot of steps to it. What I'm looking for
is a bit more, I guess, automated and easily like a click of a button,
choosing which sheet and then the cells are updated. Do you know if this
can
be done?

"Arvi Laanemets" wrote:

Hi


ReplaceAll (OldSheetName NewSheetName)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"MikeyS" wrote in message
...
Hi...

My question has probably been answered somewhere in the community; but
either I cant find it or I'm just confused.

I have 2 workbooks, Workbook A and Workbook B

Every week in Workbook A, new data is added to a new worksheet. In
Workbook
B, I need to reference several cells from Workbook A. But with each new
week,
I need to change the sheet name in all of the references to get the
weeks
data. The only thing that will be changing in Workbook B is the sheet
name
in
each external reference.

Is there any way to easily change the sheet name in each external
references
in Workbook B without doing anything to Workbook A?