Excel Name Trick 8: Names Relative Cell Reference for Sheet

Hi,

I need a reference to a previous sheet where the cell reference is not
absolute - ie automatically updates/changes when I move the information
on the reference sheet. Ie:If I have a reference on Sheet "Week 30
2006" to ='Week 29 2006'!C6+1 and I add a row, it becomes ='Week 29
2006'!C7+1.

The problem is that if I use any of the UDF I have found on the web,
the cell reference doesn't change as I move/add information: eg

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = 0
Exit Function
End If
PrevSheet = Sheets(Application.Caller.Parent.Index -
1).Range(rg.Address).Value
End Function

'and in the worksheet, enter
'=PrevSheet(E3)
Then PrevSheet(E3) does not become PrevSheet(E4).

I've also tried with indirect:

Function PrevSheet()
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function

where the worksheet formula becomes =INDIRECT("'"&PrevSheet()&"'!C6")

Again - C6 is absolute.

Can anyone help me with this?

Thank you so much
Helen

Hello there,

I'm currently working in a very complex worksheet, which takes 15 minutes to calculate (despite my i7 970 cpu). Because I need it to speed up, I'm looking for the following:

See below; the values in the column "implied volatility" are calculated by means of a double condition
- look up value in column in different sheet (Option Data) equal to option ID, in this case W2
- match date in different sheet (Option Data) to date in column S
- Then give me the value in the same row a different column

Code:

=SMALL(IF((W$2='Option Data'!$H$2:$H$50343)*(S4='Option Data'!$B$2:$B$50343);'Option Data'!$G$2:$G$50343);1)

That works all fine, but is quite intensive for my PC.
So what I want to do is:
In the columns next to implied volatility, i.e. "trade volume" and "open int" I want it to show the value in the column next(right) to the value of the implied volatility for which I use the code above.
So instead of using the same small(if()) function, I want to make it easier for the CPU and just use the relative position of the cell which was just looked up in the (optiondata) sheet, and use the cell in the column next to it. Possibly some kind of Offset function might help.

Note: In this example I entered random values in the columns Trade Volume & Open Interest, so don't get confused by that.



Model R S T U V W 1 9-1-2008 3 2 Stock price on t=-20 37850 0.407095583 32763814 3 Date from earnings announcement Date Implied Vol Trade Volu Open Int Abnormal Imp Vol 4 -44 5-11-2007 0.39625 354 345 5 -43 6-11-2007 0.388605 354 23 6 -42 7-11-2007 0.403641 4444 32 7 -41 8-11-2007 0.402985 3 4 Excel 2007

Worksheet Formulas Cell Formula R1 =IF( S1=S48,"","DATUMFOUT" ) T2 =AVERAGE( T4:T27 )
Array Formulas Cell Formula S1 =DATEVALUE( IF( INDEX( 'Earnings Data'!$E:$E,( MATCH( $B$3,'Earnings Data'!$E$1:$E$1514,0 )+T$1 ) )=Model!$B$3,( INDEX( 'Earnings Data'!$O:$O,( MATCH( $B$3,'Earnings Data'!$E$1:$E$1514,0 )+T$1 ) ) ) ) ) S2 =1000*( INDEX( 'Stock Prices'!$E$2:$E$70515,MATCH( 1,( Model!S28='Stock Prices'!$B$2:$B$70515 )*( Model!$B$3='Stock Prices'!$C$2:$C$70515 ),0 ) ) ) W2 =INDEX( 'Option Data'!$H:$H,SMALL( IF( ( 'Option Data'!$E:$E>S$2 )*( 'Option Data'!$C:$C>S$63 )*( 'Option Data'!$B:$B=S4 ),ROW( 'Option Data'!$A:$A ) ),1 ) ) S4 =OFFSET( 'Stock Prices'!$B$1,( MATCH( S$1,'Stock Prices'!$B:$B )+R4-1 ),0 ) T4 =SMALL( IF( ( W$2='Option Data'!$H$2:$H$50343 )*( S4='Option Data'!$B$2:$B$50343 ),'Option Data'!$G$2:$G$50343 ),1 ) S5 =OFFSET( 'Stock Prices'!$B$1,( MATCH( S$1,'Stock Prices'!$B:$B )+R5-1 ),0 ) T5 =SMALL( IF( ( W$2='Option Data'!$H$2:$H$50343 )*( S5='Option Data'!$B$2:$B$50343 ),'Option Data'!$G$2:$G$50343 ),1 ) S6 =OFFSET( 'Stock Prices'!$B$1,( MATCH( S$1,'Stock Prices'!$B:$B )+R6-1 ),0 ) T6 =SMALL( IF( ( W$2='Option Data'!$H$2:$H$50343 )*( S6='Option Data'!$B$2:$B$50343 ),'Option Data'!$G$2:$G$50343 ),1 ) S7 =OFFSET( 'Stock Prices'!$B$1,( MATCH( S$1,'Stock Prices'!$B:$B )+R7-1 ),0 ) T7 =SMALL( IF( ( W$2='Option Data'!$H$2:$H$50343 )*( S7='Option Data'!$B$2:$B$50343 ),'Option Data'!$G$2:$G$50343 ),1 ) Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


The Option Data sheet from which the Model sheet is extracting data looks like as follows (not including all the 1000000 rows below it):

