# Data changes with different number

1. ## 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.  Register To Reply

2. ## Re: Data changes with different number

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)  Register To Reply

3. ## Re: Data changes with different number

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))  Register To Reply

4. ## Re: Data changes with different number

Thanks enormously to you both for the great formula's.
but could you explain how this formula is evaluated,I always pressumed in a nested formula like this its evaluated from the right first.
but when i use evaluate formula it starts with the row(s) part first.

sorry i am just trying to understand.  Register To Reply

5. ## Re: Data changes with different number

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.  Register To Reply

6. ## Re: Data changes with different number

ok, i am learning,

Thanks for clearing that up for me.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1