As the subject suggests, I want to be able to have formulas keep pulling data from the same source, even if I sort the source data. Forgive me if this sounds amateurish. I'm not very knowledgeable with the formula functions.
For example, say that I have data in the first four cells of column A. A1 contains the digit 3, A2, 5, A3, 1, and A4, 2. In B1, I have =SUM(A1+A2), which is 8, and B2 contains =SUM(A3+A4), which equals 3. Say that I want to sort column A, though, from lowest to highest value. Now, it reads 1, 2, 3, and 5. The formulas, however, do no adapt to this change. I would like them to still equal 8 and 3, as they did before the sort, but now, they read 3 and 8. How do I make sure that formulas still present the same values before and after I sort their source cells?
[Solved] Formulas Don't Adapt to Sorting Their References
[Solved] Formulas Don't Adapt to Sorting Their References
Last edited by bluhawk19 on Sat Nov 24, 2018 11:29 pm, edited 1 time in total.
Re: Formulas Don't Adapt to Sorting Their Reference Cells
Personally, I don't think you can without forcing your formulas to become fixed values, but this tutorial may have other information.
[Tutorial] Sorting and Filtering with Formulas
Off topic, but to make your spreadsheet more efficient, don't use the SUM function for simple two cell addition.
=SUM(A3+A4) will give the same results as =A3+A4, but the first one requires a little bit more memory, and thus is less efficient.
For larger ranges (eg: A3 to A50 inclusive) then you should use the SUM function, rather than a lot of + characters... an easy rule of thumb, is look at the number of characters required for the formula to be entered, and go with the formula that uses the least number.
[Tutorial] Sorting and Filtering with Formulas
Off topic, but to make your spreadsheet more efficient, don't use the SUM function for simple two cell addition.
=SUM(A3+A4) will give the same results as =A3+A4, but the first one requires a little bit more memory, and thus is less efficient.
For larger ranges (eg: A3 to A50 inclusive) then you should use the SUM function, rather than a lot of + characters... an easy rule of thumb, is look at the number of characters required for the formula to be entered, and go with the formula that uses the least number.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
- MrProgrammer
- Moderator
- Posts: 5281
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Formulas Don't Adapt to Sorting Their Reference Cells
Data → Sort → Options → Copy results to → {somewhere else}.bluhawk19 wrote:How do I make sure that formulas still present the same values before and after I sort their source cells?
An in-place sort can't preserve values in dependent cells (cells which depend on the values being sorted) because it shuffles the data in ways which may not preserve cell ranges. Suppose the range being sorted is A2:A4999 and cell D5 contains =AVERAGE(A500:A3141). The values which were in one range, A500:A3141, may be broken into a thousand new ranges, say A605, A2047:A2048, A666, A3033, A1414, A905:A906, …. The AVERAGE function can't handle a thousand ranges as arguments. So there is no way Calc can adjust that formula to preserve its value after the sort has rearranged the data.
Maybe P4 contains =VLOOKUP(P3;$A$705:$A$1789;2;0). VLOOKUP needs the lookup range to be a contiguous set of cells. The sort can break A705:A1789 into many pieces. Calc cannot adjust the VLOOKUP formula so it works as before.
Because there are some dependent formulas which can't be adjusted to preserve the values which they had before the sort, the developers would have realized that the only practical way to handle dependent formulas is to adjust none of them.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7.6, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.7.6, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).