Copying data and increasing formula

[Mark]

I am creating a simple spreadsheet which pulls data from another. The first
(diary) has the names of operatives and the job numbers where they are
working, each week of the year is represented by a worksheet ie: week 17,
week 18 etc etc. The second (timesheet) takes the job numbers and enters them
onto a time sheet. This I can do by using (='[Diary.xls]Week 17'!$B$2) which
is pretty straight forward. The problem I have is in the second (timesheet)
spreadsheet, is there any way I can copy the second (timesheet) spreadsheet
increasing the week number ie: (='[Diary.xls]Week 18'!$B$2)
At the moment when I copy the worksheet the same formula appears and I have
to manually change it over and over again.

I hope this makes sense

Any help would be greatly appreciated.

 

[Pete_UK]

Instead of manually changing each formula, you can highlight all the
cells with the formula in and do CTRL-H (or Edit | Replace):

Find What: Week 17
Replace With: Week 18
Click Replace All.

Another way would be to use the INDIRECT function, as long as the
Diary.xls file is open at the same time:

=INDIRECT("'[Diary.xls]Week "&A1&"'!$B$2")

where A1 contains 17 for the Week 17 sheet, and you can change this to
18 for the next sheet.

Hope this helps.

Pete

 

[Bob Phillips]

Put the week number in a cell, say A1, and use

=INDIRECT("'[Diary.xls]Week "&A1&"'!$B$2")

then all you need to do is change A1 after copying.

 

[Mark]

Thanks guys, much appreciated

 

[Pete_UK]

You're welcome, Mark.

Pete