Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 90
Default How to convert all 3d references to normal references in a workboo

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
  #2   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,718
Default How to convert all 3d references to normal references in a workboo

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


  #3   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 90
Default How to convert all 3d references to normal references in a wor

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



  #4   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How to convert all 3d references to normal references in a wor

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




  #5   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How to convert all 3d references to normal references in a wor

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







  #6   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How to convert all 3d references to normal references in a wor

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






  #7   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 90
Default How to convert all 3d references to normal references in a wor

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert relative cell references to absolute cell references via amacro? Dave F[_2_] Excel Discussion (Misc queries) 1 May 15th 08 04:43 PM
Help with converting a block of cells with Absolute and mixed references to relative references Vulcan Excel Worksheet Functions 3 December 14th 07 12:43 AM
Excel Chart-data references link another worksheet in same workboo Wanda House Excel Discussion (Misc queries) 0 July 25th 07 03:40 PM
Excel Cell References are different to normal Diggsy Excel Worksheet Functions 1 April 5th 06 11:49 AM
How can I create drop-down lists with references in other workboo. Cezar DUMITRIU Excel Worksheet Functions 1 March 30th 05 02:15 PM


All times are GMT +1. The time now is 11:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"