IF and Offset Formula

[Aristillus]

Hi There,

I want to use 'Offset' within an 'IF' function formula for an inventory spreadsheet.

Scenario
Each day I receive a daily inventory report for rolling stock counts to be carried out. When I receive the stock count sheets back I then input the results of the physical counts in the 1st Count table. This gives me a result that is either correct or wrong. Correct in that the physical count tallies with the inventory system report, or wrong when they do not tally, which is reflected in the difference column. Dependent upon the results of the physical count, it is determined whether or not a 2nd count is required, or even (after a second count) a 3rd count, leading potentially to an investigation of stock missing, or too much stock.

Attached is a mock up of the spreadsheet I use. It is pretty much self-explanatory, but what I want to do is use an 'Offset' function within an 'IF' formula that will pull the non-tallying results only into the 2nd count table. That is to say bring the 'Part Number', 'Warehouse', and Location of the non-tallying part and place it under the appropriate headings in the 2nd count table. I also want it to do the same operation for non-tallying quantities of the 2nd count results and place them into the 3rd count table. The idea being that at each count stage the size of the counts reduce, after eliminating 'miscounts' and the finding of the missing amounts

For instance, if the first count had 20 parts to count, in an ideal world all 20 counts would tally and no further counts would be required. However, there may be some counts that do not tally, and these would have to have a 2nd count, or even a 3rd count, leading to an investigation if necessary.

What would be the syntax of an 'IF' formula with an 'Offset' function to bring the non-tallying parts to the 2nd count table, or if necessary the 3rd count table?

All help would be greatly appreciated.

Regards

Aristillus

[WideBoyDixon]

I don't see the need to use OFFSET; you can simply reference the other columns in the formulas as in the attached.

WBD

[Aristillus]

Hi WiseBoy,

Many thanks for your reply and your help. We are about a third of the way in the manner that I want it to function.

I don't want anything to appear in the 'Difference' column until an amount appears in the 'Quantity' column.

The reason why I was thinking that 'Offset' was required is because in both the second and third count tables there are gaps between the rows. I don't want there to be gaps. Thus, C65432 should appear directly under A12345, and so on.

Best regards

Aristillus

[Aristillus]

Hi Wiseboy,

I have worked out the nothing to show until a value appears in the 'Quantity column.

Regards

Aristillus

[WideBoyDixon]

OK. No gaps. Needs something a little more complex. Note that I've limited these formulas up to row 1000 so you'll need to change them as necessary if you have a bigger data set. Also, note that they are array formulas and need to be entered with Ctrl+Shift+Enter if you edit them. See attached.

WBD

[Aristillus]

Hi Wiseboy,

I must say that you have supplied me with one hell'uva formula, and I'm currently trying to wrap my head around it. It works to a fashion, but not in the way I'd like it to, so if may, I would like to take things back to the beginning and split the process down to manageable chunks. I want to control the data presentation more efficiently, i.e., when and when it doesn't appear. So, things should occur in the following order of priority...

1) Stock is counted and a quantity is placed in the quantity field on the 1st Stock Count sheet.
2) The 1st Stock Count results are entered onto the system in the appropriate field.
3) If the quantity on the 1st Stock Count tallies with the Inventory Report, then a zero (0) will appear in the 'Difference' column, but nothing will appear in the '2nd Count Required' column. The latter column will remain blank.
4) If the Inventory Report and the 1st Stock Count quantities do not match, the difference will appear in the 'Difference' column, and a 'Yes' will appear in the '2nd Count Required' column.

5) When a 'Yes' appears in the '2nd Count Required' column, this is to trigger the appearance of the 'Part Number', 'Warehouse', and 'Location of the non-tallying quantity in the appropriate fields on the '2nd Count' Table. Nothing more should appear on the '2nd Count Results' table. Furthermore (the tricky bit), there should be no gaps between rows on the '2nd Count Results' table. This is where the amount of counts reduces down. At this stage, nothing at all appears on the '3rd Count' table, because we do not yet know if a third count is required, until the second count has occurred, and the results of it have been entered onto the '2nd Count Results' table.

6) A second count is undertaken for the non-tallying results of the 1st count, and the results of the second count are then entered onto the 'Quantity' field on the '2nd Count Results' table.
7) The 2nd count should eliminate most of the non-tallying results of the 1st count, but there may still be a few non-tallying results, and these will show up again in the 'Difference' column of the '2nd Count Results' table
8) If a difference value (other than zero (0)) does appear on the '2nd Count Results' table, then this will of course result in the appearance of a 'Yes' in the '3rd Count Required' column for that part.
9) The appearance of a 'Yes' will trigger the 'Part Number', 'Warehouse', and 'Location' to appear under the appropriate fields on the '3rd Count Results' table. The same criteria at number 5 will equally apply here.
10) Only after a 3rd count has been conducted and the results entered under the 'Quantity' field on the '3rd Count Results' table will it be known if an investigation is required.

This in a nutshell is what I want to achieve. May I take this opportunity in thanking you for your effort and time in aiding me. I am not unaware of the challenge it presents so I really appreciate your help.

Best regards

Aristillus

[WideBoyDixon]

Hey,

With the requirements that you have, the provided formulas are the "standard" way to achieve the results. They work by fetching the correct INDEX from the appropriate columns based on the criteria you provided. I've updated the sheet now to only show the data at the appropriate times I think. That means you should be able to follow the process you describe above.

The other complex part was fetching the correct "expected" value based on the criteria in the first three columns. For that I used a multi-condition VLOOKUP formula which again is a reasonably standard (I think!) way to achieve the desired results.

I've attached the updated workbook; I hope that this is more in line with your requirements.

Regards,

WBD

[FDibbins]

I don't see the need to use OFFSET; you can simply reference the other columns in the formulas as in the attached.

WBD

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[Aristillus]

Hi WiseBoy,

Apologies for the delay in response. I'll have a look at what you've done over the weekend and get back to you.

Many thanks for all your work and effort.

Regards

Aristillus