Excel 2007 - Links between worksheets

[Abay]

Hello ... I use Excel at work from time to time, but not extensively, also I
am very new to Excel 2007.

I have worksheets that need to be linked. One is what we call a total
worksheet .. it contains copies of all smaller worksheets, 20 in all. I
need to have excel update the total worksheet each time one of the smaller
worksheets is altered. These are all separate files, each of the smaller
worksheets contains up to 30 rows of data.

Can I make a macro which will create the link between the total and smaller
(detail) worksheets. I expect I have to have the total worksheet open and
then open the detail worksheets one by one.

Apologies if this is not very clear & any help that would cut down the
manual labour would be most appreciated.

Abay

 

[meh2030]

Hello ... I use Excel at work from time to time, but not extensively, also I
am very new to Excel 2007.

I have worksheets that need to be linked.  One is what we call a total
worksheet .. it contains copies of all smaller worksheets, 20 in all.  I
need to have excel update the total worksheet each time one of the smaller
worksheets is altered.  These are all separate files, each of the smaller
worksheets contains up to 30 rows of data.

Can I make a macro which will create the link between the total and smaller
(detail) worksheets.  I expect I have to have the total worksheet open and
then open the detail worksheets one by one.

Apologies if this is not very clear & any help that would cut down the
manual labour would be most appreciated.

Abay

Abay,

I've done a lot of financial modelling as an investment banking
analyst, and I'm not a fan of linking a spreadsheet to other
independent spreadsheets. However, if this is your chosen method,
then when you open your "total" worksheet, select the "Data" tab,
select "Edit Links," and then select "Update Values." You could
create a macro to open each smaller workbook and then update the
values, but I think updating the values each time the "total"
worksheet is opened is the best and most painless option. (You can
even change the "Startup Prompt" in the "Edit Links" dialog box if you
so desire, depending on your preferences).

Best,

Matthew Herbert

 

[Abay]

Many thanks for your reply, which is much appreciated .. not my decision to
set it up this way .. I inherited it ... will check out the options you
mention ...thanks again.

abay

Hello ... I use Excel at work from time to time, but not extensively, also
I
am very new to Excel 2007.

I have worksheets that need to be linked. One is what we call a total
worksheet .. it contains copies of all smaller worksheets, 20 in all. I
need to have excel update the total worksheet each time one of the smaller
worksheets is altered. These are all separate files, each of the smaller
worksheets contains up to 30 rows of data.

Can I make a macro which will create the link between the total and
smaller
(detail) worksheets. I expect I have to have the total worksheet open and
then open the detail worksheets one by one.

Apologies if this is not very clear & any help that would cut down the
manual labour would be most appreciated.

Abay

Abay,

I've done a lot of financial modelling as an investment banking
analyst, and I'm not a fan of linking a spreadsheet to other
independent spreadsheets. However, if this is your chosen method,
then when you open your "total" worksheet, select the "Data" tab,
select "Edit Links," and then select "Update Values." You could
create a macro to open each smaller workbook and then update the
values, but I think updating the values each time the "total"
worksheet is opened is the best and most painless option. (You can
even change the "Startup Prompt" in the "Edit Links" dialog box if you
so desire, depending on your preferences).

Best,

Matthew Herbert