Convert cell references to numbers within formula?

[treazon]

Hi all

Apologies if this has already been asked / answered but I could not find it anywhere.

I want to convert cell references within formulae to actual figures e.g. using the table below:

=SUM(B2:B8) arrives at 271.60

but I want to make the formula say =SUM(90.23+15.25+122.04+3.00+20.47+5.00+15.61), so I can overwrite the data each month, but then look at previous months' formulae and check how each figure is calculated. Is there any way of doing this easily without having to type the figures manually?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Journal desc[/TD]
[TD]Journal amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bank charges[/TD]
[TD]90.23[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bank charges[/TD]
[TD]15.25[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bank charges[/TD]
[TD]122.04[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bank charges[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Bank charges[/TD]
[TD]20.47[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Bank charges[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Bank charges[/TD]
[TD]15.61[/TD]
[/TR]
</tbody>[/TABLE]


Hopefully this makes sense...

Thanks in advance!

 

[Rick Rothstein]

Select the cell or cells you want to do this for and then give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub CheckCellReferences()
  Dim ShapeCount As Long, Arrow As Long, Link As Long, Addr As String, Frmla As String
  Dim Cell As Range, CurrentCell As Range, OriginalSheet As String, OriginalCell As String
  Application.ScreenUpdating = False
  OriginalSheet = ActiveSheet.Name
  OriginalCell = ActiveCell.Address
  ShapeCount = ActiveSheet.Shapes.Count
  For Each Cell In Selection
    Set CurrentCell = Cell
    Frmla = Replace(CurrentCell.Formula, "$", "")
    If CurrentCell.HasFormula Then
      CurrentCell.ShowPrecedents
      Link = 1
      For Arrow = 1 To ActiveSheet.Shapes.Count - ShapeCount
        On Error Resume Next
        Do
          CurrentCell.Parent.Activate
          CurrentCell.Activate
          Addr = CurrentCell.NavigateArrow(True, Arrow, Link).Address
          If Err.Number Then
            Link = 1
            Exit Do
          End If
            If OriginalSheet <> ActiveCell.Parent.Name Then
              Frmla = Replace(Frmla, ActiveCell.Parent.Name & "!" & ActiveCell.Address(0, 0), ActiveCell.Value)
              Frmla = Replace(Frmla, "'" & ActiveCell.Parent.Name & "'!" & ActiveCell.Address(0, 0), ActiveCell.Value)
            Else
              Frmla = Replace(Frmla, ActiveCell.Address(0, 0), ActiveCell.Value)
            End If
          Link = Link + 1
Continue:
        Loop
        Cell.Offset(, 1) = Frmla
      Next
      CurrentCell.ShowPrecedents Remove:=True
    End If
    Worksheets(OriginalSheet).Activate
    Range(OriginalCell).Activate
  Next
  Application.ScreenUpdating = False
End Sub[/td]
[/tr]
[/table]

NOTE: If your formula has a text value that looks like a cell reference, for example the A12 in "Serial Number A12-345", and one of the cell references in the formula is actually A12, then the A12 inside the text constant will be replace along with the actual cell reference in the formula... I do not know a way around this should it occur.

 

[treazon]

Thanks for replying so quickly. I've tried creating a new macro and pasting the above into the VBA code screen (although I'm not massively clued up on macros), selecting the column of numbers I want to sum, then running the macro but nothing seems to happen. Which cell would the result appear in?

Cheers

 

[treazon]

Actually, scratch that, it seems to be working if I apply it to the cell containing the =SUM formula. However, it only seems to work if I add each one individually (e.g. =SUM(V5,V6,V7,V8,V9,V10,V11,V12,V13,V14,V15)). Can it be tweaked so that it works using a colon for a range of cells (=SUM(V5:V15)) instead?

Thanks!

 

[treazon]

Bump - does anybody know enough about macros and VBA to tweak that macro to work for cell ranges (see above post) as well please?

 

[Rick Rothstein]

Actually, scratch that, it seems to be working if I apply it to the cell containing the =SUM formula. However, it only seems to work if I add each one individually (e.g. =SUM(V5,V6,V7,V8,V9,V10,V11,V12,V13,V14,V15)). Can it be tweaked so that it works using a colon for a range of cells (=SUM(V5:V15)) instead?

What would you want the output from =SUM(V5:V15) to be?

 

[treazon]

What would you want the output from =SUM(V5:V15) to be?

Using the original table as an example, I would want the output from '=SUM(B2:B8)' to be '=SUM(90.23+15.25+122.04+3.00+20.47+5.00+15.61)'.

It currently only seems to work if I use separate cell references (which involves clicking on each individual one) e.g. '=SUM(B2,B3,B4,B5,B6,B7,B8)', and is very time-consuming for large amounts of data!

Thanks in advance.

 

[Rick Rothstein]

Using the original table as an example, I would want the output from '=SUM(B2:B8)' to be '=SUM(90.23+15.25+122.04+3.00+20.47+5.00+15.61)'.

Okay, then what would you want the output to be from either of these...

=SUM(V5:V10000)

or

=SUM(A1:Z1000)

Are you starting to see a problem with what you are asking for as it relates to a generalize routine?

 

[treazon]

Okay, then what would you want the output to be from either of these...

=SUM(V5:V10000)

or

=SUM(A1:Z1000)

Are you starting to see a problem with what you are asking for as it relates to a generalize routine?

Hmmm, I'm not sure I see the issue, but this may be because my programming knowledge is very limited! I would have thought that 'V5:V1000' could be turned into 'V5+V6+V7...+V1000' with some simple coding? Similary, could 'A1:Z1000' not be converted to 'A1+A2+A3+A4...+A999+A1000+B1+B2...+Y1000+Z1+Z2...+Z1000' also?

Either way, I'm only ever going to need to sum one column at a time so I guess the latter one is irrelevant.