easily change external references

[Guest]

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?

 

[Arvi Laanemets]

Hi


ReplaceAll (OldSheetName > NewSheetName)

 

[David Biddulph]

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.

 

[Guest]

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?

 

[Guest]

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!!!

 

[Arvi Laanemets]

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.