Check if cells contain in dynamic range from different sheet

goriamir

New Member
Joined
May 26, 2022
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
Hello, this is my first post in this forum. I am currently doing a mini attendance sheet for number of employees. I have two different sheets, "A" and "B".
In sheet B, there are a list of employees with each of them have 4 values (date).
In Sheet A, there are column of days of a month. I would like to mark "R" in the specific cell according from sheet B as shown for Employee 1 in the second picture.
My current formula for the cell in Sheet A is,

=IF(COUNTIF('B'!$E$5:$H$5,A!E$8)>0,"R","")

I would like to copy this formula down for Employee 2, 3, etc2 and the range in sheet refered change to according to the employee name.

I'm sorry if I didn't explain this correctly but whenever I pull down the formula from Employee 1, the range refered is still of Employee 1 and not Employee 2.
How can I make the range respond to the range Im pulling the formula to such as, Employee 2 in Sheet A will find if any date in the row include in range of Employee 2 in Sheet B.
 

Attachments

  • rd.PNG
    rd.PNG
    15 KB · Views: 4
  • attendance.PNG
    attendance.PNG
    49.7 KB · Views: 4
  • abe2.PNG
    abe2.PNG
    52.2 KB · Views: 4

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,693
Office Version
  1. 365
Platform
  1. Windows
Will the employee names always be in the exact same order on both sheets?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,693
Office Version
  1. 365
Platform
  1. Windows
In that case you can use
Excel Formula:
=IF(COUNTIF('B'!$E5:$H5,E$8)>0,"R","")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,693
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,181,426
Messages
5,929,822
Members
436,697
Latest member
sunnypl

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