Static reference to worksheet in formula

[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]

You could convert the formulas to strings, copy the worksheet, and convert
those strings back to formulas.

I like to do this:

Select all the cells
edit|Replace
what: = (equal sign)
with: $$$$$=
replace all

Then the copy.

And reverse the process:

Select all the cells
edit|Replace
what: $$$$$=
with: =
replace all

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

 

[Bernard Liengme]

Q2:
If in B1, C1, etc you have month names like "Jan", "Feb", then this formula
=INDIRECT(B1&"!B23")
will produce the required series

Q1:
How about
1) Saving Budget2006.XLS
2) Then do a Save As, naming it Budget2007.XLS
3) Then clear the data from the 12 monthly sheets but grouping them and
deleting cells
This will prevent the path being added to references as happens when you do
a copy.
best wishes

 

[Shmuli Wenger]

Thanks for the idea. Worked like a charm.

You could convert the formulas to strings, copy the worksheet, and convert
those strings back to formulas.

I like to do this:

Select all the cells
edit|Replace
what: = (equal sign)
with: $$$$$=
replace all

Then the copy.

And reverse the process:

Select all the cells
edit|Replace
what: $$$$$=
with: =
replace all

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