I have a spreadsheet with 18 columns and 8150 rows of data of all sales for the month. I want to extract data from 11 of those columns in to another spreadsheet based on the match in one column "sales person" there will be multiple matches (rows) and I need them all on the new spreadsheet to calculate sales commission each month for each salesperson. I am currently using the formula
=IFERROR(INDEX('Daily Sales'!$C$2:$R$8150,SMALL(IF('Daily Sales'!$P$2:$P$8150=$B$1,ROW(INDIRECT("1:"&ROWS('Daily Sales'!$C$2:$C$8150)))),ROW(A5)),1),"")
unfortunately I either get blank cells or the first row of the daily sales data which in this case is incorrect if I copy the formulas down. I am getting the correct information in each of the cells (cells match, but not from the matching row to the salesman # in B1)
Let me explain it a bit better. I'm trying to extract all of the information regarding a sale in the month made by one salesman. (Column P) my data starts in Column C and goes to Column R, I need the information for each sale made by this salesman (B1) on my new worksheet to fill the new columns of c, f, g, h, i, j, k, l, m, and r. So in each of the cells across I change the last number 1 to the appropriate column number 1 - 16. What the formula is not doing is matching the correct salesman # from column P on the daily sales sheet to B1 on the salesman commission sheet. What am I doing wrong?
=IFERROR(INDEX('Daily Sales'!$C$2:$R$8150,SMALL(IF('Daily Sales'!$P$2:$P$8150=$B$1,ROW(INDIRECT("1:"&ROWS('Daily Sales'!$C$2:$C$8150)))),ROW(A5)),1),"")
unfortunately I either get blank cells or the first row of the daily sales data which in this case is incorrect if I copy the formulas down. I am getting the correct information in each of the cells (cells match, but not from the matching row to the salesman # in B1)
Let me explain it a bit better. I'm trying to extract all of the information regarding a sale in the month made by one salesman. (Column P) my data starts in Column C and goes to Column R, I need the information for each sale made by this salesman (B1) on my new worksheet to fill the new columns of c, f, g, h, i, j, k, l, m, and r. So in each of the cells across I change the last number 1 to the appropriate column number 1 - 16. What the formula is not doing is matching the correct salesman # from column P on the daily sales sheet to B1 on the salesman commission sheet. What am I doing wrong?
