Automatically copy an entire row to another sheet if a cell displays a specific value

[npaparoidamis]

I am trying to set up an automatic inventory. On sheet 1 I have my master inventory, Columns A-G, and I want to make it so depending on what location (column B) is listed, it will automatically copy the entire row to another sheet. The values of column B (location) are 1-10 and I want all rows that have a location value of "1" to be copied to sheet 2 (titled Location 1), all rows that have a location value of "2" to be copied to sheet 3 (titled Location 2), etc. Is this possible to make this automatic sheet? Also when a row is added to the master inventory (sheet 1) it needs to be copied automatically. Please let me know if you need any other info.

Thank you for your help!

[Pete_UK]

It would help if you attached a sample Excel workbook, so we can see exactly how your data is laid out.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[npaparoidamis]

I have uploaded an example of the document to my attachments. Sorry for the delay, as I had to block out certain information. Please let me know if you can view the attachment.

[Pete_UK]

I have used column I on the Master sheet as a helper column (coloured blue), with this formula in I2:

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

This should be copied down beyond your data to accommodate more data being added - I've copied to row 200 in the attached file, and the hyphens help to show where the formula is active.

In all the Location sheets I have used cell M1 to record the location number for each sheet (1 to 9), and then put this formula in A2:

=IFERROR(INDEX('Master Inventory'!A:A,MATCH($M$1&"_"&ROWS($1:1),'Master Inventory'!$I:$I,0)),"")

This can be copied across to column H, and then down as far as you like - I've copied to row 20 in all sheets, though it needs to be copied further than this in some sheets in order to see all the data.

As this is a formula solution, it will automatically pick up new data that is added to the Master sheet and show it in the appropriate Location sheet.

Hope this helps.

Pete