Hi,
I have a worksheet (B) form that tracks specific people horizontally and
specific categories vertically:

Mary Joe Jim Frank
Sales
Calls
Results

The results are already compiled on a separate worksheet (A) but in reverse
format (people vertically, categories horizontally) . Ive linked the two
worksheets but cant figure out how to make the formula automatically
count/link in the correct order when I c&p it.

If you can think of a better way to tackle this problem, Im open to
suggestions. The format of Worksheet B cant be changed and there are 200+
people and 16 categories €“ a nightmare to do by hand.

Thanks!

Here's one way
Assume the top left cell in the source table in sheet: B is A1
In any other sheet,
in any startcell, say in C3:
=OFFSET(B!$A$1,COLUMNS($A:A)-1,ROWS($1:1)-1)
Copy C3 across/fill down as far as required to populate the dynamic transpose
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"AtlantaResearch" wrote:
I have a worksheet (B) form that tracks specific people horizontally and
specific categories vertically:

Mary Joe Jim Frank
Sales
Calls
Results

The results are already compiled on a separate worksheet (A) but in reverse
format (people vertically, categories horizontally) . Ive linked the two
worksheets but cant figure out how to make the formula automatically
count/link in the correct order when I c&p it.

If you can think of a better way to tackle this problem, Im open to
suggestions. The format of Worksheet B cant be changed and there are 200+
people and 16 categories €“ a nightmare to do by hand.

Thanks!

Hi,

here is one way:

=INDEX(D,MATCH(B$1,RT,0),MATCH($A2,CT,0))

If on sheet B you name the data D, the row with the column titles CT and the
column with the row titles RT, then the above formula entered in B2 of sheet
A will return the desired results.


If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"AtlantaResearch" wrote:

Hi,
I have a worksheet (B) form that tracks specific people horizontally and
specific categories vertically:

Mary Joe Jim Frank
Sales
Calls
Results

The results are already compiled on a separate worksheet (A) but in reverse
format (people vertically, categories horizontally) . Ive linked the two
worksheets but cant figure out how to make the formula automatically
count/link in the correct order when I c&p it.

If you can think of a better way to tackle this problem, Im open to
suggestions. The format of Worksheet B cant be changed and there are 200+
people and 16 categories €“ a nightmare to do by hand.

Thanks!

Hi,

Using the same range names I mentioned in my previous example, you can use
the shorter

=SUMPRODUCT((CT=$A2)*(RT=B$1)*D)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"AtlantaResearch" wrote:

Hi,
I have a worksheet (B) form that tracks specific people horizontally and
specific categories vertically:

Mary Joe Jim Frank
Sales
Calls
Results

The results are already compiled on a separate worksheet (A) but in reverse
format (people vertically, categories horizontally) . Ive linked the two
worksheets but cant figure out how to make the formula automatically
count/link in the correct order when I c&p it.

If you can think of a better way to tackle this problem, Im open to
suggestions. The format of Worksheet B cant be changed and there are 200+
people and 16 categories €“ a nightmare to do by hand.

Thanks!