| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Payroll sheet comparisons
Hi there.
I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
Ben,
You will (probably) need VBA code to do this as we have to find all holiday days between two dates. Couple of questions: What's in C51 in your example? How do we know which pay period we are in: I assume rows 51/52 are start/end dates of a given period? If you prefer, post the workbook and I'll have a look ) "ben simpson" wrote: Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
Hi topper.
C51 is a =TODAY() and you're right... rows 51 and 52 are the dates of the pay cycle. Unfortuneately I can't post because the powers that be will not allow it. I know that the IT dept. is very strict on the running of any VBA code (way too over protective), so I was looking for a way to do it through some sort of formula. But, I'm very willing to listen, and if necessary try to get that OK'd. Thanks again...Ben "Toppers" wrote: Ben, You will (probably) need VBA code to do this as we have to find all holiday days between two dates. Couple of questions: What's in C51 in your example? How do we know which pay period we are in: I assume rows 51/52 are start/end dates of a given period? If you prefer, post the workbook and I'll have a look ) "ben simpson" wrote: Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
I told you this would be complicated!
I'm assuming that: row 51 = start of period row 52 = end of period Try this: Entered as an array using the key combo of CTRL,SHIF,ENTER: =INDEX(B$4:B$103,SMALL(IF((A$4:A$103=INDEX(F$51:A F$51,MATCH(C$51,F$51:AF$51,1)))*(A$4:A$103<=INDEX( F$52:AF$52,MATCH(C$51,F$51:AF$51,1))),ROW(A$4:A$10 3)-ROW(A$4)+1),ROWS($1:1))) Copy down until you get #NUM! errors. If you want an error trap the formula will be twice as long!!!!!!!! Biff "ben simpson" wrote in message ... Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
Ben,
this is probably the minimum VBA we can get away with. It is a function which returns your holiday list between two dates. It assumes the dates in your holiday data (a4:A103) is in ascending date order and I have called this data "HOLIDAYS" - a named range. in C28 put: =AccruedHolidays(<startdate,<enddate) e.g. =AccruedHolidays("26/12/2005", "21/01/2006") I hope you can put this in your w/book. HTH ============================================= Function AccruedHolidays(ByVal ppStart As Date, ByVal ppEnd As Date) holStr = "" For Each cell In Range("Holidays") If CLng(cell.Value) CLng(ppEnd) Then Exit For Else If CLng(cell.Value) = CLng(ppStart) Then holStr = holStr & cell.Offset(0, 1) & "," End If End If Next cell AccruedHolidays = Left(holStr, Len(holStr) - 1) End Function "ben simpson" wrote: Hi topper. C51 is a =TODAY() and you're right... rows 51 and 52 are the dates of the pay cycle. Unfortuneately I can't post because the powers that be will not allow it. I know that the IT dept. is very strict on the running of any VBA code (way too over protective), so I was looking for a way to do it through some sort of formula. But, I'm very willing to listen, and if necessary try to get that OK'd. Thanks again...Ben "Toppers" wrote: Ben, You will (probably) need VBA code to do this as we have to find all holiday days between two dates. Couple of questions: What's in C51 in your example? How do we know which pay period we are in: I assume rows 51/52 are start/end dates of a given period? If you prefer, post the workbook and I'll have a look ) "ben simpson" wrote: Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
Yikes...you weren't kidding. I copied the formula down, and pasted it in. I
had to change the cell references to the correct sheet (sorry if I wasn't clear). Now it giving me a #REF error, and I don't know why. Here's what i have now: As an array. =INDEX('Data Sheet'!B4:B103,SMALL(IF(('Data Sheet'!A4:A103=INDEX(F$51:AF$51,MATCH(C$51,F$51:A F$51,1)))*('Data Sheet'!A4:A103<=INDEX(F$51:AF$52,MATCH(C$51,F$51:A F$51,1))),ROW('Data Sheet'!A4:A103)-ROW('Data Sheet'!A4)+1),ROWS($1:1))) I have no clue as to what you meant by error trap the formula. Can you clarify that for me?.....Thanks....Ben "Biff" wrote: I told you this would be complicated! I'm assuming that: row 51 = start of period row 52 = end of period Try this: Entered as an array using the key combo of CTRL,SHIF,ENTER: =INDEX(B$4:B$103,SMALL(IF((A$4:A$103=INDEX(F$51:A F$51,MATCH(C$51,F$51:AF$51,1)))*(A$4:A$103<=INDEX( F$52:AF$52,MATCH(C$51,F$51:AF$51,1))),ROW(A$4:A$10 3)-ROW(A$4)+1),ROWS($1:1))) Copy down until you get #NUM! errors. If you want an error trap the formula will be twice as long!!!!!!!! Biff "ben simpson" wrote in message ... Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
This portion:
<=INDEX(F$51:AF$52,MATCH(C$51,F$51:AF$51,1))) Should be: <=INDEX(F$52:AF$52,MATCH(C$51,F$51:AF$51,1))) Also, since there may be more than one match, when you copy the formula down you don't want these ranges to increment, do you? 'Data Sheet'!B4:B103 'Data Sheet'!A4:A103 So, make the row references absolute: 'Data Sheet'!B$4:B$103 'Data Sheet'!A$4:A$103 I have no clue as to what you meant by error trap the formula. Can you clarify that for me? When you got the #REF! error, an error trap is a portion of the formula that "anticipates" when errors will be generated and prevents them from being displayed. Let's get the basic formula working first then we'll deal with errors later! Biff "ben simpson" wrote in message ... Yikes...you weren't kidding. I copied the formula down, and pasted it in. I had to change the cell references to the correct sheet (sorry if I wasn't clear). Now it giving me a #REF error, and I don't know why. Here's what i have now: As an array. =INDEX('Data Sheet'!B4:B103,SMALL(IF(('Data Sheet'!A4:A103=INDEX(F$51:AF$51,MATCH(C$51,F$51:A F$51,1)))*('Data Sheet'!A4:A103<=INDEX(F$51:AF$52,MATCH(C$51,F$51:A F$51,1))),ROW('Data Sheet'!A4:A103)-ROW('Data Sheet'!A4)+1),ROWS($1:1))) I have no clue as to what you meant by error trap the formula. Can you clarify that for me?.....Thanks....Ben "Biff" wrote: I told you this would be complicated! I'm assuming that: row 51 = start of period row 52 = end of period Try this: Entered as an array using the key combo of CTRL,SHIF,ENTER: =INDEX(B$4:B$103,SMALL(IF((A$4:A$103=INDEX(F$51:A F$51,MATCH(C$51,F$51:AF$51,1)))*(A$4:A$103<=INDEX( F$52:AF$52,MATCH(C$51,F$51:AF$51,1))),ROW(A$4:A$10 3)-ROW(A$4)+1),ROWS($1:1))) Copy down until you get #NUM! errors. If you want an error trap the formula will be twice as long!!!!!!!! Biff "ben simpson" wrote in message ... Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
I got the areas fixed that you pointed out. Now I get a #NUM error. i need
guidance from here (obviously...hehehe). This is what it looks like. In an array of course. =INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data Sheet'!A$4:A$103=INDEX(F$51:AF$51,MATCH(C$51,F$51 :AF$51,1)))*('Data Sheet'!A$4:A$103<=INDEX(F$52:AF$52,MATCH(C$51,F$51 :AF$51,1))),ROW('Data Sheet'!A$4:A$103)-ROW('Data Sheet'!A$4)+1),ROWS($1:1))) Where does it go from here?.....Thanks....Ben "Biff" wrote: This portion: <=INDEX(F$51:AF$52,MATCH(C$51,F$51:AF$51,1))) Should be: <=INDEX(F$52:AF$52,MATCH(C$51,F$51:AF$51,1))) Also, since there may be more than one match, when you copy the formula down you don't want these ranges to increment, do you? 'Data Sheet'!B4:B103 'Data Sheet'!A4:A103 So, make the row references absolute: 'Data Sheet'!B$4:B$103 'Data Sheet'!A$4:A$103 I have no clue as to what you meant by error trap the formula. Can you clarify that for me? When you got the #REF! error, an error trap is a portion of the formula that "anticipates" when errors will be generated and prevents them from being displayed. Let's get the basic formula working first then we'll deal with errors later! Biff "ben simpson" wrote in message ... Yikes...you weren't kidding. I copied the formula down, and pasted it in. I had to change the cell references to the correct sheet (sorry if I wasn't clear). Now it giving me a #REF error, and I don't know why. Here's what i have now: As an array. =INDEX('Data Sheet'!B4:B103,SMALL(IF(('Data Sheet'!A4:A103=INDEX(F$51:AF$51,MATCH(C$51,F$51:A F$51,1)))*('Data Sheet'!A4:A103<=INDEX(F$51:AF$52,MATCH(C$51,F$51:A F$51,1))),ROW('Data Sheet'!A4:A103)-ROW('Data Sheet'!A4)+1),ROWS($1:1))) I have no clue as to what you meant by error trap the formula. Can you clarify that for me?.....Thanks....Ben "Biff" wrote: I told you this would be complicated! I'm assuming that: row 51 = start of period row 52 = end of period Try this: Entered as an array using the key combo of CTRL,SHIF,ENTER: =INDEX(B$4:B$103,SMALL(IF((A$4:A$103=INDEX(F$51:A F$51,MATCH(C$51,F$51:AF$51,1)))*(A$4:A$103<=INDEX( F$52:AF$52,MATCH(C$51,F$51:AF$51,1))),ROW(A$4:A$10 3)-ROW(A$4)+1),ROWS($1:1))) Copy down until you get #NUM! errors. If you want an error trap the formula will be twice as long!!!!!!!! Biff "ben simpson" wrote in message ... Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
#NUM! would mean there are no matches.
Send me an email..........if you can't send the file, just make a list of these entries: I need to know *EXACTLY* what you have in A4:A103, B4:B103, F51:AF51, F52:AF52, C51. Don't just tell me that you have dates! I need to know WHAT DATES!!!!!!!!!! My address is: xlcanhelpatcomcastperiodnet Remove "can" and change the obvious. Biff "ben simpson" wrote in message ... I got the areas fixed that you pointed out. Now I get a #NUM error. i need guidance from here (obviously...hehehe). This is what it looks like. In an array of course. =INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data Sheet'!A$4:A$103=INDEX(F$51:AF$51,MATCH(C$51,F$51 :AF$51,1)))*('Data Sheet'!A$4:A$103<=INDEX(F$52:AF$52,MATCH(C$51,F$51 :AF$51,1))),ROW('Data Sheet'!A$4:A$103)-ROW('Data Sheet'!A$4)+1),ROWS($1:1))) Where does it go from here?.....Thanks....Ben "Biff" wrote: This portion: <=INDEX(F$51:AF$52,MATCH(C$51,F$51:AF$51,1))) Should be: <=INDEX(F$52:AF$52,MATCH(C$51,F$51:AF$51,1))) Also, since there may be more than one match, when you copy the formula down you don't want these ranges to increment, do you? 'Data Sheet'!B4:B103 'Data Sheet'!A4:A103 So, make the row references absolute: 'Data Sheet'!B$4:B$103 'Data Sheet'!A$4:A$103 I have no clue as to what you meant by error trap the formula. Can you clarify that for me? When you got the #REF! error, an error trap is a portion of the formula that "anticipates" when errors will be generated and prevents them from being displayed. Let's get the basic formula working first then we'll deal with errors later! Biff "ben simpson" wrote in message ... Yikes...you weren't kidding. I copied the formula down, and pasted it in. I had to change the cell references to the correct sheet (sorry if I wasn't clear). Now it giving me a #REF error, and I don't know why. Here's what i have now: As an array. =INDEX('Data Sheet'!B4:B103,SMALL(IF(('Data Sheet'!A4:A103=INDEX(F$51:AF$51,MATCH(C$51,F$51:A F$51,1)))*('Data Sheet'!A4:A103<=INDEX(F$51:AF$52,MATCH(C$51,F$51:A F$51,1))),ROW('Data Sheet'!A4:A103)-ROW('Data Sheet'!A4)+1),ROWS($1:1))) I have no clue as to what you meant by error trap the formula. Can you clarify that for me?.....Thanks....Ben "Biff" wrote: I told you this would be complicated! I'm assuming that: row 51 = start of period row 52 = end of period Try this: Entered as an array using the key combo of CTRL,SHIF,ENTER: =INDEX(B$4:B$103,SMALL(IF((A$4:A$103=INDEX(F$51:A F$51,MATCH(C$51,F$51:AF$51,1)))*(A$4:A$103<=INDEX( F$52:AF$52,MATCH(C$51,F$51:AF$51,1))),ROW(A$4:A$10 3)-ROW(A$4)+1),ROWS($1:1))) Copy down until you get #NUM! errors. If you want an error trap the formula will be twice as long!!!!!!!! Biff "ben simpson" wrote in message ... Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
If anybody is still following this thread........
The OP contacted me via email and provided a complete description of his file layout and data. The problem was in our (the respondents) interpretation of his post. The one range of dates posted as being in F51:AF52 was actually a single row, row 51, but the two rows were merged! F51 was the start of the pay period and AF51 was the end of the pay period. The resulting formula was: =INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data Sheet'!A$4:A$103=C$51)*('Data Sheet'!A$4:A$103<=AF$51),ROW('Data Sheet'!A$4:A$103)-ROW('Data Sheet'!A$4)+1),ROWS($1:1))) Used CF to "hide" any errors. Biff "Biff" wrote in message ... #NUM! would mean there are no matches. Send me an email..........if you can't send the file, just make a list of these entries: I need to know *EXACTLY* what you have in A4:A103, B4:B103, F51:AF51, F52:AF52, C51. Don't just tell me that you have dates! I need to know WHAT DATES!!!!!!!!!! My address is: xlcanhelpatcomcastperiodnet Remove "can" and change the obvious. Biff "ben simpson" wrote in message ... I got the areas fixed that you pointed out. Now I get a #NUM error. i need guidance from here (obviously...hehehe). This is what it looks like. In an array of course. =INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data Sheet'!A$4:A$103=INDEX(F$51:AF$51,MATCH(C$51,F$51 :AF$51,1)))*('Data Sheet'!A$4:A$103<=INDEX(F$52:AF$52,MATCH(C$51,F$51 :AF$51,1))),ROW('Data Sheet'!A$4:A$103)-ROW('Data Sheet'!A$4)+1),ROWS($1:1))) Where does it go from here?.....Thanks....Ben "Biff" wrote: This portion: <=INDEX(F$51:AF$52,MATCH(C$51,F$51:AF$51,1))) Should be: <=INDEX(F$52:AF$52,MATCH(C$51,F$51:AF$51,1))) Also, since there may be more than one match, when you copy the formula down you don't want these ranges to increment, do you? 'Data Sheet'!B4:B103 'Data Sheet'!A4:A103 So, make the row references absolute: 'Data Sheet'!B$4:B$103 'Data Sheet'!A$4:A$103 I have no clue as to what you meant by error trap the formula. Can you clarify that for me? When you got the #REF! error, an error trap is a portion of the formula that "anticipates" when errors will be generated and prevents them from being displayed. Let's get the basic formula working first then we'll deal with errors later! Biff "ben simpson" wrote in message ... Yikes...you weren't kidding. I copied the formula down, and pasted it in. I had to change the cell references to the correct sheet (sorry if I wasn't clear). Now it giving me a #REF error, and I don't know why. Here's what i have now: As an array. =INDEX('Data Sheet'!B4:B103,SMALL(IF(('Data Sheet'!A4:A103=INDEX(F$51:AF$51,MATCH(C$51,F$51:A F$51,1)))*('Data Sheet'!A4:A103<=INDEX(F$51:AF$52,MATCH(C$51,F$51:A F$51,1))),ROW('Data Sheet'!A4:A103)-ROW('Data Sheet'!A4)+1),ROWS($1:1))) I have no clue as to what you meant by error trap the formula. Can you clarify that for me?.....Thanks....Ben "Biff" wrote: I told you this would be complicated! I'm assuming that: row 51 = start of period row 52 = end of period Try this: Entered as an array using the key combo of CTRL,SHIF,ENTER: =INDEX(B$4:B$103,SMALL(IF((A$4:A$103=INDEX(F$51:A F$51,MATCH(C$51,F$51:AF$51,1)))*(A$4:A$103<=INDEX( F$52:AF$52,MATCH(C$51,F$51:AF$51,1))),ROW(A$4:A$10 3)-ROW(A$4)+1),ROWS($1:1))) Copy down until you get #NUM! errors. If you want an error trap the formula will be twice as long!!!!!!!! Biff "ben simpson" wrote in message ... Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
Excellent job Biff. I really appreeciate the help that you gave me.....Ben
"Biff" wrote: If anybody is still following this thread........ The OP contacted me via email and provided a complete description of his file layout and data. The problem was in our (the respondents) interpretation of his post. The one range of dates posted as being in F51:AF52 was actually a single row, row 51, but the two rows were merged! F51 was the start of the pay period and AF51 was the end of the pay period. The resulting formula was: =INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data Sheet'!A$4:A$103=C$51)*('Data Sheet'!A$4:A$103<=AF$51),ROW('Data Sheet'!A$4:A$103)-ROW('Data Sheet'!A$4)+1),ROWS($1:1))) Used CF to "hide" any errors. Biff "Biff" wrote in message ... #NUM! would mean there are no matches. Send me an email..........if you can't send the file, just make a list of these entries: I need to know *EXACTLY* what you have in A4:A103, B4:B103, F51:AF51, F52:AF52, C51. Don't just tell me that you have dates! I need to know WHAT DATES!!!!!!!!!! My address is: xlcanhelpatcomcastperiodnet Remove "can" and change the obvious. Biff "ben simpson" wrote in message ... I got the areas fixed that you pointed out. Now I get a #NUM error. i need guidance from here (obviously...hehehe). This is what it looks like. In an array of course. =INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data Sheet'!A$4:A$103=INDEX(F$51:AF$51,MATCH(C$51,F$51 :AF$51,1)))*('Data Sheet'!A$4:A$103<=INDEX(F$52:AF$52,MATCH(C$51,F$51 :AF$51,1))),ROW('Data Sheet'!A$4:A$103)-ROW('Data Sheet'!A$4)+1),ROWS($1:1))) Where does it go from here?.....Thanks....Ben "Biff" wrote: This portion: <=INDEX(F$51:AF$52,MATCH(C$51,F$51:AF$51,1))) Should be: <=INDEX(F$52:AF$52,MATCH(C$51,F$51:AF$51,1))) Also, since there may be more than one match, when you copy the formula down you don't want these ranges to increment, do you? 'Data Sheet'!B4:B103 'Data Sheet'!A4:A103 So, make the row references absolute: 'Data Sheet'!B$4:B$103 'Data Sheet'!A$4:A$103 I have no clue as to what you meant by error trap the formula. Can you clarify that for me? When you got the #REF! error, an error trap is a portion of the formula that "anticipates" when errors will be generated and prevents them from being displayed. Let's get the basic formula working first then we'll deal with errors later! Biff "ben simpson" wrote in message ... Yikes...you weren't kidding. I copied the formula down, and pasted it in. I had to change the cell references to the correct sheet (sorry if I wasn't clear). Now it giving me a #REF error, and I don't know why. Here's what i have now: As an array. =INDEX('Data Sheet'!B4:B103,SMALL(IF(('Data Sheet'!A4:A103=INDEX(F$51:AF$51,MATCH(C$51,F$51:A F$51,1)))*('Data Sheet'!A4:A103<=INDEX(F$51:AF$52,MATCH(C$51,F$51:A F$51,1))),ROW('Data Sheet'!A4:A103)-ROW('Data Sheet'!A4)+1),ROWS($1:1))) I have no clue as to what you meant by error trap the formula. Can you clarify that for me?.....Thanks....Ben "Biff" wrote: I told you this would be complicated! I'm assuming that: row 51 = start of period row 52 = end of period Try this: Entered as an array using the key combo of CTRL,SHIF,ENTER: =INDEX(B$4:B$103,SMALL(IF((A$4:A$103=INDEX(F$51:A F$51,MATCH(C$51,F$51:AF$51,1)))*(A$4:A$103<=INDEX( F$52:AF$52,MATCH(C$51,F$51:AF$51,1))),ROW(A$4:A$10 3)-ROW(A$4)+1),ROWS($1:1))) Copy down until you get #NUM! errors. If you want an error trap the formula will be twice as long!!!!!!!! Biff "ben simpson" wrote in message ... Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Payroll sheet comparisons
You're welcome. Thanks for the feedback!
Biff "ben simpson" wrote in message ... Excellent job Biff. I really appreeciate the help that you gave me.....Ben "Biff" wrote: If anybody is still following this thread........ The OP contacted me via email and provided a complete description of his file layout and data. The problem was in our (the respondents) interpretation of his post. The one range of dates posted as being in F51:AF52 was actually a single row, row 51, but the two rows were merged! F51 was the start of the pay period and AF51 was the end of the pay period. The resulting formula was: =INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data Sheet'!A$4:A$103=C$51)*('Data Sheet'!A$4:A$103<=AF$51),ROW('Data Sheet'!A$4:A$103)-ROW('Data Sheet'!A$4)+1),ROWS($1:1))) Used CF to "hide" any errors. Biff "Biff" wrote in message ... #NUM! would mean there are no matches. Send me an email..........if you can't send the file, just make a list of these entries: I need to know *EXACTLY* what you have in A4:A103, B4:B103, F51:AF51, F52:AF52, C51. Don't just tell me that you have dates! I need to know WHAT DATES!!!!!!!!!! My address is: xlcanhelpatcomcastperiodnet Remove "can" and change the obvious. Biff "ben simpson" wrote in message ... I got the areas fixed that you pointed out. Now I get a #NUM error. i need guidance from here (obviously...hehehe). This is what it looks like. In an array of course. =INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data Sheet'!A$4:A$103=INDEX(F$51:AF$51,MATCH(C$51,F$51 :AF$51,1)))*('Data Sheet'!A$4:A$103<=INDEX(F$52:AF$52,MATCH(C$51,F$51 :AF$51,1))),ROW('Data Sheet'!A$4:A$103)-ROW('Data Sheet'!A$4)+1),ROWS($1:1))) Where does it go from here?.....Thanks....Ben "Biff" wrote: This portion: <=INDEX(F$51:AF$52,MATCH(C$51,F$51:AF$51,1))) Should be: <=INDEX(F$52:AF$52,MATCH(C$51,F$51:AF$51,1))) Also, since there may be more than one match, when you copy the formula down you don't want these ranges to increment, do you? 'Data Sheet'!B4:B103 'Data Sheet'!A4:A103 So, make the row references absolute: 'Data Sheet'!B$4:B$103 'Data Sheet'!A$4:A$103 I have no clue as to what you meant by error trap the formula. Can you clarify that for me? When you got the #REF! error, an error trap is a portion of the formula that "anticipates" when errors will be generated and prevents them from being displayed. Let's get the basic formula working first then we'll deal with errors later! Biff "ben simpson" wrote in message ... Yikes...you weren't kidding. I copied the formula down, and pasted it in. I had to change the cell references to the correct sheet (sorry if I wasn't clear). Now it giving me a #REF error, and I don't know why. Here's what i have now: As an array. =INDEX('Data Sheet'!B4:B103,SMALL(IF(('Data Sheet'!A4:A103=INDEX(F$51:AF$51,MATCH(C$51,F$51:A F$51,1)))*('Data Sheet'!A4:A103<=INDEX(F$51:AF$52,MATCH(C$51,F$51:A F$51,1))),ROW('Data Sheet'!A4:A103)-ROW('Data Sheet'!A4)+1),ROWS($1:1))) I have no clue as to what you meant by error trap the formula. Can you clarify that for me?.....Thanks....Ben "Biff" wrote: I told you this would be complicated! I'm assuming that: row 51 = start of period row 52 = end of period Try this: Entered as an array using the key combo of CTRL,SHIF,ENTER: =INDEX(B$4:B$103,SMALL(IF((A$4:A$103=INDEX(F$51:A F$51,MATCH(C$51,F$51:AF$51,1)))*(A$4:A$103<=INDEX( F$52:AF$52,MATCH(C$51,F$51:AF$51,1))),ROW(A$4:A$10 3)-ROW(A$4)+1),ROWS($1:1))) Copy down until you get #NUM! errors. If you want an error trap the formula will be twice as long!!!!!!!! Biff "ben simpson" wrote in message ... Hi there. I've been working on is a time sheet (and almost done with it). During our work periods throught the year we accrue holidays (just like vacation time). What I'm wanting to do is compare the present work period dates (I have the dates in ms date format in F51:AF52), to a list that I manually have looked up of the holiday dates (this is in the ms date format in A4:A103 on the data sheet). In cell C28 on the time sheet, I need the name of the holiday and/or holidays that have accrued during the pay period (thats in the cell next to dates in B4:B103 on the data sheet). Hope I haven't lost you yet. What I have: Data Sheet 38718 New Years Day 38733 Martin Luther King Jr. Day 38768 Presidents Day 38866 Memorial Day 38902 Fourth of July 38964 Labor Day 39044 Thanksgiving Day 39045 Day after Thanksgiving Day 39075 Christmas Eve 39076 Christmas Day What I've been using: =IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data Sheet'!A4:B103,2)) This only lists the last match that it finds, and not all the matches, which is what I actually need. What I need (in C28) if the pay period was from 12/26/2005 thru 01/21,2006: Accrued Holiday(s) For: New Years Day, Martin Luther King Jr. I think this is possible, I'm not savvy enough in Excel to figure this out on my own. This is way over my head. Any help is appreciated. Thanks....Ben |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lock tab in sheet 2 until cell in sheet one is completed | Excel Worksheet Functions | |||
| Index/Contents Sheet | Excel Discussion (Misc queries) | |||
| In Excel, how do you make one whole sheet equal to another. | Excel Discussion (Misc queries) | |||
| Clicking Cell Link Changes Cell on Another Sheet | Excel Discussion (Misc queries) | |||
| Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Linear Mode
