Hi, so I have a spreadsheet that we enter employee schedule/bid changes into if they take a new bid. Each bid is assigned a number and from there contains different information across the row pertaining to that bid (days off, hourly rate, Type of job and some other points of information)
The spreadsheet contains a new bid tab and an old bid tab. The new bid tab is where we enter the bid/schedule that they will be going to. There is a moves tab which across several rows actually compares data between their old bid and their new bid. If any of the information is not identical, it will then display the information from the NEW bid. So example, if their old bid had them making 17.50 per hour and their new bid had them making 17.80 per hour, the cell would display 17.80 per hour. If the amounts were equal, then it displays nothing because there is no change to be made.
That said, the formulas on the moves tab utilize the name manager. The name manager has the information from some other tabs and that is used in the index/match formulas. Here are all the headers that appear on the Moves tab by column which are lettered accordingly:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A. New Bid[/TD]
[TD]B. Old Bid[/TD]
[TD]C. LM #[/TD]
[TD]D. Name[/TD]
[TD]E. PS #[/TD]
[TD]F. Sort Code[/TD]
[TD]G. Job[/TD]
[TD]H. Hours[/TD]
[TD]I. Clock[/TD]
[TD]J. Pay Rule[/TD]
[TD]K. Dept[/TD]
[TD]L. Rate[/TD]
[TD]N. Accruals[/TD]
[TD]O. Reset Accruals[/TD]
[TD]P. Position Change[/TD]
[TD]Q. Vacation Pkg[/TD]
[TD]R. % of Rate[/TD]
[/TR]
</tbody>[/TABLE]
The above headers match the headers contained on two other tabs, one tab called Shift Info for employees at one group of pay (those with the company before 2002) and another tab called Rates for employees at another group of pay (those with the company 2002 and after)
Both tabs, even though named differently contain all of the same information in all headers. The exception is the rates of Pay. In the Shift Info tab, the rates of pay are higher while in the Rates tab, they are lower.
Here are two sample bids and their information:
Bid Days off Shift Dept Pay Rule Rate Sort Code Accruals Clock Job Code Vac
[TABLE="width: 831"]
<tbody>[TR]
[TD]14[/TD]
[TD]S/S[/TD]
[TD]11P-7A - (43 PERISH)[/TD]
[TD]C67G[/TD]
[TD]warehouse[/TD]
[TD]23.82[/TD]
[TD]C65L[/TD]
[TD]8[/TD]
[TD]1 OR 6[/TD]
[TD][/TD]
[TD]LIFT[/TD]
[TD]P[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]S/S[/TD]
[TD]11P-7A - (43 PERISH)[/TD]
[TD]C67G[/TD]
[TD]warehouse[/TD]
[TD]23.97[/TD]
[TD]C65L[/TD]
[TD]8[/TD]
[TD]1 OR 6[/TD]
[TD][/TD]
[TD]FR LIFT[/TD]
[TD]P[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
So as you can see between these 2 bids, the only differences are in the Pay rate and in the Job type
In the moves tab, the formula in the job column reads thus:
=IF(OR(AND(ISBLANK($A246),ISBLANK($B246)),$A246=$B246, INDEX(SHIFT_INFO,MATCH($B246,SHIFT_BID,0),MATCH(G$4,SHIFT_HEADER,0))=INDEX(SHIFT_INFO,MATCH($A246,SHIFT_BID,0),MATCH(G$4,SHIFT_HEADER,0))), "", INDEX(SHIFT_INFO,MATCH($A246,SHIFT_BID,0),MATCH(G$4,SHIFT_HEADER,0)))
In this particular case, the employee has switched from a Lift Bid to a Freezer lift bid, so the New bid is column A which is bid 15 above and the old bid is column B and is bid 14 above. So this formula is comparing 2 lookups in the case of Job type. And if it sees that the Job types are equal, it will do nothing in the cell. If it sees they are different however, it will display the job type of the NEW bid 15 which is a FR Lift.
The majority of the rows in the moves tab work like that, They match the headers of the moves tab with the headers of either the shift info tab or rates tabs and then yield the information corresponding to that header. The only column that is different, however, is the rates column and this is due to using two different rates, depending on when the employees were hired. This is where I noticed that the formula given to us actually didn't quite encompass what we needed it to encompass. It reads thus:
=IF(OR(A246=B246, K246=""),"",IF($R246="",INDEX(SHIFT_INFO,MATCH($A246,SHIFT_BID,0),MATCH(L$4,SHIFT_HEADER,0)),INDEX(RATE,MATCH($A246,BID_NO,0),MATCH($R246,RATES,0))))
Okay, so when it comes to rates of pay(column L), this is where column R on the moves tab titled "% of rate" comes in. If that column is blank, then it will pull rates according to what is contained in the Shift Info Tab. If Column R DOES have something in it, it will pull information from the rates tab according to those pay rates. Column L seems to look for department code in K and when it doesn't see anything, it just won't put in a rate. The problem is, that even though the department is identical, the rate is not.
Here is the specific problem I found and I'm not sure how to reword the formula to catch this, in the example bids shown above. Bids 14 and 15 are both the same department C67G which is why column K of course yields nothing. The problem though is that there is a $0.15 pay difference between the 2 jobs. How does the formula need to be worded so that even though the departments are the same, it will see that there is a different pay rate and that it needs to show it? Bear in mind, it still needs to take into account if column R has anything in it.
I realize this is very involved and if I need to give more information than what I have, I can do so, or even provide a blank template of the file with all the peoples names taken out and just the formulas. Please let me know. Apologies for the alignment issues of the headers above in some cases. Thank you so much for your time!
The spreadsheet contains a new bid tab and an old bid tab. The new bid tab is where we enter the bid/schedule that they will be going to. There is a moves tab which across several rows actually compares data between their old bid and their new bid. If any of the information is not identical, it will then display the information from the NEW bid. So example, if their old bid had them making 17.50 per hour and their new bid had them making 17.80 per hour, the cell would display 17.80 per hour. If the amounts were equal, then it displays nothing because there is no change to be made.
That said, the formulas on the moves tab utilize the name manager. The name manager has the information from some other tabs and that is used in the index/match formulas. Here are all the headers that appear on the Moves tab by column which are lettered accordingly:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A. New Bid[/TD]
[TD]B. Old Bid[/TD]
[TD]C. LM #[/TD]
[TD]D. Name[/TD]
[TD]E. PS #[/TD]
[TD]F. Sort Code[/TD]
[TD]G. Job[/TD]
[TD]H. Hours[/TD]
[TD]I. Clock[/TD]
[TD]J. Pay Rule[/TD]
[TD]K. Dept[/TD]
[TD]L. Rate[/TD]
[TD]N. Accruals[/TD]
[TD]O. Reset Accruals[/TD]
[TD]P. Position Change[/TD]
[TD]Q. Vacation Pkg[/TD]
[TD]R. % of Rate[/TD]
[/TR]
</tbody>[/TABLE]
The above headers match the headers contained on two other tabs, one tab called Shift Info for employees at one group of pay (those with the company before 2002) and another tab called Rates for employees at another group of pay (those with the company 2002 and after)
Both tabs, even though named differently contain all of the same information in all headers. The exception is the rates of Pay. In the Shift Info tab, the rates of pay are higher while in the Rates tab, they are lower.
Here are two sample bids and their information:
Bid Days off Shift Dept Pay Rule Rate Sort Code Accruals Clock Job Code Vac
[TABLE="width: 831"]
<tbody>[TR]
[TD]14[/TD]
[TD]S/S[/TD]
[TD]11P-7A - (43 PERISH)[/TD]
[TD]C67G[/TD]
[TD]warehouse[/TD]
[TD]23.82[/TD]
[TD]C65L[/TD]
[TD]8[/TD]
[TD]1 OR 6[/TD]
[TD][/TD]
[TD]LIFT[/TD]
[TD]P[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]S/S[/TD]
[TD]11P-7A - (43 PERISH)[/TD]
[TD]C67G[/TD]
[TD]warehouse[/TD]
[TD]23.97[/TD]
[TD]C65L[/TD]
[TD]8[/TD]
[TD]1 OR 6[/TD]
[TD][/TD]
[TD]FR LIFT[/TD]
[TD]P[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
So as you can see between these 2 bids, the only differences are in the Pay rate and in the Job type
In the moves tab, the formula in the job column reads thus:
=IF(OR(AND(ISBLANK($A246),ISBLANK($B246)),$A246=$B246, INDEX(SHIFT_INFO,MATCH($B246,SHIFT_BID,0),MATCH(G$4,SHIFT_HEADER,0))=INDEX(SHIFT_INFO,MATCH($A246,SHIFT_BID,0),MATCH(G$4,SHIFT_HEADER,0))), "", INDEX(SHIFT_INFO,MATCH($A246,SHIFT_BID,0),MATCH(G$4,SHIFT_HEADER,0)))
In this particular case, the employee has switched from a Lift Bid to a Freezer lift bid, so the New bid is column A which is bid 15 above and the old bid is column B and is bid 14 above. So this formula is comparing 2 lookups in the case of Job type. And if it sees that the Job types are equal, it will do nothing in the cell. If it sees they are different however, it will display the job type of the NEW bid 15 which is a FR Lift.
The majority of the rows in the moves tab work like that, They match the headers of the moves tab with the headers of either the shift info tab or rates tabs and then yield the information corresponding to that header. The only column that is different, however, is the rates column and this is due to using two different rates, depending on when the employees were hired. This is where I noticed that the formula given to us actually didn't quite encompass what we needed it to encompass. It reads thus:
=IF(OR(A246=B246, K246=""),"",IF($R246="",INDEX(SHIFT_INFO,MATCH($A246,SHIFT_BID,0),MATCH(L$4,SHIFT_HEADER,0)),INDEX(RATE,MATCH($A246,BID_NO,0),MATCH($R246,RATES,0))))
Okay, so when it comes to rates of pay(column L), this is where column R on the moves tab titled "% of rate" comes in. If that column is blank, then it will pull rates according to what is contained in the Shift Info Tab. If Column R DOES have something in it, it will pull information from the rates tab according to those pay rates. Column L seems to look for department code in K and when it doesn't see anything, it just won't put in a rate. The problem is, that even though the department is identical, the rate is not.
Here is the specific problem I found and I'm not sure how to reword the formula to catch this, in the example bids shown above. Bids 14 and 15 are both the same department C67G which is why column K of course yields nothing. The problem though is that there is a $0.15 pay difference between the 2 jobs. How does the formula need to be worded so that even though the departments are the same, it will see that there is a different pay rate and that it needs to show it? Bear in mind, it still needs to take into account if column R has anything in it.
I realize this is very involved and if I need to give more information than what I have, I can do so, or even provide a blank template of the file with all the peoples names taken out and just the formulas. Please let me know. Apologies for the alignment issues of the headers above in some cases. Thank you so much for your time!
Last edited:
