Use a formula to populate names based on text in cells

Gummyworms1234

New Member
Joined
Jul 18, 2019
Messages
23
I am working on our employee schedules for next week and I have an issue. I want to make names appear under the replacement column automatically without having to type it in or write it in each time an employee won’t be working. I made an example of what I am looking for and highlighted the specific cells I am looking at. On the sheet1 picture, I’ve highlighted a few possible messages that I can use when an employee isn’t working. On the sheet2 picture, I’ve shown on April 11th Joey has an off day and Susan is on vacation. Mike and Lisa told me they can cover for them. I want to make Mike and Lisa’s names appear on our sign in sheet since I have OFF and V on April 11th. I’d like Mike and Lisa’s name to appear under the replacement column like I’ve shown on the sheet3 picture. If someone can help me with this issue I have, I’d appreciate it!
 

Attachments

  • sheet1.PNG
    sheet1.PNG
    4.6 KB · Views: 4
  • sheet2.PNG
    sheet2.PNG
    15.3 KB · Views: 4
  • sheet3.PNG
    sheet3.PNG
    20.3 KB · Views: 4

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

hajiali

Active Member
Joined
Sep 8, 2018
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
do you have a sheet/place on any sheet that Identifies Mike and Lisa will be the replacements? or do you have any notation any where were they replace them?
 

Gummyworms1234

New Member
Joined
Jul 18, 2019
Messages
23
do you have a sheet/place on any sheet that Identifies Mike and Lisa will be the replacements? or do you have any notation any where were they replace them?
I’m somewhat confused by what you’re trying to ask. On sheet2 of my spreadsheet, I identify that Mike and Lisa will be the replacement because they have the location number associated with Joey and Susan. Hopefully that answers you question. From what I’ve read online, I think an index formula or vlookup formula would be what I need.
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Ok I think this is what you want assuming that in sheet 3 Column A you have the location number as well. If so try this in Cell D3 and drag the formula down

Cell D3=IFERROR(IF(NOT(ISNUMBER(C3)),INDEX(Sheet2!$B$2:$B$21,MATCH(A3,Sheet2!$C$2:$C$21,0)),""),"")
 
Solution

Gummyworms1234

New Member
Joined
Jul 18, 2019
Messages
23
Ok I think this is what you want assuming that in sheet 3 Column A you have the location number as well. If so try this in Cell D3 and drag the formula down

Cell D3=IFERROR(IF(NOT(ISNUMBER(C3)),INDEX(Sheet2!$B$2:$B$21,MATCH(A3,Sheet2!$C$2:$C$21,0)),""),"")
This formula worked for columb D on sheet 3. So if I want to
Ok I think this is what you want assuming that in sheet 3 Column A you have the location number as well. If so try this in Cell D3 and drag the formula down

Cell D3=IFERROR(IF(NOT(ISNUMBER(C3)),INDEX(Sheet2!$B$2:$B$21,MATCH(A3,Sheet2!$C$2:$C$21,0)),""),"")
This formula was what I needed for the example spreadsheet and it works on my main spreadsheet I'm using for my schedules! Thanks for the help!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,724
Messages
5,638,003
Members
416,998
Latest member
bbowne

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
Top