Creating an excel-based Cash Safe Log

[Owen_Parkinson]

I need to create an excel-based log of any Cash coming in and out of a safe, but my excel knowledge hit a road-block when trying to implement one part of my idea.

I use this formula to add any cash coming IN, to the Safe Contents section:

=IF(AND(I5="IN",OR(K5="Takings",K5="Float")),K5:M5,"")

What would I need to add to remove an entry in the Safe Contents section, if something coming OUT has a matching Seal Number?

You can see there is a £20 Takings coming IN, and the same £20 Takings is being taken OUT. The Safe Contents section should then only list the £300 Float, as it hasn't been taken out yet.

Any help?

Thanks,

Owen

[#2]

@Owen_Parkinson 

To remove an entry from the Safe Contents section when something is going OUT with a matching Seal Number, you can use the following formula:

=IF(AND(I5="OUT",ISNUMBER(MATCH(N5, $Q$5:$Q$100, 0))), "", IF(AND(I5="IN",OR(K5="Takings",K5="Float")), K5:M5, ""))

In this formula, we assume that the Seal Numbers are listed in cells Q5 to Q100 (you can adjust this range as needed). The formula uses the MATCH function to check if the Seal Number (N5) exists in the range of Seal Numbers. If it does (i.e., there's a match), the formula returns an empty string "", effectively removing the entry from the Safe Contents section. If there is no match, the formula behaves as before and adds the cash coming IN to the Safe Contents section.

Make sure to adjust the ranges in the formula to fit your actual data range. Also, be careful with absolute and relative references, especially if you plan to copy the formula to other cells. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.