Return different values in a cell from different column ranges, based on today`s date

1. Return different values in a cell from different column ranges, based on today`s date

Dear Forum,

I have this on-call tracking sheet (see attachment) where I need to return different values in a cell from different column ranges, based on today`s date.
So the goal would be to see which person from column D has on call duty today (based on column B and C) in which group (column A)
The Result should look somehow like this:
Picture1.png

2. Re: Return different values in a cell from different column ranges, based on today`s date

Hi
Use these formula to create a help column
=ROW(\$A2:\$A625)*--(\$B2:\$B625<=\$J\$1)*--(\$C2:\$C625>=\$J\$1)*--(\$D2:\$D625<>"")
where J1 is today()
See the fileBook1 (3).xlsx

3. Re: Return different values in a cell from different column ranges, based on today`s date

Hi Jose,

thanks for your reply. If works very well with today`s date, but if I try it with another date entered in L1, the name a group stays empty.
So I suggest if I would open the sheet in two weeks it would not work.
Any suggestion how this could be changed?
The goal would be to see the Names for every group throughout the whole year based on the day I open the sheet.

Thanks a lot
Alex

4. Re: Return different values in a cell from different column ranges, based on today`s date

Hi
Yes. You can put on other Sheet.
I correct the formula, so the names are grouped.
Formula:
`Please Login or Register  to view this content.`

See the file Book1 (4).xlsx and the Sheet "Today"

5. Re: Return different values in a cell from different column ranges, based on today`s date

Hi Jose

this is amazing. I really appreciate your help.
Have a nice day

Alex

6. Re: Return different values in a cell from different column ranges, based on today`s date

Hi Alex
Thank you for the feedback.

Do not forget to mark this thread as solved.

New Method
-Go to the top of the first post

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1