Link's between Worksheets and Workbooks

[Ian Morrin]

Hi,

I am trying to set up work books and work sheets to update information
automatically and can't work out which is the best way to do this.

Initially I have a workbook (A) which has 52 sheets, one for each week. I
need to carry some of the information over each week - what is the easiest
way of doing this?

I also have a number of other workbooks (B - E) with information on (updated
daily) that I need to copy across each week as well.

The information on workbooks B - E is updated daily but I need a snapshot
each week to enter into the weekly report on Workbook 'A'

Apart from the obvious manually copying the information each week can I
automate this?

Thanks

Ian

 

[Bob Greenblatt]

Hi,

I am trying to set up work books and work sheets to update information
automatically and can't work out which is the best way to do this.

Initially I have a workbook (A) which has 52 sheets, one for each week. I
need to carry some of the information over each week - what is the easiest
way of doing this?

I also have a number of other workbooks (B - E) with information on (updated
daily) that I need to copy across each week as well.

The information on workbooks B - E is updated daily but I need a snapshot
each week to enter into the weekly report on Workbook 'A'

Apart from the obvious manually copying the information each week can I
automate this?

Thanks

Ian

To get information from one worksheet to another (regardless of which
workbook it is in) simply insert a formula. For example a formula in a cell
on workbook A worksheet 1, to get information from workbook B worksheet 2
cell d3 would look like: ='[workbookb]worksheet2'!D3. The easiest way to do
this is to select the destination cell, press "=" then switch to the source
worksheet and click on the cell.

 

[Ian Morrin]

Thanks for that,

Is there a way to get it to repeat through all 52 sheets of the workbook
rather than me having to write in the formula each time?

Hi,

I am trying to set up work books and work sheets to update information
automatically and can't work out which is the best way to do this.

Initially I have a workbook (A) which has 52 sheets, one for each week. I
need to carry some of the information over each week - what is the easiest
way of doing this?

I also have a number of other workbooks (B - E) with information on (updated
daily) that I need to copy across each week as well.

The information on workbooks B - E is updated daily but I need a snapshot
each week to enter into the weekly report on Workbook 'A'

Apart from the obvious manually copying the information each week can I
automate this?

Thanks

Ian

To get information from one worksheet to another (regardless of which
workbook it is in) simply insert a formula. For example a formula in a cell
on workbook A worksheet 1, to get information from workbook B worksheet 2
cell d3 would look like: ='[workbookb]worksheet2'!D3. The easiest way to do
this is to select the destination cell, press "=" then switch to the source
worksheet and click on the cell.

 

[JE McGimpsey]

Thanks for that,

Is there a way to get it to repeat through all 52 sheets of the workbook
rather than me having to write in the formula each time?

One way:

First, put a User Defined Function (UDF) like my PrevSheet() method in a
regular code module in your workbook:

http://www.mcgimpsey.com/excel/udfs/prevsheet.html

Next, select your second through 52nd sheet (select the left-most, then
shift-click the right-most). Enter the formula in your desired cell,
e.g.:

=PrevSheet(B10)

After entering the common formulae, make sure you ctrl-click a worksheet
tab and choose Ungroup Sheets from the contextual menu.