Convert automatically excel table formulas from explicit cell reference to structured cell reference?

2
votes

I have an excel table with formulas such as 

=M9-C9*10-D9*10-C9*H9/100

I want to convert these formulas to use structured cell references that use the table column names in the formulas, like this fake formula:

=[Sales Amount]-[Profit]*10-[Costs]*10-[Profit]*[Risk]/100*10-[Capital]*[Gross]/100

Is there a way to automatically convert all the explicit column names (M9) to use structured references ([Sales Amount]).

It is error prone to go through and manually do it.

(Info about Using structured references with Excel tables)

2
excelvba
From what I understand from the article you reference, you have to do it manually. Or write a complex VB macro to do it (I would disadvice that as it can introduce more errors than you would like.) - Paul Ogilvie

2 Answers

0
votes

Edit: you could iterate through the explicit cell-references you want to replace. Make sure to backup your file before playing around with this!

Sub replaceformulas2()
    Dim c As Object

    For Each c In Selection.Cells
        With c

            .Formula = Replace(.Formula, "$", "") 'delete the cell-fixation
            .Formula = Replace(.Formula, "E2", "Yourtable[Sales Amount]")
            .Formula = Replace(.Formula, "F2", "Yourtable[Profit]")
            ' ...and so on

        End With
    Next c
End Sub

Note:

  • you can add more expressions to replace
  • realize that you have to replace cells like A10 or BA5 before replacing something like A1 or B5 for obvious reasons. The same goes for ranges like A1:A100.
  • instead of Selection.Cells you might want to use something like Table1.UsedRange.Cells or Table1.Range("A1:B100").Cells
  • you could Debug.Print .Formula first to get an overview over all the different formulas you have
  • If you're fancy you could
    • loop over the table headers
    • get A1-coordinates of the header
    • replicate the A1-string you want to replace, say, by adjusting them to line 9 like in your example
    • replace any occurences of the to-replace string with the .Value of the header-cell
    • however, this might be too much of an effort for a one-time overhaul of a workbook
0
votes

I know this was asked a while ago, but I just solved this. And I thought I would share how.

First convert your data into a Table. (If you want you can create more than one table, but you will need to then specify the table name as part of the reference further down this sequence).

In a separate sheet I created a reference table with two columns consisting of old references and new references. To generate the old references I used an equation for the columns:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

And then concatenated it to the number of the first row.

[Thanks: https://www.excelforum.com/excel-general/857983-how-to-return-the-column-letter-of-cell-reference.html]

For the second column I used copy and paste special; values; transpose from the headings of the table where I wanted to create the structured references.

Then I used the macro Sub Multi_FindReplace() from here: https://www.thespreadsheetguru.com/the-code-vault/2014/4/14/find-and-replace-all

and that's it. Done for the first row. Then I could just fill down the rows to complete the table.

(Note, if you have fixed references (ie using $) then you might need to remove those first as they aren't needed in the structured referencing. If you need the fixed references to locations outside the table, you can use a variation of the above process to put them back in if you accidentally removed them. )