Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace based on look up table
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace based on look up table
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace based on look up table
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace based on look up table
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace based on look up table
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. "merjet" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace based on neighboring cells? | Excel Discussion (Misc queries) | |||
Find/Replace based on CF result | Excel Programming | |||
Find and Replace - based on font | Excel Programming | |||
Find and Replace - based on font | Excel Programming | |||
Replace based on combobox value | Excel Programming |