Please could any one suggest the function I should use to resolve the following issue - With this I will be able to investigate further / get this working for our user.

Requirement: The user would like a lookup of a cell range to be displayed in another tab. We only want cells that have not been filled in with a colour to come across (no fill) or if the cell is blank / has no text in it.


Column A = Staff members name.
Column B = AM time that staff member has checked in.
Column C = PM time that staff member is checking out.

If an AM or PM time has not being given the cell will be red.

I would like to display a list of all users where the AM time has been filled out, but the PM time has not been filled in yet.

In the example below, Person A would not need to be brought into the new tab as we have AM and PM checked in / out times. However Person B and C would be brought in.

Screenshot Example

Please let me know if you need nay further information.

  • 1
    for the cell colouring, use conditional formatting, for the list use pivot table
    – PeterH
    Nov 14, 2017 at 12:47
  • How would you want to handle a situation when it's next day? Say your solution works for say Wednesday 1-Nov-17, now it's Thursday 2-Nov-17 and again same set of Staff members. Do you have any output format in mind, as to how the list should look like on next Tab?Also when you mention 'If an AM or PM time has not being given the cell will be red' is this already present in the sheet as Conditional Formatting or you expect a solution like that thru this question?
    – patkim
    Nov 14, 2017 at 17:40
  • My understanding is that each day the user will be changing the pivot to look up the correct columns for the day - Wednesday they user B+C / Thursday - D + E / and so on. Nov 15, 2017 at 12:14
  • I have highlighted the amount of work this will be for them each day and they should just set up filters on AM + PM on cell colour and then copy and past the records into a new tab however the business users feels this is going to take more time than editing the pivot for each day. Nov 15, 2017 at 12:15


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.