[Solved] Autonumbering based off criteria in 3 other columns
Posted: Fri Mar 15, 2019 8:24 pm
Hello again; apologies to ask another question but in continuing to work on updating a legacy form I am encountering another issue when translating excel to OpenOffice.
The entire worksheet working hinges on the ability of the main data table to apply numbering to entries automatically dependent on 3 other criteria. It is to first look at what kind of data-entry the row is, then for what department it is, and then what date it falls on to autonumber accordingly. The data is sorted by date.
In re-creating this for OpenOffice I have "simplified" what one would enter in this formula as instead of evaluating for 9+ "kinds", there are only 6,. this is possible because certain kinds can be grouped together for entry on the final form. (Please keep this in mind when looking at the input for Excel.)
In Excel, I was using:
I have begun to build this in OpenOffice with:
The above works correctly. It only effects rows where column M and L equal "A", and since the data is already sorted by date it numbers sequentially as I would hope. However this is not nearly the final product I need, as there are 3 options for column M and 6 options for column L. In effect I will be creating 18 different IF statements to make this work... though not my actual question, is there a better way to format this so that it is not as long?
To take a step further In OpenOffice, I have attempted:
In this case I would expect the first IF statement to be treated separately from the second, but somehow they are crossing paths, as the numbering completely flops upon the addition of this second "kind" and "department".
Is there a way to achieve this same auto-numbering in OpenOffice?
The entire worksheet working hinges on the ability of the main data table to apply numbering to entries automatically dependent on 3 other criteria. It is to first look at what kind of data-entry the row is, then for what department it is, and then what date it falls on to autonumber accordingly. The data is sorted by date.
In re-creating this for OpenOffice I have "simplified" what one would enter in this formula as instead of evaluating for 9+ "kinds", there are only 6,. this is possible because certain kinds can be grouped together for entry on the final form. (Please keep this in mind when looking at the input for Excel.)
In Excel, I was using:
Code: Select all
=IFERROR(IFS((OR(M2="Z",M2="J",M2="DR",M2="ES",M2="R")),COUNTIFS(D$2:D2,D2,L$2:L2,L2),(OR(M2="W",M2="HG",M2="CD",M2="D",M2="M")),COUNTIFS(D$2:D2,D2,B$2:B2,B2,L$2:L2,L2)),"")
Code: Select all
=IF(AND($M2="A";$L2="A");COUNTIFS(D$2:D2;D2))
To take a step further In OpenOffice, I have attempted:
Code: Select all
=IF(AND($M4="A";$L4="A");COUNTIFS(D$2:D4;D4;L$2:L4;L4);IF(AND($M4="A";$L4="B");COUNTIFS(D$2:D4;D4;L$2:L4;L4)))
Is there a way to achieve this same auto-numbering in OpenOffice?