Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Absolute sheet references

138 views
Skip to first unread message

Will Fleenor

unread,
Oct 10, 2001, 12:24:19 PM10/10/01
to
Sheet references in excel formula are absolute by default. This is a
problem when copying formula down through sheets. Is there any way to make
sheet references in formula relative references (i.e. always the sheet
immediately above the current sheet)?

Thanks, Will
wi...@k2e.com


Myrna Larson

unread,
Oct 10, 2001, 3:28:08 PM10/10/01
to
Only by writing a VBA macro.

Short of this, if you've used the default sheet names, i.e. Sheet1, Sheet2,
etc, then after copying your formulas to another sheet, use search and replace
to change, e.g. Sheet3 to Sheet4.

Peo Sjoblom

unread,
Oct 10, 2001, 4:16:21 PM10/10/01
to
If the sheets use their default names and numbering, then this formula (on a saved file) will return the
the cell contents of A1 from the previous sheet, can be used from Sheet2 and onward (or rather it will return an error used on
sheet1)..

=INDIRECT("Sheet"&SUBSTITUTE(RIGHT(CELL("filename",AV1),LEN(CELL("filename",AV1))-FIND("]",CELL("filename",AV1))),"Sheet","")-1&"!A1
")

change the -1 at the end to +1 if you want the sheet after the one you are on..

--
Regards,

Peo Sjoblom
(Use ROT13 to decode email)


"Myrna Larson" <myrna...@home.com> wrote in message news:m789st4cjfcghtgd8...@4ax.com...

Harlan Grove

unread,
Oct 10, 2001, 6:07:18 PM10/10/01
to
Myrna Larson <myrna...@home.com> wrote...

>Only by writing a VBA macro.
...

Very picky: it could be done with a user-defined function with cell
reference and sheet offset arguments.


Function showoff( _
r As Range, _
s As Long, _
Optional rr As Boolean = True) As Variant
'-----------------------------------------------------------
Application.Volatile

s = s + r.Parent.Index

If s < 1 Or s > Worksheets.Count Then
showoff = CVErr(xlErrRef)
ElseIf rr Then
Set showoff = Worksheets(s).Range(r.Address)
Else
showoff = Worksheets(s).Range(r.Address).Value
End If
End Function


The optional rr argument allows it to return a range reference (default) or
an array of the range's values.


Myrna Larson

unread,
Oct 11, 2001, 2:29:39 AM10/11/01
to
Yes, that's what I was talking about. I include "user-defined function[s]" in
the definition of VBA macros. I don't restrict the use of the word "macro" to
Sub procedures.
0 new messages