[Solved] Sort incremented dates

[cephas]

The first set of rows in the spreadsheet retrieve the date from another sheet. For example

Code: Select all

=FixedAmts.F39

, and then the next iteration of that row will be

Code: Select all

=SUM(B12+30)

. That is repeated down the date column for all types.

However, when I sort by date, the dates that were positional keep getting messed up. Should I be using the 'dollar' sign. All the dates are of type 'date'. It's just that after the sort, B12 is now another row, but the values are not getting adjusted correctly.

[robleyd]

We could guess as to how your spreadsheet is designed, or you could upload a sample file that demonstrates the issue.

[cephas]

Yes, good idea. I need to do some more testing first, using the "$".

[robleyd]

Code: Select all

=SUM(B12+30)

You don't need the SUM here. =B12+30 returns a single value; SUM is intended to return the sum (surprise) of a number of values.

For better understanding of use of the $ you may wish to read [Tutorial] Absolute, relative and mixed references

[cephas]

Okay, I will remove the SUM's

[cephas]

Here is some test data

test_sortdates.ods

(31.57 KiB) Downloaded 11 times

[MrProgrammer]

However, when I sort by date, the dates that were positional keep getting messed up.

Done as attached, the dates are in order and there is no need to sort.

202505082136.ods

(20.81 KiB) Downloaded 4 times

Okay, I will remove the SUM's

You didn't. I did.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] VLOOKUP questions and answers
[Tutorial] Ten concepts that every Calc user should know

[cephas]

Done as attached the dates are in order and there is no need to sort.202505082136.ods

Great, thanks, very impressed. I have done some minor testing, changing dates and values, adding new rows by copy/paste. It all seems to work just fine. I'll do some more testing in the next few days.

Okay, I will remove the SUM's

You didn't. I did.

The only SUM's left were in the balance column. I had removed all the others, however it does seem that they are not even needed in the 'Balance' column.

If I want to add new entries (not from a copy/paste), your code doesn't seem to be referencing the sheet "FixedAmts" ?? I can only assume the VLOOKUP is searching through both sheets. I will need to read up on VLOOKUP obviously and understand it.

Thanks muchly fr your help.

[cephas]

I have done more testing, adding new entries in the 'FixedAmts' sheet and then adding an entry in the first sheet, changing the 'Repeat' value,etc, etc. I must say I'm VERY impressed with the work done in creating those formulas, thank you.

[Alex1]

References to FixedAmts use a name Fixed defined as $FixedAmts.$A$1:$D$30. Press Ctrl+F3 to manage defined names.
Cells containing dates and currencies are formatted using format styles. Press F11 to manage them.

[cephas]

References to FixedAmts use a name Fixed defined as $FixedAmts.$A$1:$D$30. Press Ctrl+F3 to manage defined names.

Ctrl+F3 definitely appears to be how a defined name setup is accessed, yet those function keys didn't work for me.

I found https://help.libreoffice.org/latest/he/ ... 70100.html , and From the menu bar: Choose Sheet - Named Ranges and Expressions - Define. --> works fine.

Cells containing dates and currencies are formatted using format styles. Press F11 to manage them.

F11 works okay.

[cephas]

The column "Repeat" is used now and reflects the number of days. How can that be tweaked for calendar months ? For example, monthly items were defined previously as

Code: Select all

=EDATE(D15,1)

[MrProgrammer]

How can Repeat be tweaked for calendar months [using EDATE(date;1)]?

Please review our forum policy: One issue, one thread.

This topic is about Sorting incremented dates. That issue was solved. You now have a new question about using EDATE in the formula and should have opened a new topic for it. This time I'll overlook the mistake, but don't expect to continue to receive replies from me for additional new questions in this [Solved] topic. I don't see anythig about EDATE in the first set of posts.

202505092123.ods

Formulas changed to use EDATE

(20.82 KiB) Downloaded 2 times

[cephas]

You now have a new question about using EDATE in the formula and should have opened a new topic for it.

Okay thanks, point taken. I've added a new topic and marked it as 'Solved" - viewtopic.php?t=112764