ExcelBanter - View Single Post - How to convert all 3d references to normal references in a workboo

Dima

Thank you, Rick!
"Rick Rothstein (MVP - VB)" wrote:

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)" wrote in
message ...
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