Forum Discussion

Andrew_Hinson's avatar
Andrew_Hinson
Brass Contributor
Nov 02, 2022

Formula updating

Hi everyone!

 

I have a list of transaction data that I regularly add new lines to, The data in the table contain  things such as Date, Category and £/$ Value. I have a macro attached to a button so that when it is clicked the data in the list sorts by the date in column A, oldest to newest.

 

I have various formulas on another sheet that are keyed to particular cells in that transaction table data.. For example 'IF' formulas that will display different results depending on the values in particular cells in that table data.

 

The trouble I am having is that when I sort the list, the cell references that my formulas are attached to are changing, and then the formulas are producing the incorrect results. 

 

I can't lock the cell with $ because the value in that cell will change when the data is sorted.

 

Does anyone know what to do so that I can sort the data and the formula will just change depending on the cells new location?

 

Thank you! 🙂

excel
Formulas and Functions
Macros and VBA
  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 03, 2022
    If I understand you have a formula inside the table (e.g. column D) and when you sort the table that formula move up or down and that causes issues. What are these formulas? please give us examples and is the table formatted as a table? even better would be to host the sheet on a site like onedrive or dropbox where you can share a link to it so we can actually see what you have (please no private info in the sheet).
    • Andrew_Hinson's avatar
      Andrew_Hinson
      Brass Contributor
      Nov 03, 2022
      mtarler thank you for coming back. I think I have not been clear. So the table is in one sheet, various rows of data, different columns for different things. Column A = Date, B=Income/Expense C=Monetary Value.

      The macro is recorded. I recorded simply highlighting all of the data and then sort by column A (Date)

      The formula on a different page relates to a cell in the monetary column, and is an IF formula. Basically if cell C14<0 then do this, if not then do that.

      But if I sort the data, the value in C14 changes. I want the formula to pick up that the value in C14 is X, answer the IF function and displaying the correct result, BUT if I sort the data and the value in C14 moves now to C24 (for example), the formula picks up that the value has moved and so the formula doesn't reference C14 in the IF function, but changes to the cell position the data has moved to. The IF function now references C24 in it.

      All of these rows and columns etc are purely for reference.

      Thank you.

Resources