Change on another worksheet based on a drop-down

**Richard Buttrey** · 08-12-2011, 10:30 AM

Hi,

You don't need a macro. One way is in W2 on the Payroll sheet

=IF(AND('Master List'!$A$1>V$1,'Master List'!$A$1<=W$1),IF(INDEX('Master List'!$A:$A,MATCH($D2,'Master List'!$F:$F,FALSE),1)="Active",INDEX('Master List'!$K:$K,MATCH($D2,'Master List'!$F:$F,FALSE),1),IF(INDEX('Master List'!$A:$A,MATCH($D2,'Master List'!$F:$F,FALSE),1)="Hold","Hold","Cancel")),"X")

Copy down and across as needed after you've converted past weeks to hard values.

Regards

**yunesm** · 08-12-2011, 10:35 AM

Sorry, I ommitted something that was critical. The reason why I wasn't using a formula for this is becuase I need to be able to see past changes. So if someone cancels and then the next week activates and then puts it on hold I need to be able to see the status they were at during those payroll periods.

The formula works perfectly but it changes the past status which may or may not be accurate.

Does that make sense?

**Richard Buttrey** · 08-12-2011, 10:40 AM

Hi,

If I understand you, that's why I said that before changing the date and copying the formula across you need to range value the previous formulas - or write a small macro to do it for you. You could also get the macro to automatically add the formula I gave you if you want.

Regards

**yunesm** · 08-12-2011, 10:58 AM

Thanks Richard, I unerstand what you're suggesting.

This is what I'll do:
I'm going write a macro to fire whenever a cell is changed in Column-A on the Master List, it will find the interestion between that person's row and date, insert the formula and replace it with values immediately. I think this will give me what I need and it will be easy enough to write.

Thanks for helping me think this one out!

Thanks again!