static reference in formula to worksheet

[Shmuli Wenger]

I have several Documents. For example one is called Budget 2007, Budget
2008. In each of these I have worksheets for each month, Jan, Feb, etc... I
also have a worksheet called Year. In the Year I have cells that reference
the month worksheets. For example I might have =Jan!$B23

My problem is that when I now copy the worksheet Year to the new file the
cell reference is ='C:\[BUDGET 2007.xls]Jan'!$B$23

I can't edit the links because I do have some formulas which I want to
reference previous years and otherwise will create lots of circular
references.

2 questions I have.

1) Is there a formula in Excel which I can put into =Jan!$B23 so that when I
copy the worksheet it doesn't insert the whole path since the Tab Jan does
exist in the new file?
2) Is there a formula in Excel which I can put into =Jan!$B23 so that if I
drag it accross to the cells to the right it will change the Jan to Feb,
Mar, etc... similiar to changing B to C and D

Thanks

Sam

 

[Dave Peterson]

See your other post.

I have several Documents. For example one is called Budget 2007, Budget
2008. In each of these I have worksheets for each month, Jan, Feb, etc... I
also have a worksheet called Year. In the Year I have cells that reference
the month worksheets. For example I might have =Jan!$B23

My problem is that when I now copy the worksheet Year to the new file the
cell reference is ='C:\[BUDGET 2007.xls]Jan'!$B$23

I can't edit the links because I do have some formulas which I want to
reference previous years and otherwise will create lots of circular
references.

2 questions I have.

1) Is there a formula in Excel which I can put into =Jan!$B23 so that when I
copy the worksheet it doesn't insert the whole path since the Tab Jan does
exist in the new file?
2) Is there a formula in Excel which I can put into =Jan!$B23 so that if I
drag it accross to the cells to the right it will change the Jan to Feb,
Mar, etc... similiar to changing B to C and D

Thanks

Sam