replace based on look up table

[Guest]

Hi. I have a payroll workbook. Each row has a Cost Center and a Fiscal Year.
The Fiscal Year for each Cost Center changes based on the calendar month. I
have been hard coding the changes in the Fiscal Years but my boss wants a
solution that doesn't require hard coding. He suggested having a look up
table that gets revised when necessary and have some code that used that to
replace the Cost Center Fiscal Year combos in the payroll workbook. I don't
have any idea how to start. Help/suggestions/ideas would be appreciated.
Thanks in advance.

 

[merjet]

Consider adding another column First Fiscal Month. When the calendar
month is changed to that month, the Fiscal Year is bumped by one via a
Worksheet_Change() event. To avoid accidental bumps, use an IF in the
VBA code to test that the new Fiscal Year and First Fiscal Month
aren't more than, say, a couple of months later than Now().

Hth,
Merjet

 

[Guest]

Hmmm....interesting idea. Problem there is I would have to code the start
date of each Cost Center and the rows change every two weeks (pay periods) so
I need something kinda static....any other thoughts? I've tried doing and If
Then with a loop but can't get it to work. Thanks again.

 

[merjet]

I don't get your second point. If the calendar month changed, the
Fiscal Year would change only if the calendar month matched the First
Fiscal Month. The Fiscal Year would be pretty static.

Hth,
Merjet

 

[Guest]

Thanks for getting back to me. I guess I just aren't following your
suggestion. I did an =mid to pull out the Cost Center and Fiscal Year from
the long string we have, did a paste special value, and then tried to do a
VLookup to another worksheet that has a master list of Cost Center and Fiscal
Years but it won't work. Funny, if I manually type what I pulled out using
the =mid, it works like a charm. I really appreciate your willingness to
help.