Inventory Audit

Hi,

I'm trying to create a spreadsheet to audit items that need to be re-ordered on a weekly basis.

I have a number of columns that relate to:

• 1st Monday of the week
• Optimum Stock Level
• Stock on Hand

I have about 30 different items that need to be counted and entered in. However, I'm trying to find a formula that will allow the current stock on hand to be deducted from the optimum stock level for that particular week. Sounds easy so far.

However, I need to keep track of the re-order qty so the forumla needs to look at the current date and calculate which weekly column it needs to updated. All I've got so far is that when I enter the current stock on hand it updates ALL cells - which isn't much use. I've tried to mess around with the DATEDIF() function, but didn't get anywhere.

I've attached an example.......

I'm not sure whether this is a 'simple' Excel formula or whether a macro is needed, but any assistance will be greatly appreciated.

MAny thanks,
Simon

Hi,

I'm not too worried about the formula to calculate the re-order level and the future qty are based on me not being able to get the right formula to calculate the dates

My problem is to get the right Column, based on date, completed.

I tried that formula, but it changes ALL columns prior to todays date......

The logic needs to be:

If Todays date is equal to column date or less than the next column date then make the calculation
If Todays date is greater than column date, then move to next column

I think thats how it needs to work.....

Thanks again.....

I think it's going to be something like this:

=IF(AND(TODAY()>=E2,(TODAY()<F2)),($A3-$B3))-D3

Anyone got any bright ideas???