Give this macro a try; place it in a Module (Insert/Module from the VBA menu
bar)...

Sub Expand3dFormula()
Dim RE As Object
Dim C As Range
Dim WS As Worksheet
Dim X As Long
Dim Y As Long
Dim StartIndex As Long
Dim EndIndex As Long
Dim ShtRef As String
Dim ShtStart As String
Dim ShtEnd As String
Dim CelRef As String
Dim WSname As String
Dim Reference As String
Dim REsub As String
Dim Parts() As String
For Each C In Selection
Set RE = CreateObject("VBScript.RegExp")
RE.Global = True
RE.Pattern = "[^:!a-zA-Z0-9=_]"
REsub = RE.Replace(C.Formula & "=", " ")
RE.Pattern = " "
REsub = Trim(RE.Replace(REsub, " "))
Set RE = Nothing
Parts = Split(REsub)
For X = 1 To UBound(Parts) - 1
Reference = ""
If Parts(X) Like "*:*!*" Then
ShtRef = Left(Parts(X), InStr(Parts(X), "!") - 1)
CelRef = Mid(Parts(X), InStr(Parts(X), "!") + 1)
ShtStart = Left(ShtRef, InStr(ShtRef, ":") - 1)
ShtEnd = Mid(ShtRef, InStr(ShtRef, ":") + 1)
For Each WS In Worksheets
If WS.Name = ShtStart Then StartIndex = WS.Index
If WS.Name = ShtEnd Then EndIndex = WS.Index
Next
For Y = StartIndex To EndIndex
WSname = Worksheets(Y).Name
If WSname Like "*[!a-zA-Z_]*" Then WSname = "'" & WSname & "'"
Reference = Reference & WSname & "!" & CelRef
If Y < EndIndex Then Reference = Reference & ","
Next
C.Formula = Replace(C.Formula, Parts(X), Reference)
End If
Next
Next
End Sub

Rick



"Dima" wrote in message
...
Thanks Jim for replying!
Yes, I mean the way to convert this:
=SUM(Sheet2:Sheet4!A1)
to
=SUM(Sheet2!A1,Sheet3!A1,Sheet4!A1)
Dmitry
"Jim Rech" wrote:

If you mean that you'd like a way to convert this:

=SUM(Sheet2:Sheet4!A1)

to

=SUM(Sheet2!A1,Sheet3!A1,Sheet4!A1)

I don't think there is anything in Excel that does it unfortunately.

--
Jim
"Dima" wrote in message
...
| Hello!
| How to convert all 3d references to normal references in a workbook?
| I want to move the ranges to which the 3d references refer, but the 3d
| references do not follow the movement of the precedents inside the 3d
range
| and become corrupted.
| Regards,
| Dmitry