Results 1 to 2 of 2
2003-06-04, 13:17 #1
- Join Date
- Mar 2002
- Thanked 0 Times in 0 Posts
Relative sheet reference (97/SR-1)
This may have been answered in the past, but since the search function is disabled....
I have a file with several sheets. Named Jan-Dec (or 1-22, or whatever). I enter a formula in sheet 13 such as ='Jan'!$A$1. I would like to copy that formula to the next 11 cells in a row, with the sheet name being relative. Is this possible?
I know there is a way to make sheet references relative by this VBA function:
Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
But this only works if you copy across sheets, not cells. Can this be modified to do what I want? I'm not very good at VBA or macros, but have worked with them a little.
Thanks for your help.
2003-06-04, 13:37 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
Re: Relative sheet reference (97/SR-1)
You could use the INDIRECT function to get the sheet name. Just include a column and enter the sheet names. If col A has SHeet namestheis formula copied down the rows will put the value of A1 from each of the sheetnames listed in Col A:
You could make it even more "generic" by also using ADDRESS and adding info on row and col number:
will give the contents of A1 from the sheetname listed in cell A1
will give the contents of C2 from the sheetname listed in cell A3