I should have mentioned, you need to select one or more cells (the macro can
handle contiguous as well as non-contiguous ranges of cells) and then run
the macro; it will then expand every 3D reference (even differing multiple
3D references within a single cell's formula) in every cell within the
selection.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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