[Solved] Working across a schedule true/false

[ProfessorFazil]

EDIT (with live OpenOffice example)
Hello, first time poster, I usually like to figure this stuff out on my own, but I've struggled enough to let my pride die.

I am making a schedule and want to check to make sure certain people do not have "Bunk Duty" the night before their Day Off. I will also use this as the framework to check that a person who is on Bunk Duty isn't actually on their day off and that they aren't in two locations at the same time.

Here's an example-
I have a list of people's acronyms and I was going to make columns beside them with each of the questions I posed above

Acronyms for people:
GV1F (in cell H19)
GV2F
GV3F

Attached is a section of the table I want to check information from.
I basically want it to say if "GV1F"(aka H19) is found in the first section and also in the second section=False and if found in either or neither section=True
But then repeat the query about 10 more times checking each pair for a day off/Bunk Duty mismatch.

I hope that makes sense. Here is some of the coding that I have been fiddling around with; not sure how to join the other days into the same query or even to get this one to work on it's own.
IF(NOT(SUMPRODUCT($H$43:$I$54=H19)>0)AND(NOT(SUMPRODUCT($P$43:$Q$54=H19)>0));0;1)

[keme]

Attached is a section of the table I want to check information from.

Not quite. You attached a picture of a section of the table.

To test a possible solution, the helper would have to first key in the data from the picture, instead of opening an attached spreadsheet file (even copying text posted on the page might be less work).

Should we do the typing, we would not catch any problems arising from mistakes in your original file (naming mismatch in formulas, leading/trailing whitespace in your data, etc.). IOW, more work for us and less chance of success.

Also, you named the attachment "Excel_example". While solutions for Calc frequently also work with Excel, there is no guarantee. If it is MS Excel you have, you may be better served by Microsoft support, or forums specializing in MS software.

[ProfessorFazil]

Fair points, it would be easier to work with a live spreadsheet, it was late and my brain didn't think of it. attached here

[keme]

From first look, it should be possible to fix this in a spreadsheet, though I think a database will make the task a bit easier. May be more work setting it up properly, but when it is done you'd have a flexible model, fairly easy to understand.

A little more info is required for me to understand your data. Some rearranging/redirection is required to make the check efficient (build structured tabular data from the planning tableau), and I don't want to do the work again because there was something I didn't know. Start with this:

• Are all of "GV", "BV" and "TC" people, or are some of them reservations for tools/implements or places? What about the "Sup" columns, are they "supervisor" or "support"? In other words, is it possible that similar checks wil be required for allthe lists starting at row 18?
• I see that you check the entire BD block (Is that what you call "bunk duty"?) for the GVxx people, but it looks like there is a designated GV section. Does GV mean different things in H19:H25 vs. in rows 47-50?

[ProfessorFazil]

The rows 19-28 contain acronyms for individual people that will be using the same code. I will be using the same exact checks for the Support Staff in each village. You could place everyone into one column, I just have it separated for ease of reference.

There are 3 locations that we need people to be in for Bunk Duty and that is indicated by the merged blocks A43-A46 Location Boys Village, A47-A50 Location Girls Village, and A51-A54 Tween Creeks. Someone could potentially be a support staff for Girls Village (ex. GVSF), but be needed for a Bunk Duty at Tween Creeks and even sometimes Boys Village. Therefore I check the entire BD block to make sure I count all the places that she could be assigned.

I think I see what your saying. I should just make a detailed table that pulls the information from the schedule and then use that information for my "checks" rather than trying to pull it directly out of the schedule.

[ProfessorFazil]

I made a table and I am now working on trying to get the code that ensures they do not have a "bunk duty" the day before they are off.

EDIT- Thanks for helping me think it through, I really was in a roadblock and couldn't progress further. I finished coding everything using the table.

[ProfessorFazil]

I don't know who does this but this topic can be marked solved.

[robleyd]

I've done it for you, but for future reference:

How to tag a topic as solved.

In your first post, click the Edit button. Go to the subject line and add [Solved] at the beginning of the subject.

Then select the green tick as the Post icon (just below the subject line). Preview if you wish to see how it looks.
Finally, hit Submit - done!