Vlookup a value in a different row coumn

[RyanGauci]

I have a set of data in different rows and columns, however 1 lead name (in 1 cell only).

How do I sort this information - as the lead name is not in each row!

I'm trying to go down the route of copy, pasting in a 2nd sheet and sorting the list alphabetically (column 1), then use vlookups to populate the rest of the table. WHich I've succeeded for the 1st 3 columns (straight forward vlookups) how do I populate the rest of sheet 2?

Thanks,

[BB1972]

Ryan,

Welcome to the forum. What exactly do you want on Sheet2? You have 5 different "week ending" entries per employee, with associated basic rate & overtime rate for each entry. It might be better to do a mock-up (don't worry about formulas) of the information, presenting it in the way you'd like to see it, and we can work on that.

[RyanGauci]

Hi Brandan,

I want the totals to show next to the name and get rid of all the inbetween data!

Thanks


R

[BB1972]

Ryan,

Please see attached.

If you simply want the totals, and the data on the Report tab will always be in the same layout, you can simply tell the cells in Sheet2 to look at the relevant cell addresses in Report, as I have done. You might want to make the Report tab "clever", and have it sum the totals for the different weeks for you, as I have done in I14:J20. Yes, you can also use Vlookups, or an index/match combination, in which case you'll need to make sure you have the employee name entered in the same ROW as the totals you're looking for.

I hope this helps - if it doesn't, please come back with a more detailed description of your requirements.

[RyanGauci]

Brendan,

The direct reference is the easy option, can't believe I oversaw that!!

I take it that I have to do a one off manual exercise for each cell, as I don't seem to be able to drag the formula down - I have 200 employees - is there an easier way?

R

[BB1972]

Yeah, sometimes we can over complicate things, and miss the easy option.

Well - again, if the data will always be the same shape - you could do this:

Please Login or Register  to view this content.

in A12, which says - well, I think you know what it says. Drag it down, and you now have the employee name/number/whatever in the same row as his/her totals. If you then use an index/match formula in Sheet2, it should be significantly more straightforward. Using

Please Login or Register  to view this content.

in cell H3 of Sheet2 will pull the total overtime hours for Employee 1 into cell H3.

Can you manage the rest?

[RyanGauci]

You're a star!!

Cheers.

R

[BB1972]

Glad to help, Ryan. And thanks for the rep