Excel The logic hurts my head


Joined
Feb 23, 2018
Messages
66
Reaction score
24
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!20190509_141147.jpg

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!
 
Last edited:
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,508
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 ;)
 
Joined
Feb 23, 2018
Messages
66
Reaction score
24
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?
 
Joined
Feb 23, 2018
Messages
66
Reaction score
24
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!!
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,508
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top