Excel - multi sheet operation

[bowz75]

I have two sheets (sheet 1 and 2). Sheet 1 has a list of names. Each user has multiple rows with a single value on each row. Sheet 2 has the users listed on one line each and the values listed in row 1 accross the top. I want to place an "x" in the correct column (column B, C, and/or D) of the value on sheet 2 if it is found in column B on sheet 1.

Sheet 1 example (2 columns 6 rows)
A B
1 User1 x
2 User1 y
3 User1 z
4 User2 y
5 User2 x
6 User3 x

Sheet 2 example (4 columns 4 rows)
A B C D
1 x y z
2 User1
3 User2
4 User3

 

[RivetHed]

I'd try something like:

=IF(SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$2:$B$1000=B$2))>0,"x","")

Which should hopefully enter an x if it finds a row with an entry matching the row header and the column header.

 

[SkipVought]

Hi,
[tt]
B2: =IF(ISNA(MATCH(B$1,OFFSET(Sheet1!$A$1,MATCH($A2,User,0),1,COUNTIF(User,$A2),1),0)),"",MATCH(B$1,OFFSET($H$1,MATCH($A2,User,0),1,COUNTIF(User,$A2),1),0))
[/tt]
I used Named Ranges to simplify.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[bowz75]

I am just not getting it, would it be possible to email a sample to someone that could put in the function?

 

[SkipVought]

Explain what you did and what result you are seeing.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[bowz75]

I selected cell B2 on sheet 2 and typed exactly as you specified above into the formula bar.

=IF(ISNA(MATCH(B$1,OFFSET(Sheet1!$A$1,MATCH($A2,User,0),1,COUNTIF(User,$A2),1),0)),"",MATCH(B$1,OFFSET($H$1,MATCH($A2,User,0),1,COUNTIF(User,$A2),1),0))

Excel error: The formula you typed contains an error.

I tried substituting User for the name in cell A2, but that didn't get it either.

 

[bowz75]

The values in column A on both sheets is identical - Last, First

 

[SkipVought]

I stated, "I used Named Ranges to simplify. "

I'd suggest you do that. The range on sheet1 for the users is named User. The heading is in Sheet1!A1



Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]