Few easy question I hope!

[bigj]

Hello everyone!

I have learned a lot on this forums lately, however I am stuck o
something that seems easy but I guess I am just overlooking/blind.

What I am trying to do is :

I have 2 worksheets

1 - contains information on employee's and the department they work in

2- contains various data regarding activity throughtout the day.

What I want to happen is on sheet one for instance if I enter the nam
"Jason" is A1 I want excel to look on worksheet 2 and see wha
department "Jason" works in and input that into A2.

The names will always be in Column A and the output I want will alway
go into Column B, only other thing is that there is a empty ro
inbetween each entry.


Example :
A B
Jason Department <--I want this to refer to workshee
1 to input what department "Jason" works in
no data here
Sam Department


Hope I have made this clear enough

Thanks in advance

 

[Bob Phillips]

You need VLOOKUP, but it is not clear where on Sheet2 the department value
is held.

Normally, it would be something like

=VLOOKUP(A1,Sheet2!$A$1:$E$100,2,False)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

One way ..

Assume the names and depts are in cols A and B in Sheet1
(names are assumed unique)

In Sheet2
-------------
Put in A2

=IF(ISNA(MATCH(TRIM(A1),Sheet1!$A:$A,0)),"",VLOOKUP(TRIM(A1),Sheet1!$A:$B,2,
0))

A2 will return the dept for the name in A1 from Sheet1

 

[Max]

Here's an approach to play with ..
which could bring you quite close to what you're after

In each of the sheets: Week 1, Week 2, etc
use an empty col to the right of the data, say col X

Put in X1: =details!$F$1
(Cell F1 in sheet: details is where the user initials,
e.g.: MXB will be input)

Put in X2: =IF(TRIM(H2)="","",IF(TRIM(H2)=TRIM(X$1),ROW(),""))

Copy X2 down by a safe "max" number of rows
that data is expected in cols A to J, say down to X500?

In sheet: details
-------------------
User Initials will be input in cell F1, e.g.: MXB
Let's reserve cell G1 for input of the sheetname, e.g.: Week 1

The query results (cols A to J) for the user and sheet input/specified in F1
& G1 will be extracted and displayed in row5 down (as per your specs)

Put in A5:

=IF(ISERROR(MATCH(SMALL(INDIRECT("'"&TRIM($G$1)&"'!$X:$X"),ROW(A1)),INDIRECT
("'"&TRIM($G$1)&"'!$X:$X"),0)),"",OFFSET(INDIRECT("'"&TRIM($G$1)&"'!$A$1"),M
ATCH(SMALL(INDIRECT("'"&TRIM($G$1)&"'!$X:$X"),ROW(A1)),INDIRECT("'"&TRIM($G$
1)&"'!$X:$X"),0)-1,COLUMN(A1)-1))

(you'd need to restore the inevitable line wrap/breaks when you copy > paste
the formula above into the formula bar for A5)

Copy A5 across to J5, fill down by as many rows as data is expected for the
query (for the specified user and sheet), say down to J100? Or, just fill
down to cover the max scenario, i.e. down to J500 thereabouts.

The above set-up should return the query results,
albeit on a per sheet basis.
Do a copy > paste special > values of the results returned elsewhere
Then repeat steps to query for the next sheet (Week 2, etc)

If you have probs getting the above up, I could send you a sample file via
private email. Just post a "readable" address in reply here
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Max that worked great thank you!

One last question and I am pretty sure
I have finished this blasted spreadsheet

In the first photo I have highlighted the data I would like to show.

In H column I have initials that represent the person
responsible for the action.

Now on the second sheet "details" I would like to input a
persons initials and then it return all off the findings.
I would prefer it return the whole row.
So for a search for "MXB" it would return rows 69,95,102.

I would like this to search the whole workbook however.
As it is split up into 5 weeks in which a person can be listed anywhere.

I can live without this feature but I figured I would throw
it out there! Thanks!

 

[Max]

Then repeat steps to query for the next sheet (Week 2, etc)

On 2nd thought, replace the above line with
(reads much better <g>):

Then just change the input in G1 to query for the next sheet: Week 2
Repeat the copy > paste special > values of the results returned
And so on ..