| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a match within a match
I have employees in column A, payroll info in column B. All payroll fields
are same for each employee (Fred and Tom both have gross wages) Rows are the pay periods done by date. I need to locate Freds Gross Wage on a given date. I cant seem to get the right functions/formulas nested together. Any help greatly appreciated. -- Lainey |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding a match within a match
One way (possibly):
=SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Need a better description of your layout. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... I have employees in column A, payroll info in column B. All payroll fields are same for each employee (Fred and Tom both have gross wages) Rows are the pay periods done by date. I need to locate Freds Gross Wage on a given date. I cant seem to get the right functions/formulas nested together. Any help greatly appreciated. -- Lainey |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding a match within a match
Typo:
=SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Should be: =SUMPRODUCT(--(A1:A10="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way (possibly): =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Need a better description of your layout. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... I have employees in column A, payroll info in column B. All payroll fields are same for each employee (Fred and Tom both have gross wages) Rows are the pay periods done by date. I need to locate Freds Gross Wage on a given date. I cant seem to get the right functions/formulas nested together. Any help greatly appreciated. -- Lainey |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding a match within a match
The "Register" worksheet looks like what is below but with about 10 types of
wages for each employee, approx 30 employees. I need to be able to pull to a summary pay period sheet for 1/5/08 for certain lines. I was doing it with a H or V Lookup and hard coding in the row or column number. Problem is, adding a line, messes up all the rows and columns. I need it to look up Freds Gross for 1/5/08, not necessarily in the same location as the dates change. A B C D 1/5/08 1/19/08 Fred Regular 500.00 625.00 Fred Overtime 100.00 125.00 Fred Gross 600.00 750.00 Fred FICA -30.00 -45.00 Tom Regular 300.00 350.00 Tom Overtime 75.00 80.00 Tom Gross 375.00 430.00 Tom FICA -23.00 -27.00 -- Lainey "T. Valko" wrote: Typo: =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Should be: =SUMPRODUCT(--(A1:A10="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way (possibly): =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Need a better description of your layout. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... I have employees in column A, payroll info in column B. All payroll fields are same for each employee (Fred and Tom both have gross wages) Rows are the pay periods done by date. I need to locate Freds Gross Wage on a given date. I cant seem to get the right functions/formulas nested together. Any help greatly appreciated. -- Lainey |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding a match within a match
See this screencap:
http://img508.imageshack.us/img508/7980/lookup22ju3.jpg -- Biff Microsoft Excel MVP "Lainey" wrote in message ... The "Register" worksheet looks like what is below but with about 10 types of wages for each employee, approx 30 employees. I need to be able to pull to a summary pay period sheet for 1/5/08 for certain lines. I was doing it with a H or V Lookup and hard coding in the row or column number. Problem is, adding a line, messes up all the rows and columns. I need it to look up Freds Gross for 1/5/08, not necessarily in the same location as the dates change. A B C D 1/5/08 1/19/08 Fred Regular 500.00 625.00 Fred Overtime 100.00 125.00 Fred Gross 600.00 750.00 Fred FICA -30.00 -45.00 Tom Regular 300.00 350.00 Tom Overtime 75.00 80.00 Tom Gross 375.00 430.00 Tom FICA -23.00 -27.00 -- Lainey "T. Valko" wrote: Typo: =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Should be: =SUMPRODUCT(--(A1:A10="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way (possibly): =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Need a better description of your layout. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... I have employees in column A, payroll info in column B. All payroll fields are same for each employee (Fred and Tom both have gross wages) Rows are the pay periods done by date. I need to locate Freds Gross Wage on a given date. I cant seem to get the right functions/formulas nested together. Any help greatly appreciated. -- Lainey |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding a match within a match
Thank you, I typed this in but I got a #N/A, will look again after a break.
Maybe I have another error somewhere -- Lainey "T. Valko" wrote: See this screencap: http://img508.imageshack.us/img508/7980/lookup22ju3.jpg -- Biff Microsoft Excel MVP "Lainey" wrote in message ... The "Register" worksheet looks like what is below but with about 10 types of wages for each employee, approx 30 employees. I need to be able to pull to a summary pay period sheet for 1/5/08 for certain lines. I was doing it with a H or V Lookup and hard coding in the row or column number. Problem is, adding a line, messes up all the rows and columns. I need it to look up Freds Gross for 1/5/08, not necessarily in the same location as the dates change. A B C D 1/5/08 1/19/08 Fred Regular 500.00 625.00 Fred Overtime 100.00 125.00 Fred Gross 600.00 750.00 Fred FICA -30.00 -45.00 Tom Regular 300.00 350.00 Tom Overtime 75.00 80.00 Tom Gross 375.00 430.00 Tom FICA -23.00 -27.00 -- Lainey "T. Valko" wrote: Typo: =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Should be: =SUMPRODUCT(--(A1:A10="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way (possibly): =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Need a better description of your layout. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... I have employees in column A, payroll info in column B. All payroll fields are same for each employee (Fred and Tom both have gross wages) Rows are the pay periods done by date. I need to locate Freds Gross Wage on a given date. I cant seem to get the right functions/formulas nested together. Any help greatly appreciated. -- Lainey |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding a match within a match
If you get a #N/A error it's coming from the MATCH function which means it
can't find a matching date. Make sure the lookup_date and the table dates are all TRUE excel dates. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... Thank you, I typed this in but I got a #N/A, will look again after a break. Maybe I have another error somewhere -- Lainey "T. Valko" wrote: See this screencap: http://img508.imageshack.us/img508/7980/lookup22ju3.jpg -- Biff Microsoft Excel MVP "Lainey" wrote in message ... The "Register" worksheet looks like what is below but with about 10 types of wages for each employee, approx 30 employees. I need to be able to pull to a summary pay period sheet for 1/5/08 for certain lines. I was doing it with a H or V Lookup and hard coding in the row or column number. Problem is, adding a line, messes up all the rows and columns. I need it to look up Freds Gross for 1/5/08, not necessarily in the same location as the dates change. A B C D 1/5/08 1/19/08 Fred Regular 500.00 625.00 Fred Overtime 100.00 125.00 Fred Gross 600.00 750.00 Fred FICA -30.00 -45.00 Tom Regular 300.00 350.00 Tom Overtime 75.00 80.00 Tom Gross 375.00 430.00 Tom FICA -23.00 -27.00 -- Lainey "T. Valko" wrote: Typo: =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Should be: =SUMPRODUCT(--(A1:A10="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way (possibly): =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Need a better description of your layout. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... I have employees in column A, payroll info in column B. All payroll fields are same for each employee (Fred and Tom both have gross wages) Rows are the pay periods done by date. I need to locate Freds Gross Wage on a given date. I cant seem to get the right functions/formulas nested together. Any help greatly appreciated. -- Lainey |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding a match within a match
THANK YOU THANK YOU THANK YOU!!!!!
I got it, cleaned up my match problem and it worked!:) Thanks so very much -- Lainey "T. Valko" wrote: If you get a #N/A error it's coming from the MATCH function which means it can't find a matching date. Make sure the lookup_date and the table dates are all TRUE excel dates. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... Thank you, I typed this in but I got a #N/A, will look again after a break. Maybe I have another error somewhere -- Lainey "T. Valko" wrote: See this screencap: http://img508.imageshack.us/img508/7980/lookup22ju3.jpg -- Biff Microsoft Excel MVP "Lainey" wrote in message ... The "Register" worksheet looks like what is below but with about 10 types of wages for each employee, approx 30 employees. I need to be able to pull to a summary pay period sheet for 1/5/08 for certain lines. I was doing it with a H or V Lookup and hard coding in the row or column number. Problem is, adding a line, messes up all the rows and columns. I need it to look up Freds Gross for 1/5/08, not necessarily in the same location as the dates change. A B C D 1/5/08 1/19/08 Fred Regular 500.00 625.00 Fred Overtime 100.00 125.00 Fred Gross 600.00 750.00 Fred FICA -30.00 -45.00 Tom Regular 300.00 350.00 Tom Overtime 75.00 80.00 Tom Gross 375.00 430.00 Tom FICA -23.00 -27.00 -- Lainey "T. Valko" wrote: Typo: =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Should be: =SUMPRODUCT(--(A1:A10="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way (possibly): =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Need a better description of your layout. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... I have employees in column A, payroll info in column B. All payroll fields are same for each employee (Fred and Tom both have gross wages) Rows are the pay periods done by date. I need to locate Freds Gross Wage on a given date. I cant seem to get the right functions/formulas nested together. Any help greatly appreciated. -- Lainey |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding a match within a match
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Lainey" wrote in message ... THANK YOU THANK YOU THANK YOU!!!!! I got it, cleaned up my match problem and it worked!:) Thanks so very much -- Lainey "T. Valko" wrote: If you get a #N/A error it's coming from the MATCH function which means it can't find a matching date. Make sure the lookup_date and the table dates are all TRUE excel dates. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... Thank you, I typed this in but I got a #N/A, will look again after a break. Maybe I have another error somewhere -- Lainey "T. Valko" wrote: See this screencap: http://img508.imageshack.us/img508/7980/lookup22ju3.jpg -- Biff Microsoft Excel MVP "Lainey" wrote in message ... The "Register" worksheet looks like what is below but with about 10 types of wages for each employee, approx 30 employees. I need to be able to pull to a summary pay period sheet for 1/5/08 for certain lines. I was doing it with a H or V Lookup and hard coding in the row or column number. Problem is, adding a line, messes up all the rows and columns. I need it to look up Freds Gross for 1/5/08, not necessarily in the same location as the dates change. A B C D 1/5/08 1/19/08 Fred Regular 500.00 625.00 Fred Overtime 100.00 125.00 Fred Gross 600.00 750.00 Fred FICA -30.00 -45.00 Tom Regular 300.00 350.00 Tom Overtime 75.00 80.00 Tom Gross 375.00 430.00 Tom FICA -23.00 -27.00 -- Lainey "T. Valko" wrote: Typo: =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Should be: =SUMPRODUCT(--(A1:A10="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way (possibly): =SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10) Need a better description of your layout. -- Biff Microsoft Excel MVP "Lainey" wrote in message ... I have employees in column A, payroll info in column B. All payroll fields are same for each employee (Fred and Tom both have gross wages) Rows are the pay periods done by date. I need to locate Freds Gross Wage on a given date. I cant seem to get the right functions/formulas nested together. Any help greatly appreciated. -- Lainey |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| finding all cells that match a certain value | Excel Worksheet Functions | |||
| Finding a match and pasting next to it | Excel Discussion (Misc queries) | |||
| Finding Closest Match | Excel Worksheet Functions | |||
| Finding a match in several columns | Excel Worksheet Functions | |||
| Finding a match | Excel Discussion (Misc queries) | |||
Linear Mode
