I am trying to find a way to calculate remaining balance within a specific
date code.
This is an inventory control spread sheet. The left side is for receivables
with date code and the right side is for reduction to inventory for
production base on date codes (not working!).
ex:
REC TOT EX DATE PROD DATE REL QTY EXP DATE 10
10/1/2009 11/2/2007 15
2/14/2008 7056 9/1/2010 2/13/2008 48 4/1/2010
2/14/2008 7056 9/1/2010 2/18/2008 2222 4/1/2010
2/14/2008 7056 9/1/2010 2/18/2008 2148 9/1/2010
2/14/2008 3696 9/1/2008 2/20/2008 5404 9/1/2010

In this ex. I need to be able to deduct 2148 and still reflect that I have
X amount left in that code date. And once all of that code date is used up,
I need a flag saying use next code date or 0 remainin or something like that.
Is there a way to do this?

I am sure there are ways to do this, but I don't understand your terms. What
is a date code? Is the Date code The EX DATE on the left and the EXP DATE on
the right? If I am understanding correctly, you need to to track the total
received of Date Code 9/1/2010 (received 7056) so that then 2148 released
against it yields an ending available balance of 7056-2148 or 4908, right?
Then next to the releases I'd add a column called Available or something like
that. In this I'd have a formula that would add all that Date Code's
Releases together and then subtract that # from the received qty and display
the available balance. Then we'd have to build a macro to alert you when
that # goes below zero. I can do that if you like, but want to make sure I
am on the right track first. Please advise.

"Lynn" wrote:

I am trying to find a way to calculate remaining balance within a specific
date code.
This is an inventory control spread sheet. The left side is for receivables
with date code and the right side is for reduction to inventory for
production base on date codes (not working!).
ex:
REC TOT EX DATE PROD DATE REL QTY EXP DATE 10
10/1/2009 11/2/2007 15
2/14/2008 7056 9/1/2010 2/13/2008 48 4/1/2010
2/14/2008 7056 9/1/2010 2/18/2008 2222 4/1/2010
2/14/2008 7056 9/1/2010 2/18/2008 2148 9/1/2010
2/14/2008 3696 9/1/2008 2/20/2008 5404 9/1/2010

In this ex. I need to be able to deduct 2148 and still reflect that I have
X amount left in that code date. And once all of that code date is used up,
I need a flag saying use next code date or 0 remainin or something like that.
Is there a way to do this?

Mike H.
Thank you for your response. I do believe that we are on the same page as.
I have saved a copy of my spread sheet that I use for inventory if you have a
place that I could send it to you.
The "code dates" are the expiration dates of the material received. In
attempting to write the request for help I forgot that not everyone works
with food products and would understand the various terms. I apologize for
that.
Yes, I need to bundle the expiration dates on the left side of the sheet and
then be able to deduct from the total of that date on the right side so that
I am not trying to use more than I have of a specific date or leave product
to expire because I missed it. Currently this is done manually and I really
need to find a way to be able to track it on the spread sheet.
I do appreciate your help.


"Mike H." wrote:

I am sure there are ways to do this, but I don't understand your terms. What
is a date code? Is the Date code The EX DATE on the left and the EXP DATE on
the right? If I am understanding correctly, you need to to track the total
received of Date Code 9/1/2010 (received 7056) so that then 2148 released
against it yields an ending available balance of 7056-2148 or 4908, right?
Then next to the releases I'd add a column called Available or something like
that. In this I'd have a formula that would add all that Date Code's
Releases together and then subtract that # from the received qty and display
the available balance. Then we'd have to build a macro to alert you when
that # goes below zero. I can do that if you like, but want to make sure I
am on the right track first. Please advise.

"Lynn" wrote:

I am trying to find a way to calculate remaining balance within a specific
date code.
This is an inventory control spread sheet. The left side is for receivables
with date code and the right side is for reduction to inventory for
production base on date codes (not working!).
ex:
REC TOT EX DATE PROD DATE REL QTY EXP DATE 10
10/1/2009 11/2/2007 15
2/14/2008 7056 9/1/2010 2/13/2008 48 4/1/2010
2/14/2008 7056 9/1/2010 2/18/2008 2222 4/1/2010
2/14/2008 7056 9/1/2010 2/18/2008 2148 9/1/2010
2/14/2008 3696 9/1/2008 2/20/2008 5404 9/1/2010

In this ex. I need to be able to deduct 2148 and still reflect that I have
X amount left in that code date. And once all of that code date is used up,
I need a flag saying use next code date or 0 remainin or something like that.
Is there a way to do this?

You could send me something at

"Lynn" wrote:

Mike H.
Thank you for your response. I do believe that we are on the same page as.
I have saved a copy of my spread sheet that I use for inventory if you have a
place that I could send it to you.
The "code dates" are the expiration dates of the material received. In
attempting to write the request for help I forgot that not everyone works
with food products and would understand the various terms. I apologize for
that.
Yes, I need to bundle the expiration dates on the left side of the sheet and
then be able to deduct from the total of that date on the right side so that
I am not trying to use more than I have of a specific date or leave product
to expire because I missed it. Currently this is done manually and I really
need to find a way to be able to track it on the spread sheet.
I do appreciate your help.


"Mike H." wrote:

I am sure there are ways to do this, but I don't understand your terms. What
is a date code? Is the Date code The EX DATE on the left and the EXP DATE on
the right? If I am understanding correctly, you need to to track the total
received of Date Code 9/1/2010 (received 7056) so that then 2148 released
against it yields an ending available balance of 7056-2148 or 4908, right?
Then next to the releases I'd add a column called Available or something like
that. In this I'd have a formula that would add all that Date Code's
Releases together and then subtract that # from the received qty and display
the available balance. Then we'd have to build a macro to alert you when
that # goes below zero. I can do that if you like, but want to make sure I
am on the right track first. Please advise.

"Lynn" wrote:

I am trying to find a way to calculate remaining balance within a specific
date code.
This is an inventory control spread sheet. The left side is for receivables
with date code and the right side is for reduction to inventory for
production base on date codes (not working!).
ex:
REC TOT EX DATE PROD DATE REL QTY EXP DATE 10
10/1/2009 11/2/2007 15
2/14/2008 7056 9/1/2010 2/13/2008 48 4/1/2010
2/14/2008 7056 9/1/2010 2/18/2008 2222 4/1/2010
2/14/2008 7056 9/1/2010 2/18/2008 2148 9/1/2010
2/14/2008 3696 9/1/2008 2/20/2008 5404 9/1/2010

In this ex. I need to be able to deduct 2148 and still reflect that I have
X amount left in that code date. And once all of that code date is used up,
I need a flag saying use next code date or 0 remainin or something like that.
Is there a way to do this?