Move row data to separate worksheet

[cyoungtx]

Hello and thanks for reading,

I have 2 worksheets where data on both is constantly updated. On worksheet called 'Red' in Column B I want to add numbers. Those same numbers will be located somewhere on a worksheet called 'Inventory' in Column D.

When I add the numbers to 'Red' in Column B, I needthe data from Columns E-J on the row of the corresponding number in 'Inventory' Column D be copied to Columns C-H of that number on worksheet 'Red'.

Lastly, if any values change on 'Inventory' Columns E-J, I need them to update the row of the corresponding number on 'Red' Columns C-H.

Help is appreciated!

 

[Marcol]

Create two dynamic named ranges on Sheet "Inventory"
1/. InventoryNumber
Refers To:=

=OFFSET(Inventory!$D$2,0,0,COUNTA(Inventory!$D:$D)-1,[COLOR=red]1[/COLOR])

This list must be continuous, no blanks.

2/. InventoryTable
Refers To:=

=OFFSET(Inventory!$D$2,0,0,COUNTA(Inventory!$D:$D)-1,[COLOR=red]7[/COLOR])

This can contain blanks in cells other than in Column D

3/. Use the named range "InventoryNumber" as data validation for Sheet "Red" Column B2 down as far as you need.

4/. In Sheet "Red" C2

=IF(B2="","",INDEX(InventoryTable,MATCH($B2,InventoryNumber,0),COLUMN(B$1)))

Drag/Fill Across to Column H, Then Down as required.

When you select from the dropdowns in Column B the data should appear in Columns C:H for the selected number.

Any number added to the "InventoryNumber" list will be reflected in the dropdowns.
If you change the data in the "InventoryTable", this will be reflected in Sheet "Red"

If you change an "InventoryNumber" then the "Red" table will return #N/A, but the validation list will contain the change, select this and all should be well again.

If you need changes to the "InventoryNumber" to automatically reflect in the "Red" sheet you will need vba.

Hope this helps.

 

[cyoungtx]

This was helpful, it works well, except for the last 5 lines in the range.

If any of the last 5 numbers in the range 'InventoryNumber' are added to the Red sheet Column B, the 'InventoryTable' values returns #N/A. Everything above those 5 work perfect. I've currently have a placeholder 'X' for the last 5 cells in the range to accomodate the working area.

Any idea why this may happen?