Excel The logic hurts my head

[Simont485]

SO this rota that is used at work is a nightmare........I have been tasked in listing by date, staff who are available to cover shifts based on previous overtime count.

I can't upload an attachment example??!!

Attached a photo instead...hope it's Ok!

I have been trying with index/match which I can understand, but the logic to include if blank or something similar is beyond me at present!

 

[Becky]

I'm not sure I follow what you're asking, but there is a way to upload the spreadsheet here - if you zip it first, it should be fine. I'm can't get to my laptop much for the next few days but I'll try and take a look when I get a chance

 

[Simont485]

I have been trying to sort this but I have fallen at the first hurdle!

I can use Index/match to list a name from column A dependent on the contents of column B. But I want to show a name from column A when column B is blank.

Any ideas?

 

[Simont485]

Attached....I hope!

 

[Simont485]

So been searching/asking on another forum. Have been shown this formula.

=INDEX($B$3:$B$12,MATCH($O$3&"",INDEX($D$3:$D$12&"",0),0))

It Indexes to column B and matches to column D but when the reference cell is blank it returns a result instead of N/A!
Ie it searches for blanks!

Anywayfurther digging and I have found Index and Aggregate.......which I think could be the answer I need......anyone used this before?

I have a couple of youtube videos to watch and re-watch but its not making sense!!

 

[Becky]

So been searching/asking on another forum. Have been shown this formula.

=INDEX($B$3:$B$12,MATCH($O$3&"",INDEX($D$3:$D$12&"",0),0))

It Indexes to column B and matches to column D but when the reference cell is blank it returns a result instead of N/A!
Ie it searches for blanks!

Anywayfurther digging and I have found Index and Aggregate.......which I think could be the answer I need......anyone used this before?

I have a couple of youtube videos to watch and re-watch but its not making sense!!

Sorry been away and lots to catch up on since getting back!

Have you tried ISBLANK?