Formula help - return value based on 2 cell values

[dangoodwin96]

Hi guys,

sorry if the title doesn't really give you a good overview of the issue...

I basically am trying to create an in/out system in excel.

When we give out access cards to people, they are logged on a spreadsheet then updated when they return them.

Capture.PNG

Based on the above image, there are 2 conditions to know the card is with us and ready to give out again:
1) The cell colour is green
2) The "Actual Return" is filled in.

So for example badge 1,5 are all in and 2,3,4,6,7 are out.

So in theory, I want a table to display the below:

Badge 1 | IN
Badge 2 | OUT
Badge 3 | OUT
Badge 4 | OUT
Badge 5 | IN
Badge 6 | OUT
Badge 7 | IN

Can someone help me with a formula to do the above please?

Thanks,
Dan

[Sam Capricci]

this would be one way...
=IF(AND(B4<>"",C4<>""),"IN","OUT")
dragged down

[Sam Capricci]

but if you want it EXACTLY as you wrote the results then this...
=IF(AND(B4<>"",C4<>""),"Badge | "&A4&" IN","Badge | "&A4&" OUT")

Oh and by the way, mine should reference L and M instead of B and C.

[PaulM100]

I think that you can only reference the interior color of a cell via VBA. Uploading a sample and explaining the rules behind the green colour may help.

To attach a file, click Go Advanced below the text edit window, scroll down and click the Manage Attachments link, browse to the required file, and then push the Upload button, then Close Window to return to your post. (The "paper clip" icon for attachments is not currently working.)

[dangoodwin96]

Hi sorry, one thing I forgot to mention was that I will have a seperate table for the IN/OUT status.

e.g. the below:
Capture1.PNG

So i need a formula to look down the K column (as there will be multiple badge numbers as they get signed out and returned) with whatever badge number is in the Y column.

[jason.b75]

Hi Dan,

Based on your screen capture, I would suggest something like

=IF(COUNTIFS(K:K,A2,L:L,"<>",M:M,"")=0,"IN","OUT")

Where A2 refers to an individual badge number in the output table.

This should count all rows where the specified badge number has an entry (date) in column L but not in column M, which will be 1 for OUT or 0 for IN.

This doesn't look at the cell colour, a formula is not able to distinguish that.

Does that help?