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

 

[Andrew Poulsom]

Welcome to MrExcel.

Is it?

=IF(E2="","",VLOOKUP(E2,MaterialsLookup,2,FALSE))-SUMIF(E:E,E2,F:F)+SUMIF(E:E,E2,G:G)

 

[jumuraa]

Thanks a lot! That works for the dynamic accounting! Heck it almost works too well, in that it updates all previous entries with the new current inventory.

How would I modify this to move the SumIf portion to the InStock sheet, that way there is only a single entry of the new dynamic total for each item?

I know this won't work, but would it be something like:

=C2-SUMIF($InStock$E:$InStock$E,$InStock$E2,$InStock$F:$InStock$F)+SUMIF($InStock$E:$InStock$E,$InStock$E2,$InStock$G:$InStock$G)

 

[jumuraa]

I tried several variations of this as well on the InStock worksheet

=IF(RecipientTracking!E:E="","",VLOOKUP(RecipientTracking!E:E,MaterialsLookup,2,FALSE))-SUMIF(RecipientTracking!E:E,RecipientTracking!E:E,RecipientTracking!F:F)+SUMIF(RecipientTracking!E:E,RecipientTracking!E:E,RecipientTracking!G:G)

It acts like a a simple =RecipientTracking!H2 (The column where I currently have the dynamic inventory being tracked.)

Any ideas? I'd really love to only have a single instance for my numbers, it will make it less confusing for other staff.

 

[jumuraa]

I figured out how to use the IFERROR function correctly to get ride of my #value! issue. So the sheet at least looks better. This is already a giant leap forward for my office. I wish they would have just let me do it in a database though.

I still can't figure out how to rework the formula Mr. Poulsom gave me to work on the InStock sheet instead of the RecipientTracking sheet. Having a single instance for the current amount to easily compare with the original in stock amount would be great.

If no one has any ideas on how to do that though, they will have to live with it the way it is.

 

[jumuraa]

I take it that no one knows how to do this then... Oh well.

 

[buxtongt]

Can you be more specific about what you have working currently and what changes you want to make to it?

 

[jumuraa]

The code that Mr. Poulsom provided worked great at making sure that the total was correct.

This is on the RecipientTracking worksheet:

=IFERROR(IF(E3="","",VLOOKUP(E3,MaterialsLookup,2,FALSE))-SUMIF(E:E,E3,F:F)+SUMIF(E:E,E3,G:G),"")

What I am trying to do is get this same output, but on the InStock worksheet. I want it to correspond to the correct inventory item that is listed on the InStock worksheet.

Everything I've tried so far will only copy a specific cell, it will not adjust it to match the inventory item. I've been thinking I may need to use another Vlookup, but I don't know what it would look like.

I would show you the document, but my work servers are too restrictive.

 

[buxtongt]

So the number you need is showing on the RecipientTracking worksheet and you just want to pull it over to the InStock worksheet as well?

 

[jumuraa]

Yes, but I need it to line up with the corresponding item.

I've been able to upload the document here.

I think it will make more sense if you can see it.