Long time listener, first time caller...

I'm sure this is simple, but I don't know how to do it.
I am putting together a very simple spreadsheet that will autopopulate the correct shift when a manager is input into a cell. I need to do this cell by cell, because some of the managers overlap shifts.

In place of "name" and "shift" I have the actual manager and shift, but for simplicity sake I have:

=IF(K21="NAME", "SHIFT1", "SHIFT2")

and that works just fine for this because there are only 2 managers with different shifts.

However, when there are no manager names in K21, it still gives me a shift in the other cell.

How do I leave that blank until the manager name is filled in?

(I will also need to have several other "IF" statements in other cells, as there are more managers on those shifts, and nested IF statements are telling me there are too many arguments. Not sure what I'm doing wrong.)

CodePudding user response：

Try using your IF as the second argument for another IF that would check if the Manager cell is empty:

=IF(ISBLANK(K21),"",IF(K21="NAME","SHIFT1","SHIFT2"))

CodePudding user response：

However I would just go with vlookup() like this:

=IFERROR(VLOOKUP(K21,$Q$20:$R$21,2,0),"Not in list")

and then you can have a list of names and shifts and even add a 3rd column with weekend shifts...

So trap the blank first:

=if(k21="","",your if here)

But if k21 has other text then you may need to consider something else, perhaps a list of valid names:

=if(iferror(vlookup(k21,list,1,0),0)>=1,your if here,"")

That list can be a range of cells or just both the names.

See:

=IF(IFERROR(VLOOKUP(K21,Q20:Q21,1,0),0)>=1,IF(K21="Fred","SHIFT1",IF(K21="Julie","SHIFT2","Error1")),"Not in List")

CodePudding user response：

Since you mentioned you'll have multiple if statements, you might consider doing a table and then a lookup. Here's a