copy & pasting automatic formulas

[AtlantaResearch]

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) . I’ve linked the two
worksheets but can’t 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, I’m open to
suggestions. The format of Worksheet B can’t be changed and there are 200+
people and 16 categories – a nightmare to do by hand.

Thanks!

 

[Max]

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

 

[Shane Devenshire]

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

 

[Shane Devenshire]

Hi,

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

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