Data changes with different number

Hi all,

Is this possible in excel,
i have a w/book with 2 sheets.if a saleperson on figures sheet number changes then his data on totals sheet changes to suit. example if salesperson john(V2) on figures sheet changes his number in W2 from 3 to 4 then on totals sheet should now display data from column P2:P11 and not his current data (k2:k11) .

I hope this is clear enough for someone to understand.

Try:

=INDEX(Figures!$A$1:$T$11,ROWS($A$1:$A2),MATCH(Figures!W$1&Figures!W$2,Figures!$A$1:$T$1,0))

copied down and across.

You just need to adjust the W$2 for each person's table accordingly. (e.g. for OWEN you use W$3)

Put this formula in B2 and copy down/across to F11:
=INDEX(Figures!$A$2:$T$11, ROW(A1), MATCH(B$1&Figures!W$2,Figures!$A$1:$T$1,0))

In B14:
=INDEX(Figures!$A$2:$T$11, ROW(A1), MATCH(B$1&Figures!W$3,Figures!$A$1:$T$1,0))

In B26:
=INDEX(Figures!$A$2:$T$11, ROW(A1), MATCH(B$1&Figures!W$4,Figures!$A$1:$T$1,0))

In I2:
=INDEX(Figures!$A$2:$T$11, ROW(A1), MATCH(B$1&Figures!W$5,Figures!$A$1:$T$1,0))

In I14:
=INDEX(Figures!$A$2:$T$11, ROW(A1), MATCH(B$1&Figures!W$5,Figures!$A$1:$T$1,0))

In I26:
=INDEX(Figures!$A$2:$T$11, ROW(A1), MATCH(B$1&Figures!W$6,Figures!$A$1:$T$1,0))

This is not a nested formula. It is the INDEX() function and it needs to know what it's arguments are before it evaluates... so it needs to evaluate each argument to find out what the row and column arguments are before it can resolve the INDEX() itself.