Need a whole row of Data placed into separate sheet based on one cell's criteria in Row.

Hey All,

I have a table of data the will continuously grow over time. We will call this Table1. Table1 has 16 data categories (columns) and the rows will continue to have additional data added to them on a near daily basis. This table lists all of the sales that have come in for that day with one of the categories (Column I) being that of the employee that completed the sale. What I need is a formula that will automatically pull the entire row of data for that particular sale and every other sale within Table1 based on the employee that sold it into a separate sheet designated for that employee. For example:

If I have a total of 50 sales, 20 of which were completed by employee John Smith, how would I throw all 20 of those sales into a separate sheet called "John Smith" within the same workbook; and likewise, allow this process to automatically continue to pull this data as additional sales are added into Table1?

I would prefer NOT to use VBA if at all possible, however if there is no other way, than an explanation would be wonderful!

Thank you all in advance for any help you can provide.

Please share a desensitized sample file so we can see what you are working with, as per the yellow banner above.

maniaCB, Should be attached. Thanks for the help!

Anyone able to help?

Try this formula:

=FILTER(Table1,Table1[Sales Rep]=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"None")

This assumes your SHEET NAME is the same as the Sales Rep name.

Or, if you want to put the name in a cell, like B2 for for example, you could use this:

=FILTER(Table1,Table1[Sales Rep]=B2,"None")

Thank you for your help kvsrinivasamurthy, but it looks like two separate sales from two different sales reps also made it in the sheet for John Smith, so I'm not sure if this fully works.

It works. Post the workbook that didn't work and we can tell you what's not right

Should be attached

What's not working? You don't have any data in the table except for the sales rep and it's showing the proper # of records. As you put in data, it shows up.

Okay, after I re-opened the Doc it worked, I swear I was getting a #VALUE error when I initially put it in tho. Thank you so much for all your help Greg! You are the best!