Change on another worksheet based on a drop-down

[yunesm]

I have a challenging problem... I will try and make this clear but please see the attached file for clarification.

I have a two worksheets: 'Master List' and 'Payroll'

The end user of this workbook will:
1) Go to worksheet 'Master List'
2) Use dropdown in column-A to select 'Active, Cancel or Hold'.
3) Status in column A needs to also change the status in on payroll tab.

The status is changed in the following manner:
1) status changes on worksheet 'Master List'
2) ID number in column F on Master List (ID_NUM) is located on Payroll tab to ID row
3) Based on the date the status will change on the now of ID_Num and the column within that row will need to change appropriate to the date. For example, if today is 8/11 and I am changing ID 94782 (row 2) to Cancel then my status would change in the payroll tab in column W on the payroll tab to 'X' (Cancel = X, Hold = Hold, Active = dollar amount)
4) When the status is changed to active the dollar amount on worksheet Master List in the cost column (K) is the value that is used.

For the macro to run clearly I need to evaluate whether an individual has an ID number assigned and that a cost is selected on the Master List worksheet.

Thoughts???

[Richard Buttrey]

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]

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]

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]

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!