Let's try this again

[tommy20]

I posted this earlier, but don't think I did a very good job of
describing what I need.

On worksheet 1, I have a table with five columns; Item, Agent 1, Agent
2, Agent 3, Agent 4. There are 20 rows of data. These values are
set.

Item Agent 1 Agent 2 Agent 3 Agent 4
1 11 9 7 5
2 12 10 8 6
3 12 10 8 6
4 13 11 9 7
5 13 11 9 7
etc...

On worksheet 2, individual cells will contain values corresponding to
the Item column. The cells are not contiguous, and the values change.
10 12 14 2 11
11 5 3 5 15
14 17 10 13 19

Finally, on worksheet 3 (the actual report), I have columns that will
be labelled Agent 1, Agent 2, etc (each Agent can appear multiple
times), and rows labelled as Categories. Under each Agent, I need the
values from worksheet 1 to appear, based on the Items entered in
worksheet 2.

Categories Agent 1 Agent 2 Agent 3 Agent 4
Cat. 1
Cat. 2
Cat. 3

I know this is confusing. Any help is greatly appreciated!

-Tommy

 

[tommy20]

Obviously the columns do not line up in my example. I hope you get the
gist of it...

 

[Max]

(a) Assuming you have in Sheet2, in A23

2..1..4..5
4..2..B..3
3..B..5..1

where B = blank cell
(as you mentioned cells may not be contiguous)

(I'm assuming the above data-set instead of taking your sample for Sheet2)

(b) Assume also that the figures in col A correspond to Agent1,
those in col B to Agent2, and so on, and that
the numeric figures refer to the "row" numbers in Item column in Sheet1
(as you stated)

(c) Assume your sample table given in Sheet1 is in A1:E6

(d) In Sheet3
-----------

Taking your layout in Sheet3 as given,
i.e. col B = Agent1, col C = Agent2, and so on

Put in B2 (i.e. the cell just below the label "Agent1")
:
=IF(ISNA(OFFSET(Sheet1!$A$1,MATCH(Sheet2!A1,Sheet1!$A:$A,0)-1,COLUMN()-1,1,1
)),"------",OFFSET(Sheet1!$A$1,MATCH(Sheet2!A1,Sheet1!$A:$A,0)-1,COLUMN()-1,
1,1))

Copy B2 across B2:E2 (ie to the last col on the right)
Copy down B2:E4 (ie to the last row)

B2:E4 will return the values under each Agent's col,
their values from Sheet1 based on the entries in Sheet2.

Any non matching entries in Sheet2
(when matched against the figures in the Item column in Sheet1)
- for example Blank cells, or figures other than 1-5 in
the sample given above in part (a)

will result in corresponding "-----" indications in B2:E4

Based on the above set-up,
below is what you should see in Sheet3, in A1:E4

Categories..Agent1..Agent2..Agent3..Agent4
Cat.1................12..........9..........9...........7
Cat.2................13........10.......------........6
Cat.3................12......------........9...........5

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------

 

[Max]

Correction for a typo, sorry... the line

(a) Assuming you have in Sheet2, in A23

should read

(a) Assuming you have in Sheet2, in A13

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------

 

[tommy20]

OK! I'll give it a try. Thanks

 

[Max]

Good luck!

Pl feedback so that
we understand what has worked
(and what doesn't)

 

[Max]

so, tommy20,
hope it went alright for you?

rgds,
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------