How to convert all 3d references to normal references in a workboo

D

Dima

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
 
J

Jim Rech

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
| 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
 
D

Dima

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
R

Rick Rothstein \(MVP - VB\)

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) said:
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 said:
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
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

One more thing I should mention... the macro I posted physically changes the
formulas in the cells and those changes cannot be undone... so, if this is
not what you actually wanted, do NOT run the macro against any live formulas
(that is, test it on a COPY of your worksheet first). If you need this code
to output a value for inclusion in a different cell than the one that
contains the formula, it would be very easy to convert the code to a UDF
(user defined function)... just let me know and I rewrite it into a UDF for
you.

Rick


Rick Rothstein (MVP - VB) said:
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) said:
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 said:
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
:

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
| 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
Click to expand...
Click to expand...
Click to expand...
 
D

Dima

Thank you, Rick!
Rick Rothstein (MVP - VB) said:
One more thing I should mention... the macro I posted physically changes the
formulas in the cells and those changes cannot be undone... so, if this is
not what you actually wanted, do NOT run the macro against any live formulas
(that is, test it on a COPY of your worksheet first). If you need this code
to output a value for inclusion in a different cell than the one that
contains the formula, it would be very easy to convert the code to a UDF
(user defined function)... just let me know and I rewrite it into a UDF for
you.

Rick


Rick Rothstein (MVP - VB) said:
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) said:
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



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
:

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
| 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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert selected formula references from relative to absolute 6
How to convert range names (about 100) to cell references? 1
convert relative cell references to absolute cell references via amacro? 1
Make all values of a 3D named range appear on summary sheet 1
Range of cells: Convert relative reference into absolute 5
How do I make cell references return to normal? 4
trace all dependents 1
how to graph an equation of a plane in 3D 1

Top