Check if cells contain in dynamic range from different sheet

goriamir

New Member
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
15 KB · Views: 4
• attendance.PNG
49.7 KB · Views: 4
• 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
Will the employee names always be in the exact same order on both sheets?

goriamir

New Member
Will the employee names always be in the exact same order on both sheets?
Yes it will

Fluff

MrExcel MVP, Moderator
In that case you can use
Excel Formula:
``=IF(COUNTIF('B'!\$E5:\$H5,E\$8)>0,"R","")``

goriamir

New Member
In that case you can use
Excel Formula:
``=IF(COUNTIF('B'!\$E5:\$H5,E\$8)>0,"R","")``
work like a charm! thank you!!

Fluff

MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
12
Views
268
Replies
6
Views
151
Replies
3
Views
57
Replies
1
Views
66
Replies
4
Views
80

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.

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

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