Excel formula

[jayesh]

There are 25 employees in my company and I need to give them a single task on all 7 days a week. Every one has theire weekly off on different days. Is there a random function with some condition that I can use so that thier name appears on a perticular day of the month and that day should not be on their weekly off.

 

[machopicho]

Something like this?

=IF(VLOOKUP(INT(RANDBETWEEN(1,25),A2:D26;3)=VLOOKUP(INT(RANDBETWEEN(1,25),A2:D26;4),"WEEK OFF=DAYMONTH",VLOOKUP(INT(RANDBETWEEN(1,25),A2:D26;3)

 

[jayesh]

It dose not work.
I have list of employee and their weekly off on one sheet.
and in second sheet I have April1 thru April 30. I need the employee name beside each date but not on their day of weekly off.

 

[machopicho]

In which columns of sheet 1 a 2?
Expalin your sheet layout...

 

[jayesh]

Sheet 1 - Column A has Name
Column B has Weekly off(eg. Sunday)

Sheet 2 - Column A has Dates (April 1 thru April 30)

In sheet 2 Column B I need names without weekly off days.

 

[machopicho]

Your situation:

Formula:

=IF(INDIRECT("'Table A'!B"&INT(RANDBETWEEN(1;25))+1;4)=TEXT(DAY(INDIRECT("A"&INT(RANDBETWEEN(1;25))+1));"DDDD");"W.O. of "&INDIRECT("'Table A'!A"&INT(RANDBETWEEN(1;25))+1;4);INDIRECT("'Table A'!A"&INT(RANDBETWEEN(1;25))+1;4))

Put in B2 of sheet2 and drag down.

 

[machopicho]

ATTENTION:
Substitute ';' with ',' in formula

 

[jayesh]

Thanks,
this works fine but one more change. In your formula A If a person has his weekly off on saturday, his name appears on satruday and I dont want that. If a person has weekly off on saturday then his name should not appear on saturdays.