Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
static reference in formula to worksheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
static reference in formula to worksheet
See your other post.
Shmuli Wenger wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need formula to reference a cell in previous worksheet | Excel Worksheet Functions | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Changing a Worksheet Reference Formula using a drop down box | Excel Discussion (Misc queries) | |||
What is the formula to reference a cell in a different worksheet? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |