A 3D reference is a formula that uses the same cells on a series of worksheets.
For example this formula:
=SUM(SheetA:SheetC!B3:B5)sums the cells B3 to B5 on all the worksheets from a worksheet named Sheet A to the worksheet named Sheet C (in this example I had three sheets - the third being SheetB).
The formula was on a fourth Worksheet 'Sheet1'.
To me, converting this to a 2D reference would mean having three formulas:
The following Macro works with a selection of cells, and takes 3D formulas and creates a series of single sheet (2D) formulas in the cells below each 3D formula.
There is no check that the cells in the rows below the 3D formulas are empty - any existing data will be overwritten.
The macro will ignore cells without formulas and it will ignore cells with formulas that do not reference another worksheet.
There are weaknesses with this, as it errors out if one of the cells in the selection refers to a single worksheet, i.e. it is not a 3D formula but it is referencing a cell on another worksheet.
The Macro does not attempt to recognize references to other workbooks and likely will error out if one is present in the selection.
I have only tested this with the SUM() function, but it should work on other simple functions with the same formula structure.
Private Sub ThreeDToTwoD()
Dim rngCell As Range
Dim strFmlaAddr As String
Dim strFormula As String
Dim strFunc As String
Dim strSheets As String
Dim strFirstSht As String
Dim strLastSht As String
Dim strCells As String
Dim intRow As Integer
Dim n As Integer
On Error GoTo ErrHnd
For Each rngCell In Selection
'only test cells with a formula and a worksheet name (has ! in formula)
If rngCell.HasFormula = True And InStr(1, rngCell.Formula, "!") > 0 Then
'get 3D formula and get its component parts
strFormula = Right(rngCell.Formula, Len(rngCell.Formula) - 1)
strFunc = Left(strFormula, InStr(1, strFormula, "("))
strSheets = Mid(strFormula, InStr(1, strFormula, "(") + 1, _
InStr(1, strFormula, "!") - InStr(1, strFormula, "(") - 1)
strFirstSht = Left(strSheets, InStr(1, strSheets, ":") - 1)
strLastSht = Right(strSheets, Len(strSheets) - InStr(1, strSheets, ":"))
strCells = Right(strFormula, Len(strFormula) - InStr(1, strFormula, "!"))
'set first row offset for new 2D formulas
intRow = 1
'loop through all the worksheet names in the 3D range
For n = Worksheets(strFirstSht).Index To Worksheets(strLastSht).Index
'create 2D formulas below 3D formula
rngCell.Offset(intRow, 0).Formula = _
"=" & strFunc & Worksheets(n).Name & "!" & strCells
intRow = intRow + 1
This would go into a standard module (not a Class module) attached to the workbook.
As this is something that is unlikely to be run repeatedly on one workbook, it is not worth creating a command button to run it from. Just run it with f5 from the Visual Basic window. (If you want to try this and need more advice on macros, please ask)
If you try this macro, please note that it should be tested on a copy of your workbook, and always make a backup before running it as there is no undo function for macros.