Bug in tricky array formula

[joshuaos]

Hello all!

So, with the help of this forum a couple of months ago, I got this epic and tricky spreadsheet done with some very complex formulas, which had stumped me forever until someone brilliant used array formulas to solve my problem.

Now, in using the sheet, there is a bug and I am at a complete loss to get it to work right! Please please you formula gurus out there, help me get this bug figured out.

(I am on LibreOffice Calc 4.2.8.2 in English under linux)

The spreadsheet is here attached, and the problem is in the ‘current’ tab in the F column. The formula pulls data from the ‘advocacy log’ tab and is supposed to pull the name of the most recent advocate for the person in question (named in the A column of the current sheet on that same line). It works until two entries have the SAME DATE, then as you can see in the example data, it doesn’t work (current.F9 should show as Joshua).

council record.ods

I am so lost in this formula, your help is deeply appreciated.

Thanks, Joshua

[Lupp]

Let’s be explicit.

You once got the hint that you are using the wrong tool for the task. You get it again now. If you decide, however, for specific reasons to do such things using spreadsheets instead of a database system, you have to design your solution very thoroughly, and to regard the basics of databasing as best as possible in spreadsheets.

In addition you need to find an way to cope with the very poor means of documentation you have at hand developing complex spreadsheets. How to make sure a concept you used will be clear enough to you (not to speak of someone else) a few months later? How to prepare for maintenance, enhancement, even debugging? Yes! Software is buggy and so are spreadsheets. We have to be prepared.

The only thing actually helping with this (as far as I can say) is, to split complicated formulae into their functional subformulae as detailed as possible using helper columns and to comment on them for later use.

Please be sure to understand that the attached version of your file containing a patch is not your solution. Redesign your sheets or shift to a database. Otherwise you will be back here again and again, and one time a helper will not. ask60067_council_record_Re001.ods

[joshuaos]

Lupp, thank you very much, you have indeed fixed the bug. And I understand your hint and I see that a database would be a simple solution for this. However, due to the environment that I’m in, the portability of the spreadsheet is HIGHLY favourable for us, and though it’s perhaps stretching the power of spreadsheets, it does now seem to work thanks to some brilliant formula-making. Now that it’s working, it’s use should be simple and it function for a long time to come. My deepest thanks!

[joshuaos]

Interesting… it seems you used an operator I didn’t know about, the &… I will learn about this now with your example. Thank you so much again!

[Lupp]

The & is the concatenation operator for texts (strings). Using the CONCATENATE function often leads to unreadable formulae.

Be sure to understand what I said. The patch may introduce new gateways for errors, and thus be a bug in itself. In specifict it relies on an automatic type conversion number–> text which I would prefer to avoid. The patch and the formula it is patching as well will fail, eg, as soon as one of the “dates” in fact is a date-time value only fornatted as a date.