What formulas to use for two workbooks - one referencing the other dynamically but only part of it...??

[warhammer486]

I have two workbooks, pricing and inventory, I need to setup for a co-worker. Inventory Workbook is open to everyone on the team to reference inventory items. Pricing Workbook is visible only to the managers for setting up pricing, discounts, final-pricing, etc...for the existing inventory in the inventory workbook. They want to try to avoid typing all the inventory in twice. The managers will type 9 columns worth of information in for the inventory workbook for everyone to reference. The redundant columns that are in both workbooks are the first four columns, which is what they want to try to avoid retyping/copying & pasting from one to the other.

I thought that using a simple import range would be the solution. the problem is that the managers will change the position of the data when an item moves from on-order to in-stock status. When they move it, the pricing sheet obviously moves with it, but the columns with information pertinent only to the pricing sheet doesn't move with it, so everything is out of alignment.

Is there a way to "lock" the row on the pricing sheet? As a visual example below...if this was the manager pricing sheet and I wanted to =importrange all the fruit labels that are listed on the inventory page but keep the apple data row, peaches row, etc... together if someone sorts the data out of order on the other workbook, how can I keep that apple row data to stay with the apples? I wasn't sure if indexing/matching would be needed. I'm not sure.

 

[sijpie]

Can you extend your examples?
A "screenshot" of the inventory sheet and one of the pricing sheet.

Also I don't understand :

but the columns with information pertinent only to the pricing sheet doesn't move with it, so everything is out of alignment

So show what happens when a manager moves the information

 

[warhammer486]

Can you extend your examples?
A "screenshot" of the inventory sheet and one of the pricing sheet.

Also I don't understand :

So show what happens when a manager moves the information

Thank you for your consideration.

Unfortunately I cannot give you actual screenshot data due to restrictions of sharing real data. However, I believe I may be on the right track now, using an alignment index number and vlookup in combination with the importrange formula.

 

[sijpie]

OK. That's why i had screenshot in between quotes.
I would normally make a copy of the sheet, then run find/replace a few times to swap various letters. That makes the text pretty garbage.