Return a TEXT value if certain TEXT is missing

steve89

Board Regular
Joined
Oct 4, 2015
Messages
152
I really need help with this formula. This is what I'm looking for

I'm writing a schedule but want to make it very automated for the managers. I have the following positions on my schedule: AM 1, AM 2, AM 3, AM 4, PM 1, PM 2 & PM 3. These positions are on everyday. In that specific space sequence. I want the sheet to know if column d9:d23 were missing any of those shifts to automatically return that exact missing shift. I know how to use in conditional formula but don't want color coding just want it to return the missing shift if these are suppose to be all the shifts for 1 day.

Thank you kindly all love this site great help and knowledgeable smart individuals
 
Re: Need help..Want to return a TEXT value if certain TEXT is missing

really wish I could send you the exact layout and what im looking for as it works but still not what im looking for the shift is suppose to be in the "open shifts" section if any if the days shifts are missing
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Re: Need help..Want to return a TEXT value if certain TEXT is missing

col H is the shifts you have sorted out - there are some missing - these are flagged in col D
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

a,b,c in col a
c,b in col b

a is missing - where do u want to put this info - logically we could turn the a in col A red
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

really wish I could send you the exact layout ..
You can post a small section of your sheet directly here in your post. A link in my signature block below has information about several way to do that.
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

okay the conditional formula sounds good will put that in thanks for feedback. The formula does work cant say it doesn't. It's still not what i'm looking for no fault of yours wish I could explain it better... thank you Peter for the info.----Brewer I set up a sheet for employee data. This is suppose to be a universal template for any manager in our company to use. I setup an "Employee Data" sheet where the manager will enter his/her exact shifts on Column A2-A62. Then they need to enter the hours for the exact shift on Columns E2-E62. once there done there the formula is written to give those exact count if formulas based off of each managers accounts as all are different. That being said the formula I'm seeking would need to be on that page where all data will be entered. I need the manager to enter the shifts as you did in your table example in the earlier post. Depending how many shifts the manager fills I would create a table with name manager and those stats. will go into the schedule sheet and say that shifts is missing but the catch is it cant go in the cell where the schedule or shift is but rather on the bottom in the "open Shifts" cell. Based out of how many positions you filled in the other page it will match and know that from cell C6-C21 you need at least all the shifts you entered in the "Employee Data" sheet and if one of those are missing to return that missing shift needed--thank you very much again you have been of perfect assistance.
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

let us say you have 3 departments each with one manager and 9 employees
there are 5 shifts and for now a week is 3 days
you want to have an overview of any shifts not yet "covered"
on this shift deployment sheet each manager "fills" the shifts
day1day1day1day1day1day2day2day2day2day2day3day3day3day3day3
shift1shift2shift3shift4shift5shift1shift2shift3shift4shift5shift1shift2shift3shift4shift5
dept1manager1emp1emp2emp3emp4emp5emp1emp2emp3emp6emp7emp8emp9emp1emp2emp3
dept2manager2emp10emp11emp12emp13emp14emp10emp11emp12emp13emp14emp11emp12emp13emp14
dept3manager3emp19emp20emp21emp22emp23emp20emp21emp22emp25emp26emp27emp28
unfilled shifts
dept1manager10
dept2manager21
dept3manager33
total unfilled4
what other information do you need ?

<colgroup><col span="4"><col span="2"><col><col><col span="2"><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

11
162
let us say you have 3 departments each with one manager and 9 employees313
993
there are 5 shifts and for now a week is 3 days
you want to have an overview of any shifts not yet "covered"
on this shift deployment sheet each manager "fills" the shiftsthe cells are numbered 1 to 45 at the start
day1day1day1day1day1day2day2day2day2day2day3day3day3day3day3
shift1shift2shift3shift4shift5shift1shift2shift3shift4shift5shift1shift2shift3shift4shift5123456789101112131415
dept1manager1emp1emp2emp3emp4emp5emp1emp2emp3emp6emp7emp8emp9emp1emp2emp3
dept2manager2emp10emp11emp12emp13emp14emp10emp11emp12emp13emp1426emp11emp12emp13emp1426
dept3manager3emp19emp20emp21emp22emp2336emp20emp21emp2240emp25emp2643emp27emp28434036
emp13
emp23
emp33
UNFILLED SHIFTSemp41
unfilled shiftsemp51shifts worked
positionrowdeptmanageremp61by each
dept1manager101433dept3manager3emp71employee
dept2manager212403dept3manager3emp81
dept3manager333363dept3manager3emp91
4262dept2manager2emp102
5emp113
6emp123
total unfilled47emp133
8emp143
9emp150
10emp160
11emp170
12emp180
13emp191
14emp202
15emp212
16emp222
17emp231
18emp240
19emp251
20emp261
21emp271
22

<colgroup><col><col><col span="2"><col><col><col span="2"><col span="2"><col><col><col><col><col><col><col><col span="16"></colgroup><tbody>
</tbody>
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

so this looks good except its just 1 department and no managers are needed on the schedule just AM 1, AM 2, AM 3, AM 4, PM 1, PM 2 & PM 3. We currently have 10 employees filling 7 shifts while the other 3 are off. No need for department either that can be removed. Also no shift is kept the same for an employees it rotates. I would like to the formula to just understand that on row C10-C21 if a day is suppose to have filled shifts that I listed above then a message needs to be returned stating the exact shifts I need to fill to close my schedule.
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

I have given you a table of unfilled shifts - you can see the approach - just fine tune it to what you want...
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

hi i know its been a long time im just getting back into writing excel formulas and still need help with this one.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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