Option Data A B C D E F G H I J 1 secid date exdate cp_flag strike_price best_bid volume open_interest impl_volatility optionid 2 100972 1-9-2006 20-1-2007 C 20000 28.4 0 317 21579610 3 100972 1-9-2006 20-1-2007 C 30000 18.6 0 871 21579613 4 100972 1-9-2006 20-1-2007 C 35000 13.7 0 1859 0.261059 21583529 5 100972 1-9-2006 20-1-2007 C 40000 9 11 5660 0.240411 21579611 6 100972 1-9-2006 20-1-2007 C 45000 4.7 0 16325 0.20472 21583315 Excel 2007





My hope is that it will increase the speed of the process, since I have to do this calculation 100 times.

I need to reference data from one sheet to the other and the sheet name can change and the cell needs to be relative so if a line is added in the other sheet the reference is not lost.
I tried using Indirect with the name of the sheet in cell C9, which changes,
=INDIRECT("'"&$C$9&"'!"&"D23")
but if a row was added in the sheet, D23 needs to change to D24 to pull the right data but it doesn't.
I tried Concatenate but I can't get the syntax right.

Any help would be greatly appreciated, since this is the last problem I need to fix in a file they have to have asap. UUghghh

Excel 2002, XP SP2
In preparing a sequence of annual reports on a standard template, I wish to
prepare a table that refers to the previous year's results, in the previous
sheet in a workbook. In copying the table to the next year, I want the sheet
reference to be relative to the working one, i.e. the previous sheet(year).
However when I copy the table including cell references to the succeeding
sheet, the original sheet reference remains the same.

One can declare cell references to be relative or absolute. How does one do
the same for sheet references?

The answer may be in the help files, but I cannot find it.

I have a workbook that gathers and displays data from six other workbooks. Changes in any of the six source workbooks automatically reflect in the summary workbook, whether the source workbooks are open or not. It all works fine, but is unwieldy if I want to change the source workbooks.

So I'd like to make 'dynamic' links to make it easy to change the filenames of the six source workbooks by typing the six source filenames into the summary workbook.

Right now, I have to type in, for example =[Book1.xlsx]Sheet1!$A$1 into cell B1 to get the source data cell A1 into the summary workbook. However, I'd like the Book1.xlsx part of the formula to come from another cell in the summary workbook, so that I can easily change the referenced source workbook. I suppose an indirect reference is what I'm looking for, but I can't seem to get that to work. So, I could type Book1.xlsx into cell C1 like: =[INDIRECT(C1)]Sheet1!$A$1. But that doesn't work.

Any help appreciated!

I am often confused by the behavior of my Excel marocs when it comes to the degree to which I need to specify sheet names.

It seems that at times I can Select or Activate a sheet, and then everything I want to do happens on that sheet without error. At other times, it seems that I am getting errors that I can make "go away" by re-specifying a sheet name. Sometimes one line works without explicilty specifying the sheet, but the next line fails. (I am sure that it has something to do with what I am doing in the line of code, but if I knew what that was, I wouldn't have the question. I have no immediate example... I am about to write a new procedure and I jsut want to use good programming conventions to get started.)

My question is, can someone give me a short primer on the basic convention for referencing sheets in macros? Is it good convention to repeatedly call out Sheets(mysht), or something else?

OPTION 1: Start everything with:
Sheets(shtname).Range(...

OPTION 2: Activate a sheet, and then refer to Active Sheet.
Sheets(shtname).Activate
Activesheet.Range(...

OPTION 3 (which seems to get me into trouble...) Select a sheet, and then hope that Excel stays focused on that sheet.
Sheets(shtname).Select
Range(...

Thanks!
Mike

Hi all,

Is there a way to refer to a sheet by number, say sheet1, in a formula, regardless of what the sheet is named?

No luck with search...

Thanks!

Hi,

I have this formula in Cell B1: =1*Sheet2!$C$2+Sheet2!$C$3

I have a column starting in A1 like this:

A1=Sheet2
A2=Sheet3
A3=Sheet4

Can I somehow change my formula, so I can drag it down with references to A1, A2 and A3?

That is, instead of =1*Sheet2!$C$2+Sheet2!$C$3 I want in
Cell B2: =1*Sheet3!$C$2+Sheet3!$C$3
Cell B3: =1*Sheet4!$C$2+Sheet4!$C$3

Many thanks
Cusuma

Been searching but haven't found an answer to this one.

From a formula, how can I reference other sheets in my workbook via a relative reference as opposed to needing to know the sheet name? I know I can write a macro, but looking for a non-VBA solution.

When a formula is copied, a Relative reference is used. A Relative reference is the distance, in rows and columns, between the reference and the cell containing the formula.
For example: In cell A1, enter the number 100, and in cell B1, enter the formula =A1. Cell B1 is in a one column distance from cell A1. When the formula is copied from cell B1 to cell B10, the distance between the reference and the cell containing the formula remains one column, and the formula in cell B10 is =A10.



Select cell B1 (as in previous example). In the Formula Bar, select A1, (or just click on A1). Then press F4. The result is =$A$1.
Copy the contents of cell B1 to cell B10. Notice that the formula does not change, the formula reference remains constant as =$A$1.



The F4 keyboard shortcut has four combinations:
State 1: Absolute reference to both column and row, =$A$1
State 2: Relative reference (column) and Absolute reference (row), =A$1
State 3: Absolute reference (column) and Relative reference (row), =$A1
State 4: Relative reference to both column and row, =A1