Its more likely that I'm not getting it...
This is a link to a tester spread
sheet.
I have three sheets
MaterialsValidationTable! (Hidden)
RecipientTracking! For entering information for a specific order
InStock!For maintaining a single list of all items and their current amount in stock.
Currently I am using data validation to create linked dropdown lists on RecipientTracking! (Pulled from MaterialsValidationTable!). This includes a media type and the title of the item being sent. I am then using vlookup and a couple of IF statements to pull the Last known inventory stock taken from InStock! and display a value for Remaining Stock on RecipientTracking.
I've worked it out so that no matter how many entries there are for a single item, the value in Remaining Stock will be associated with the Media Type and Title from the drop down lists on RecipientTracking! & the corresponding inventory Item on InStock!.
I was trying to match those double values (MediaType+Title) in both worksheets in order to display the Remaining Stock value on the InStock! worksheet with its corresponding inventory item.
I have multiple Media Types for the same Titles, so your suggestion of an ID key makes sense. And it makes a lot of sense to have those IDkey's set and hidden.
The problem I have with your proposal, is I don't know how to keep my dropdown lists and associate them with an IDkey so that I can then compare the two IDkeys to pull the information I want from one sheet to the next.
Bear in mind that there are likely to be multiple entries for the same MediaType and Title, so this will likely require something that looks at the whole worksheet.
I really appriciate all the help I've gotten from this site so far on this. I wouldn't even be as far as I am without it. So, honestly, if no one has the time/inclination/knowledge to make this work... I will not be put out. My boss is already thrilled, I just wish I could have made it do what I envisioned when I started.