Inventory Tracking

[Jack6627]

Hello.

I have an inventory tracking problem that I am having trouble with. I have been trying to use the multi-row formula tool but keep running into circular references. I have attached a sample csv and a notepad which explains how each formula works. I think this will require an iterative macro since it is a row-by-row calculation but I don't know how to even get started with one because of the circular reference issue. I will be happy to clarify any further doubts. 🙂

Thanks!

[Jack6627]

Hi @Christina_H. Your macro is near-perfect!! It calculates all but one field correctly. The [Opening Balance] field is where I run into problems. Your formula within the multi-row formula tool is correct so I can't figure out why it is calculating the incorrect value.

When my RecordID is NOT EQUAL TO 1, it should take the closing balance value from the previous row but it doesn't do that right now.

[Christina_H]

@Jack6627 I'm glad it's almost there! I'll take another look tomorrow, it's probably to do with the order the fields are calculated in.

[Jack6627]

@Christina_H you have calculated the opening balance before the closing balance and since it is a row wise calculation it should give the correct value from row 2 onwards. Thanks anyway! 🙂

[Christina_H]

@Jack6627 yes it was bound to be something like that, it was almost impossible to keep track of what order they needed to be calculated in since they're all so interconnected!

[Jack6627]

I still haven't been able to figure out the issue with the Opening Balance formula. 😞

Any help would be much appreciated.

[Christina_H · Accepted solution]

@Jack6627 I've figured out where I went wrong.  I initially had it set up to calculate every row on every iteration, which was giving the right results.  Then I was running out of time and tried to improve the efficiency by limiting it to calculate only the current row, forgetting that it was actually correcting earlier rows as it went.  I obviously didn't check the results properly after that change.

Here's an updated version.  The problem really is the circular calculations between closing balance / order quantity / received quantity.  I still feel like it could be more efficient, but working and inefficient is better than not working at all!

[Jack6627]

Hi @Christina_H . Thanks a tonn!! This solution worked. I just had to tweak it a little bit and put the running total tool back into the macro. Thank you so much!! 🙂

[Christina_H · Accepted solution]

@Jack6627 Updated version as discussed

[Jack6627]

Thanks @Christina_H!! 🙂