Excel help please

Cornwall_Girl

I currently have a spreadsheet that has a master sheet holding all data, the sheet is where work will be assigned to individuals. I now want to create a tab per worker, which will (if possible) automatically populate the individual's tab with the full row, when their name is selected against a particular job.

Is this possible, if so could somebody please tell me how to do it?

Thanks in advance.

stevemcol

Are you reasonably good with excel? If so, can I suggest you have a look at the vlookup function. It sounds like it will do what you're after. Is that enough to go on or do you need the full solution?

gonzo127

probably the easiest way is to make a macro which when ran, will filter by name and copy all the information to the named tab, this will then not slow down the spreadsheet by having multiple formulas in all the tabs for each person, and could be run as and when you added new jobs

Cornwall_Girl

Thanks for your responses. I thought I was alright on spreadsheets until it came to this!

I just made an attempt with a vlookup, which did not go to plan at all, I didn't get the data I needed anywhere. The macro screen looks very complicated.

gonzo127

pivot tables on each tab? - limited by the name

stevemcol

keep trying with vlookup. start on a single sheet to pratise. Generate a bit of dummy data with a column that includes some names. Get a vlookup expression to find the name then lookup the data alongside. It might take half an hour or so to get the jist of it but it's a powerful function when you suss it. After getting it to lookup data on the single sheet you need to work out how to address other worksheets, which isn't too difficult.
Macros would be difficult to learn from scratch just for this purpose. I don't use pitvot table personally so can't advise on that.

stevemcol

example
Put some random data in cells b2 to b4 and c2 to c4 with names in column B. Then in another cell:

=VLOOKUP("john smith",B2:C4,2,FALSE)

you can see how it looks out John Smith then returns the data next to his name.

Cornwall_Girl

this doesn't seem to work with my data. The names are in the 13th anf 14th column of the sheet. I notice that VLOOKUP is for the left and column of a table. Is this why it's not working?

Thanks for the helpful responses so far.

rmg1

Vlookup expects to find the item you're looking for in the first column and then moves to the right however many columns you specify to retrieve the data.
You can use Index/Match in a similar fashion to move both ways.
An example formula would be:-
=INDEX(A:A,Match(Z2,D:D,0))
or
=INDEX(F:F,match(Z2,D:D,0))

What that means in English is:-
Get the item in Z2 and look for it in column D. On the same row, return the value in column A.
So, if the value in Z2 was found in D55, you would get the value in A55 returned.
or
Get the item in Z2 and look for it in column D. On the same row, return the value in column F.
So, if the value in Z2 was found in D55, you would get the value in F55 returned.

Hope all that made sense.

stevemcol

agree, or reproduce the name column at the left hand side of the sheet, hidden if necessary.

stevemcol

rmg1 wrote: »

Vlookup expects to find the item you're looking for in the first column and then moves to the right however many columns you specify to retrieve the data.
You can use Index/Match in a similar fashion to move both ways.
An example formula would be:-
=INDEX(A:A,Match(Z2,D:D,0))
or
=INDEX(F:F,match(Z2,D:D,0))

What that means in English is:-
Get the item in Z2 and look for it in column D. On the same row, return the value in column A.
So, if the value in Z2 was found in D55, you would get the value in A55 returned.
or
Get the item in Z2 and look for it in column D. On the same row, return the value in column F.
So, if the value in Z2 was found in D55, you would get the value in F55 returned.

Hope all that made sense.

I've just realised why there are random smileys in your formulas!
Lol ....colon close-bracket