Index/Match/(or other)? Multiple Column/Row Values

My apologies - I really have no idea how to briefly describe my issue in the Thread Title, so I hope my title was sufficient for my issue.

I have attached a sample workbook (very shortened version) to help define my problem. On the attached workbook, I have the following two tabs:
1) Timesheet
2) Pay - Biweekly

Pooger Sample.xlsx

I need to take the total hours worked from the "total of multiple columns" for 1 person from Tab 1 (timesheet - row 7 (ee ID) and the total of all columns with that EE ID) and put those total hours on the right row and column for the right person in Tab 2 (pay-biweekly - column A (ee ID) and column F (hours)).

My problem is that the "person" appears in multiple columns on Tab 1, but appears on a single row in Tab 2.

I cannot figure out a formula that will work to perform this function, particularly if I wish to "delete" or "move" columns in Tab 1 (which I need to do).

The following formula works, but will not allow me to move or delete the columns in Tab 1 and requires me to point to a specific column for each person (see middle of formula below):

=SUMIF('Timesheet 04-27-13'!$D$7:$EP$7,'Timesheet 04-27-13!$D$7,'Timesheet 04-27-13!$D$298:$EP$298)

I've considered putting the EE ID in Column A of Tab 1, BUT this is a very shortened version of all the Job #'s and G/L #'s that truly exist..compounded with the multiple Labor Classification #'s - but this doesn't seem to be usuable solution.

I've tried to "color code" my sample workbook to make it easier to understand.

Does anyone have any ideas of a formula I could use to do this and/or an alternate way to set up Tab 1 to accomplish this? I am very frustrated.

Thanks so much!!

Hi

So based on your example file, what number from timesheet should go where on Pay, any what is the criteria for getting that number? I'm assuming that you would want a combination of the EE ID and the Dept, and you want to pull the total from row 201 on Timesheet, but where are you putting this number on Pay?

rylo

Hi Rylo,

That's sorta the tricky part. So if you look at EE ID 65 on the timesheet tab, you'll notice that this employee shows up in Columns H, I, J, K, and L. I need the total hours of all of those columns (appearing on row 210 in timesheet) for this employee to show up on Row 8 (which is the row for this employee) and Column F (hours) of the Pay tab. The "hours' currently showing in Column F are there just to make sure my other calculations were working. Sorry I forgot to remove them before I sent the file.

Some employees will only show up in 1 column on the timesheet tab, where others may show up in multiple columns in this tab. Each employee only appears once on the Pay tab.

Does this make sense?

Hi

Firstly, you will have to make the IDs in Timesheet row 7 the same as the IDs in Pay. So for your example, timesheet H7 - L7 will have to contain the value '065.

Then
Pay!F8: =SUMPRODUCT(--('Timesheet 4-27-13'!$A$7:$L$7='Pay - BiWeekly'!$A8),('Timesheet 4-27-13'!$A$210:$L$210))

rylo

OMG Rylo - you are a genius!!!!!!!!!! Thank you so much!!!

I had been beating my head against the wall on this one for hours/days!!

There are not enough words to express my gratitude!!!

Pooger