I have a daily sales spreadsheet that is added to daily. It is everything that is sold from 1/1 to 12/31 each year. It lists salespeople and items sold etc. I need to create a spreadsheet for each salesperson monthly to track their commissions for the month.
I have pulled that spreadsheet in to a second one with the columns I need by simply doing an = function and then delete the sales I do not need to calculate commissions on.
On my next spreadsheet (one for each salesperson) I am trying to pull their info to the new spreadsheet. The one I am pulling from has data from columns A - R. I only need the data from C - R with a few columns left out on the new spreadsheet, but I need all sales for that specific salesperson so I can't use Vlookup. I am trying this 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(A1)),1),"")
Unfortunately, I am getting the first row of data in the spreadsheet that does not belong to the salesperson's number in B1 and in the following rows just blanks when the formula is copied down. I am guessing that my problem is somewhere in the Indirect formula but I have no idea what I should be using.
My original table (Tab: Daily Sales) has headers in row 1 and currently data in rows from 2 - 8150 and columns C - R. The salesperson's number is in column P. My new table (tab: 201 - Dave Brown) has the salesman # in B1 and the header info in row 4 with data to be in rows 5 - 500 or however are necessary to meet the sales for the month for each salesperson and columns A - J. The data in each of the columns is correct in that it came from the correct column on the original spreadsheet, just the wrong line(row).
Can someone please assist me in correcting this formula or help me with a new one so it grabs the correct rows.
I have pulled that spreadsheet in to a second one with the columns I need by simply doing an = function and then delete the sales I do not need to calculate commissions on.
On my next spreadsheet (one for each salesperson) I am trying to pull their info to the new spreadsheet. The one I am pulling from has data from columns A - R. I only need the data from C - R with a few columns left out on the new spreadsheet, but I need all sales for that specific salesperson so I can't use Vlookup. I am trying this 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(A1)),1),"")
Unfortunately, I am getting the first row of data in the spreadsheet that does not belong to the salesperson's number in B1 and in the following rows just blanks when the formula is copied down. I am guessing that my problem is somewhere in the Indirect formula but I have no idea what I should be using.
My original table (Tab: Daily Sales) has headers in row 1 and currently data in rows from 2 - 8150 and columns C - R. The salesperson's number is in column P. My new table (tab: 201 - Dave Brown) has the salesman # in B1 and the header info in row 4 with data to be in rows 5 - 500 or however are necessary to meet the sales for the month for each salesperson and columns A - J. The data in each of the columns is correct in that it came from the correct column on the original spreadsheet, just the wrong line(row).
Can someone please assist me in correcting this formula or help me with a new one so it grabs the correct rows.
