Search for Date Column Header in non-table spreadsheet and return value below.

almacote

New Member
Joined
Aug 5, 2005
Messages
25
This is for a non-profit Assisted Living facility where I work as an aide. All the staff are pretty new, so I'm trying to get a workable staff schedule together.

I'm using Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20850) 64-bit. I am not good at VBA and usually get what I need from formulas.

See image: I have a maximum number of hours allowed, weekly, and each employee has a number of contracted hours they need to work, plus I need to cover needed hours per day. The top worksheet is a place where I can do all these machinations, however, I need to put out the resulting schedule in a simplified Monthly 8.5 x 11 sheet, and currently, it's printed manually which has made for many mistakes.

I'm hoping, using the bottom worksheet daily date, I can look for that date in the top sheet, and return the in/out times of each individual, and then return the name of the individual by returning the named cell "so many" cells to the left. I tried using a named range for the particular day from the top sheet, i.e., _mar01, _mar02, etc. I'm not sure if the date column hear being merged and centered using two cells is a problem, but I tried to force the range my typing in F152:F166. So, I want it to automatically populate from the top sheet date. I would also like to return notes that are under the hours, if needed.

My long term desire is to have a data table with each employees name next to dates and availability and vacations, etc., that will be easily brought into the top sheet by an employee number and that will return an alert if someone is not available if the scheduler tries to put that person on the schedule.

So, back to my short term needs, I'd like to populate the output worksheet (bottom) from the top sheet.

Thank you hive mind!!! You've always bee there for me!
 

Attachments

  • FindDateAndReturnValueBelow.png
    FindDateAndReturnValueBelow.png
    254.2 KB · Views: 18

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top