1

I have this sheet where I want to do simple project planning and resource booking

The columns 6-17 show week numbers and each main objective within a project has a start week and end week - no actual dates needed in this case

So I set the start/end week numbers and based on that either X or Y is set into the cell, I'll use that for conditional formatting later to display a "Gantt" like view

Now the problem I am facing is with the resources further down the page.

How do I find the objective Jane is booked for that has the highest week number?

Resources can be part of several objectives and what I want to display in the bottom is, when will Jane be available again for new projects?

Improve this question
1
  • Your question would be better if it didn't rely so much on the linked spreadsheet. We want questions to be helpful to people of the future who might be searching for a solution to the same problem. If (when) the link stops working, there's probably not enough information here for someone to answer or determine if their problem is similar. It'd be great if you could edit your question to include some minimal examples of what you're trying to do. (It's not hard to fake up some text that looks like it's in a spreadsheet.)
    – ale
    Feb 3, 2016 at 13:51

1 Answer 1

Reset to default
1

Suppose TEAM is in B2:B10, to find if member Alice is part of the team, you do regexmatch(B2:B10,"\b(?i)Alice\b"). Here, \b(?i)Alice\b is a regular expression. \b means word boundary, and (?i) means the word search is not case sensitive.

Now, suppose you want to all A2:A10 values where Alice is a part, you do filter(A2:A10, regexmatch(B2:B10,"\b(?i)Alice\b"))

Next, to get the maximum A value, you do max(filter(A2:A10, regexmatch(B2:B10,"\b(?i)Alice\b")))

See filter and regmatch

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.