Hope someone can help to to use Macro to adjust some FIFO Manual Entries

[kateleung]

Hello everyone. I am a new member of this forum and I am from Hong Kong.
If my English is not so good, please forgive me.
I am now doing some manual entries and updates for the FIFO inventory system.

As you can see from the picture cell "A5",originally, I have physical stock count which is 30, but my last record update shows the sum of my accounts is "C5",which is (D5+E5+F5+G5=10+15+30+5=60)

In other words, Column B shows the difference, which means the company has sold out (C5-A5) 30 number of stock.

In order to update my accounts (diff=0) and match with the FIFO principle, I will manually subtract 5 in cell "G11" (90-120 days is the oldest stock) first and then 25 in cell "E11" (second oldest so that the difference of 30 will be eliminated. Also, sum of record will be equal to closing stock balance.

Are there any methods, formulas or Macros that **If I see there is a difference in column B, it can be automatically subtract the difference gradually from the oldest stock to the latest.

Thank you very much and you help would be really really appreciated.

I am using Excel 2010.

 

[elmer007]

Try in cell D11: =D5-MIN(D5,($B5-(SUM(E5:$G5)-SUM(E11:$G11))))
then copy it into the rest of D11:F12

In G11: =G5-MIN(G5,$B5)
then copy to G12

Does that work?

 

[kateleung]

Try in cell D11: =D5-MIN(D5,($B5-(SUM(E5:$G5)-SUM(E11:$G11))))
then copy it into the rest of D11:F12

In G11: =G5-MIN(G5,$B5)
then copy to G12

Does that work?

Wow...this is so amazing.! It works! Can you explain about a bit about the formula as it is a bit complicated to me��

Btw,the oringinals are all numbers consisting thousand of entries, do I need to copy the data to a new sheet in order to run the result? Is it possible to do it in the same sheet, like, I can press a button or a macro, then it will immediately eliminate all the difference.

Thanks elmer! It helps me much!

 

[elmer007]

I'm not sure I understand exactly how your spreadsheet is setup, but I was imagining two tables: 1) the table with the originals, and 2) the table with the results. In order to use the formula as it is, the results table needs to be the same size as the originals and the items need to be in the same order (basically identical tables). They don't, however, have to be on the same sheet. Your results table could be on another sheet, so in theory you could make a copy of the originals sheet, then put the formulas in columns D:G on the new sheet.

If you don't actually want to have a results table, you could do this and then copy/paste-values back onto the original table when you finish. Or, you could have a macro that fixes the numbers instead. It would work a little differently, though (it wouldn't include the formulas shown).