static reference in formula to worksheet

S

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
 
D

Dave Peterson

See your other post.

Shmuli said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top