Please Help... I've forgotten everything!

[jumuraa]

I have been trying to get this to work for weeks to no avail. I’ve tried several methods posted in the boards, but nothing has worked. This is probably because I don’t understand the method as well as I’d like to think.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Before anyone tells me that I should be doing this in a database, I tried that… and my office threatened to hang me by my toes… for some reason that idea scares them worse than… well I won’t get into that.
<o></o>
Here is the situation
<o></o>
I have an inventory workbook which currently has three spreadsheets.
<o></o>
MaterialsValidationTable:
· This is used to make sure that all inventory items naming convention is not randomly changed
<o></o>
Instock:
· Currently a static number based upon the last physical inventory taken.
<o></o>
RecipientTracking:
· Used to show what items we give out at events
<o></o>
Current Methodology:
· A staff member uses cascading drop down lists to choose the item(s) they are taking to an event.
· They enter the amount they are taking in the CheckOut column
· They enter the amount they bring back in the CheckIn column
<o></o>
What I CAN do
· Have excel automatically us VLookup to find the current inventory amount, subtract CheckOut and CheckIn and display the new total. The problem is that this is a static event, and is not included in future checkout/in events.
<o></o>
Here is my current formula:
<o></o>
=IF(E2="","",VLOOKUP(E2,MaterialsLookup,2,FALSE))-F2+G2
<o></o>
Please understand that if you give me a generic formula… I am unlikely to be able to adapt it to mine… I’ve been out of this stuff for too long.
<o></o>
Additionally:
· I would like to have a blank cell if nothing has been entered in the preceding cells.

This link is to a partial screen shot:
https://picasaweb.google.com/Jumura...authkey=Gv1sRgCPfFy-mqnfqs1QE&feat=directlink

 

[jumuraa]

In an attempt to get the data from RecipientTracking!H (where I have the dynamic math occuring) to display in InStock!D, I have un successfully tried to do another VLookUp

=IFERROR(IF(B2="","",VLOOKUP(B2,CheckInOutLookup,4,FALSE)),C2)

All this does is display the value from InStock!C in InStock!D (which is what I would like if there are no instances of the item found on RecipientTracking!

It does not grab the information from RecipientTracking!H Any ideas?

 

[jumuraa]

Okay, I've almost got this! It turns out that my CheckInOutLookup was spelled wrong, so it now works.

I do have a slight problem. I have some Titles which are the same across media platforms, i.e. "Hello World" is the title of a book, a CD, and a DVD.

The code I used in the above post can't tell the difference, and just pulls the first one it finds.

Any ideas?