Forum Discussion
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 s...
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).
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.
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.