IF AND to return text

kimberlee.bernadot

I'm trying to return a person's name based on two criterions.

=IF(AND({FocusArea} = "Senior Project Manager", {Projects_Inititatives} = "Project One", {Name}, ""))

Also, can I reference two different sheets? I have two sheets that have the same information but would like to cross reference both sheets into one with who is responsible based on their focus area for each of the projects.

=Chris Palmer

Hello @kimberlee.bernadot
Something like this perhaps?

=IF(AND({FocusArea} = "Senior Project Manager", {Projects_Inititatives} = "Project One"), {Name}, IF(AND({AnotherSheet FocusArea} = "Senior Project Manager", {AnotherSheet Projects_Inititatives} = "Project One"), {AnotherSheet Name}, ""))

kimberlee.bernadot

Hum, I'm still coming up with an INVALID OPERATION. I'm just trying to figure out the first part of being able to reference the Name based on the two criterion, Focus Area and the Project. Then dig deeper into being able to reference the other sheet.

=IF(AND({FocusArea} = "Senior Project Manager", {Projects_Inititatives} = "ProjectOne"), {Name}, "")

Jgorsich

If you are doing this in the sheet you are operating in, you'd want to change your reference, so it would look more like this:

=IF(AND([FocusArea]@row = "Senior Project Manager", [Projects_Inititatives]@row = "ProjectOne"), [Name]@row, "")

If you are trying to do it in a different sheet, your formula is actually saying the following (sorta, in practice it is saying nothing because it is erroring out):

If ALL of the {FocusArea} range = "Senior Project Manager" and ALL of the {Project_Initiatives} range = "ProjectOne" then return ALL of the {Name} range. There is nothing there to tell it to limit it to a row by row comparison or to output the results row by row instead of the entire {Name} range.

What you'd need would be something like an index(collect()) where you first tell it "Go collect all the values from the {Name} range where on that row {FocusArea} = "Senior Project Manager" and {Project_Initiatives}="ProjectOne", then give me those names in the following order…"

If you are only going to have a single senior project manager per project initiative and you've got a list of Project Initiatives (called "Project List") in the sheet where you are trying to write this formula (I figure this is most likely), it might look like this:

=index(collect({Name},{FocusArea},"Senior Project Manager",{Project_Initiatives},[Project List]@row),1)

The specific formula you need would depend upon precisely what you are trying to do - this might be listing all projects and their managers, in which case you'd need to vary the index value ("1" in the sample above), or it might be listing the manager for a defined list of projects (the sample above), or it might be something else entirely.

kimberlee.bernadot

I tried the Index formula too, and still not working. But I think you're getting closer as to what I'm trying to do. Maybe it will help if I include more specifically what I'm trying to do. :) And I sincerely appreciate all the help.

This is my source worksheet, let's call this "US Team Members". I have a second worksheet that is for "EU Team Members".

This is the sheet I wanted to use to display all the people resources for each project.

Maybe I'm going about this all wrong. I tried to find a template that would help manage resource but couldn't find one. Basically, I have two spreadsheets that include all our team members from two locations, US / EU. It has all their information for hiring date, and all the projects (which can be multiple), they are assigned to. I could put these in one spreadsheet if I need to.

I need a way to show who is assigned to which project(s) and eventually would like to figure out away to show their capacity. I'm really struggling with Smartsheet being the right tool to manage resources. It's kind of like you can only have one resource assigned to one project or you have to have a spreadsheet for each project, we have over 40 projects and a team of 200 so this is really tough.

Jgorsich

Okay, it looks like your "Projects / Initiatives" column in the first sheet is from a drop down list, correct? So - the formula I gave you would assume that "Project 1" was the ONLY thing in that column for John, which in this case it isn't, so it would fail (in most cases).

What you need instead is to collect everything where "Project 1" is part of the cell contents. This thread is about that: Count if all, cells contain a specific word. — Smartsheet Community

Try this, see if it works. Obviously, modify the references in the curly brackets to work after pasting! (BTW - did you want to search "Title" or "Focus Area" for "Senior Project Manager"? Kinda looks like Title.)

=index(collect({Name},{Title},"Senior Project Manager",{Project_Initiatives},contains([Project List]@row, @cell)),1)

kimberlee.bernadot

Thank you again Jgorsich. You're correct, focus area is likened to title. I truly appreciate your help. I also reviewed the link you sent as well. I feel like I get the concept but I'm probably doing something very simple wrong.

Still getting UNPARSEABLE error.

Jgorsich

Hi Kimberlee - you just need to finish the index formula. Prior to your closing parenthesis, add a ",1" to get the first value, or increment on a match() formula to get a variable response. In your case, as long as there should only ever be 1 value that meets your conditions, the ",1" should do the trick! Also, word to the wise, for your contains() reference your [Digital Inspection Toolbox]@row should probably be [Project / Initiative]@row (you are pulling the value from the "Project / Initiative" column in the row being examined).

=index(collect({Name},{FocusArea},"Application Architect", {Project_Initiatives}, contains([Project / Initiative]@row,@cell)),1)

kimberlee.bernadot

Oh yah, that was a silly miss but unfortunately still doesn't work. I'm reaching out to our Smartsheet Rep, there's got to be a better way to manage resources… I hope. :) Thank you again for all your help. I really appreciate it and your fast response.

Jgorsich

Best of luck with it! If your Smartsheet rep isn't able to help you, feel free to post what the formula is doing now - possibly it is something that can still be fixed!

kimberlee.bernadot

Will do! Thank you so much and have a great weekend. :)