drag horizontally a formula cell and increase the vertical reference

[a_hatzidakis]

Hello everyone, I'm facing a problem with a book i'm currently working on, heres the thing: In the first sheet thers a vertical calendar with some names next to each day of the month. In the second sheet there is a horizontal calendar with a first column the one with the list of the names used in the first sheet. When a name is displayed next to a a date in sheet1 I want a "yes" to appear in the date next to the name in sheet 2. I have found a formula that does the work (see first printscreen) and I want to drag it horizontally in sheet2 and increase the reference vertically in sheet1. I hope I explained what I need well, otherwise I can refrase.

sheet1.jpg
sheet2.jpg

PS: Sorry for the greek language in my MS Office

[sandy666]

To attach an Excel file to your post,

• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[Glenn Kennedy]

removed by GK

[JohnTopley]

try

in B2 of "Sheet2"

=IF(SUMPRODUCT(--(Sheet1!$A$2:$A$32=Sheet2!B$1)*--(Sheet1!$B$2:$Z$32=$A2)),"Yes","No")

Copy across and down

Change highlighted range to max number of names in a row