-2

This is a hack-y Excel question, certainly not standard procedure. Apologies in advance.

Inside a workbook, I have a financial model. It spans three sheets: Revenue and Expense, which both read information from Inputs. Together, the sheets form a complete model.

I want to expand my workbook by creating a duplicate model alongside my first one. The idea is to have side-by-side scenarios that I can compare. 2 sets of inputs, 2 sets of expense calcs, and 2 sets of revenue calcs, each set side-by-side on its respective tab.

Normally, I would just copy the formulas over and 'bam' I've got a duplicate model. Unfortunately, I can't do this because I used a ton of $ characters, locking my cell references. Copying the formulas in Revenue off to the right wouldn't change which cells the formulas reference on Inputs tab. The model is large enough that it would take hours to remove the cell reference locks from each formula manually.

My current plan is to use VBA to remove all of the $ characters from formulas and then go ahead with the copy pasting method.

  1. Will this work?
  2. How can I remove a specific character from formulas using VBA?
Improve this question
3
  • 2
    Sounds like a good case for the principle of "design before you implement". ;) I believe there's a formula member of the Cell object you can reference by Range. I'm not at a computer, so I can't type anything up for you. Commented Jan 19, 2017 at 0:48
  • How about using "Move or Copy" to duplicate the three tabs? Then modify one set of the three tabs to form your new scenario. If you need to see the two models simultaneously use View > New Window -- you can use those View options to see two tabs side-by-side. Commented Jan 19, 2017 at 1:20
  • It is absolutely a case of "design before you implement"! Unfortunately, this was a condition that the bossman passed down to me after I'd completed the model. . . . Commented Jan 19, 2017 at 14:59

2 Answers 2

Reset to default
5

You can press ctrl+' this will change all the cells from showing their value to showing their formula. In this view you can press ctrl+H to replace all $ with nothing and click "Replace All".

So no need to code it all in vba, which would have been possible too, but probably a bit more complicated.

This will remove all the dollar signs you wanted to remove.

Pay attention though if you make any edits extrapolating formulas in the new dollar-sign-less sheet, that it will probably be incorrect for it will also extrapolate the set references which should contain a $.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is exactly what I was looking for. Thank you!
Note that ctrl + ' didn't work for me; I selected all cells and and clicked 'Show Formulas' under the 'Formulas' tab, per this link: ablebits.com/office-addins-blog/2016/01/13/show-formulas-excel
@rvictordelta Hi I'm glad that it worked for you! I had a little trouble formatting the correct shortcut above, with ' I meant `, the backtick or backquote in stead of the regular quotation mark '. As in: zeigen.com/shortcuts/20.. in the future you could still try that if you would like to switch to formula view with the shortcuts, if it is on your keyboard, for me it works in Excel 2016. Or it might not be available on your keyboard layout, in that case, well done figuring out the manual workaround!
1

For removing the $ in external link paths in Excel -

Sub ExtLinks_RelativePaths()

This macro converts external links in selected cells to relative links by removing the $ from the cell reference ' Source: [email protected]

 Dim myCells As Range, curCell As Range
 Dim myVal As Boolean

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set myCells = ActiveSheet.UsedRange

    On Error Resume Next
    For Each curCell In myCells
        curCell.Select
        myVal = IsFormula(curCell)
            If myVal = True Then
                With Selection
                    .replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
                        xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                End With
            End If
    Next curCell

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    MsgBox ("Formulas on the active sheet now have relative references.")
End Sub

Function IsFormula(cell_ref As Range)
    IsFormula = cell_ref.HasFormula
End Function
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.