Pulling Statistics Based on Name

Hi,

Was hoping for a solution to this problem. I have two spreadsheets one that arranges salespeople by role and there associated statistics and the other that arranges them by store. I basically want the statistics from the role based spreadsheet to populate into the stor based spreadsheet based on the salesman name.

Thanks in anticipation.

Post a sample file that we can look at and work on - you'll get far more responses that way.

Pete

Hi Pete,

Please see attached,it probably isnt that complex a solution I just cant figure it myself.

Cheers

Well, it's quite involved ...

I've inserted a new column C and a new column E in your "store" sheet, and put this formula in E3:

=MATCH(D3,'Data By Role'!$5:$5,0)

which finds which column the Role is located on in the "Role" sheet, and this formula in C3:

=MATCH(B3,INDIRECT("'Data By Role'!"&CHAR(E3+64)&":"&CHAR(E3+64)),0)

which finds the row where the name is located in the appropriate column of the "Role" sheet. Both of these columns help to reduce the size of the formula needed to retrieve the actual data, which is located in F3:

=INDEX('Data By Role'!$A:$AJ,$C3,$E3+COLUMNS($A$1:A$1))

and this can be copied across and down as necessary, as can be seen in the attachment.

Hope this helps.

Pete

Originally Posted by pete_uk

well, it's quite involved ...

I've inserted a new column c and a new column e in your "store" sheet, and put this formula in e3:

=match(d3,'data by role'!$5:$5,0)

which finds which column the role is located on in the "role" sheet, and this formula in c3:

=match(b3,indirect("'data by role'!"&char(e3+64)&":"&char(e3+64)),0)

which finds the row where the name is located in the appropriate column of the "role" sheet. Both of these columns help to reduce the size of the formula needed to retrieve the actual data, which is located in f3:

=index('data by role'!$a:$aj,$c3,$e3+columns($a$1:a$1))

and this can be copied across and down as necessary, as can be seen in the attachment.

Hope this helps.

Pete

thanks heaps!!!

Hi Pete,

I was wondering if you could update the formula for the attached sheet. It's the same problem but I have recently added some columns so the formula you provided is producing an error.

Greatly Appreciated.

Hi Haseeb,

I can see that the formula works, but am having trouble tranferring it to my actual spreadsheet. Any tips?

More specifically the error that I get is N/A "This error indicates that a value is not available to a function of formula". The first line works but the second does not. Any advice?

Hi Haseeb,

I solved the problem by reformatting the data to a flat format and then used VLOOKUP COLUMNS formula to pull the data.

Thank You for your help, I will look to use this forum in the future.