Sorting & allocating data entered on one sheet to multiple columns on another sheet.

I'm working on a sales/inventory tracking workbook that has stretched my knowledge of Excel far beyond my usual comfort zone. But, with the help of this forum and countless hours of Googling, I'm getting to the end. My last hurdle is as follows:

I think this problem can only be solved with that VBA voodoo magic that you guys do so well.
Unfortunately, I'm a big newbie when it comes to VBA, so I don't even have a starting point for you guys.

I have a series of identical tabs called "Daily - Jan" through "Daily - Dec" where the data entry person will enter all her sales info. I also have another tab called "Inventory" where she will also enter her inventory tracking info. Everything works like a charm, but I'm trying to find a way where the units sold data entered on the "Daily" sheets in the columns called "Daily Units" is automatically matched-up with the corresponding "Item Description" on the "Inventory" sheet and allocated to specific columns depending on their "Dept." To make things a little more complicated, I also need the additional criteria of filtering by the earliest date under 100% on the "Inventory" sheet.

Along with attaching a reduced version of the file, I also made a visual mock-up to hopefully make this easier to understand.

I know this is a big ask, so a ton of thanks go out to you guys ahead of time.

Allocate Sales to Inventory.jpg

Hi Big Moe. Can you follow the playback below and let me know if I've understood what you're after?

• Units consumed each day are entered against each item in the Daily sheet in the column headed "Daily Units".
• Each item also has a unit of measure, category and department (they all say Retail but presumably this is just test data).
• When someone enters an amount in the Daily Units col, you want that amount to go into the Inventory sheet in the column labelled "QTY. SOLD/USED" (label in row 4) in the group of columns corresponding to the department (Retail, Sushi, Prepared Food etc) - label in row 3 - against the corresponding product in col A.
• Unit, Cat, Sort and % are all calculated so no issue. The date is the date from the Daily sheet corresponding to whichever report had the Daily Units edited.
• What goes into Source, Qty, and Total Cost?
• What happens if the product is not in col A? Or is this only showing some of the products but they will all be there in the full sheet?
• And the sort is the earliest date and highest % under 100% so that's fine.

This is my key question: is the structure of the Daily sheets EXACTLY the same for all months? Same header rows and same exact header text? The reason for this is that unless I put in a bunch of named ranges, I need to rely on headers not changing and things like the position of the report date relative to the heading "Daily Units" being 3 columns to the right and 7 columns up.

If you can let me know if any of the above is wrong and answer the questions, I'll take a look at it for you.

Also - I think the formula in the Inventory sheet col E to calculate the % has an error. Cell E6 has the formula as:

=IF(H6="","",IF(A6="","",(L6+O6+R6+U6+X6+Z6+AB6+AD6+AJ6)/H6)*100)

But I think the last reference in the summation - AJ6 - is supposed to be AK6 (the sum of units adjusted).

MatrixMan,
Thank you so much for taking the time to look at my question. I understand it is a bit involved, so I will do my best to clarify things.

*Units consumed each day are entered against each item in the Daily sheet in the column headed "Daily Units".

YES.

*Each item also has a unit of measure, category and department (they all say Retail but presumably this is just test data).

YES. I haven't entered all the department labels, but I can adjust code to fit final labeling.

*When someone enters an amount in the Daily Units col, you want that amount to go into the Inventory sheet in the column labelled "QTY. SOLD/USED" (label in row 4) in the group of columns corresponding to the department (Retail, Sushi, Prepared Food etc) - label in row 3 - against the corresponding product in col A.

YES.

*Unit, Cat, Sort and % are all calculated so no issue. The date is the date from the Daily sheet corresponding to whichever report had the Daily Units edited.

The date in the Inventory Sheet is the date when we (The Lobstore) purchased the product to be sold and became our inventory (not when the product was actually sold to our clients).

*What goes into Source, Qty, and Total Cost?

"Source" is just the name of the wholesale vendor we purchased the item.
"Qty" is the amount we purchased at wholesale (to be sold in our retail store).
"Total Cost" is the total wholesale amount we paid for that line item, which in turn calculates unit cost for the "Daily" sheets.

*What happens if the product is not in col A? Or is this only showing some of the products but they will all be there in the full sheet?

The list in Column A is only items that are purchased by us for resale and hence are in our inventory. If the user tries to enter sales information in the Daily sheets without having a corresponding entry in the Inventory Sheet, it will not generate a cost and hence the user will not be able to enter the sales info, until they go back and enter an Inventory entry with the corresponding item description. When the user enters a value in the PRICE/UNIT" column on the Daily sheets, the "COST/UNIT" from the Inventory Sheet is automatically entered in the corresponding "COST/UNIT" cell of that line item. If there is no such item in the inventory, the "COST/UNIT" cell on the Daily sheet will stay empty.

*And the sort is the earliest date and highest % under 100% so that's fine.

Yes, this was my way of making sure that once the number of units that we purchased for that line item has been sold, the % value would become 100 and hence would eliminate that line item from future sales allocations.

This is my key question: is the structure of the Daily sheets EXACTLY the same for all months? Same header rows and same exact header text? The reason for this is that unless I put in a bunch of named ranges, I need to rely on headers not changing and things like the position of the report date relative to the heading "Daily Units" being 3 columns to the right and 7 columns up.

Yes, each month has the exact structure for each day. I have attached a different reduced version of the file to show the first three days of each month, so you can see a sample of each month. Each month looks exactly the same other than the number of days.

Also - I think the formula in the Inventory sheet col E to calculate the % has an error. Cell E6 has the formula as:

=IF(H6="","",IF(A6="","",(L6+O6+R6+U6+X6+Z6+AB6+AD6+AJ6)/H6)*100)

But I think the last reference in the summation - AJ6 - is supposed to be AK6 (the sum of units adjusted).

You are correct and I fixed that error on the new file below.


Lastly, I'm still trying to work-out all the bugs in the workbook, so please excuse any formula errors I may have missed.
Again, I am fully aware that this is a big ask, so even if you can't solve the whole problem, a partial solution will be much appreciated.

Hi Big Moe. Thanks for the detailed response and additional sample data - that all looks fine. I'll take a look at it later today and should have a solution by the end of the day.

Hi Big Moe. Code below goes in the worksheet object and fires whenever a change is made in any sheet in the workbook. Run a few tests and see if it's doing what you need ... Also in attached sample file - I've put comments throughout the code to help you follow the logic & trace what it's doing. Let me know if anything isn't clear and I'll explain.

Please Login or Register  to view this content.

Hope that helps. MM.

Hi Big Moe. Of the two issues you've described, I understand the second one (correcting a value already in the Daily sheet). A complication with this is if a user enters a value that is the full inventory, the item is removed from the Inventory sheet and "correcting" that will be an issue. I'm not sure if we can, but let me have a think about how we might resolve this one. Although it wouldn't "fix" this issue entirely, one thing we could do is to fire the inventory update only when the inventory sheet is activated or a daily sheet is deactivated (instead of when anything changes in the daily sheets). It won't fully address the issue, but it will allow a user to make corrections and only update the daily sheet when they're done. Still, if they made a mistake and the item is no longer in the inventory, the problem remains.

The first one I don't really understand this bit: "go to the "Daily" sheet and enter one by one quantities in the respective field". You enter a quantity of 5 in the Inventory sheet (col H), then enter 5 against a valid item, the code runs and the item is removed from the Inventory sheet. I can't replicate the error you getting ... can you outline it using real data and entries?

A related issue I found is that when you use all of an item's inventory, it is removed from the Inventory sheet (which is by design), but that means the profit figures in the Daily sheet don't calculate because COST / UNIT is blank. This just needs to be allowed for in the PROFIT / UNIT col.

While on the formulae, the one in col L reads:

Please Login or Register  to view this content.

And could more efficiently be written:

Please Login or Register  to view this content.

You can ignore F59 because if it is blank then G59 is also blank (see formula in G59):

Please Login or Register  to view this content.

Hi Big Moe. Hmmmm ... I thought that was a feature you had as a requirement as I thought there were no 100% items in the inventory. But no problem and yes, I can adjust it so it doesn't remove the item from inventory.

lol - no problem ... you didn't actually say to remove it - I inferred it from the description. but - not to worry; it's one line and already sorted. I'm working on the other two issues ... will let you know how I get on.

Hi Big Moe. Well this has been an interesting exercise ... have a look at the attached, test it out a bit, and let me know what you think.

The extra formula-checking code you had in the Daily - Jan sheet I deleted and wrote the functionality into the Workbook_SheetChange event with the rest of the main code. This means you don't need that code in every single Daily sheet as this will cover it. It's this bit in the main code about 1/2 way down after the header row is set in the variable lngInvHeaderRow:

Please Login or Register  to view this content.

The bulk of the code is in that event module and shown below:

Please Login or Register  to view this content.

There is a new module called "StoreValues", which holds the values of the selected cells in an array in case the user changes that selection. If they do and they make a mistake or overwrite a previous value, the values in the array can be used to undo or correct the entry. This is used when someone enters 2 against an item but then changes it to a 4 (for example). It is also used if the user enters 10 against an item but there are only 5 remaining in inventory. That code is here:

Please Login or Register  to view this content.

And finally, the code above is also called when ANY selection change is made or sheet is activated. This is because the Selection_Change event doesn't fire when a sheet is selected to pick up the active cells, which need to be written into the array to hold their current values.

Please Login or Register  to view this content.

Let me know how you get on testing with real data.

Get well!!!!!!!!!!

Hi Big Moe - I did wonder what had happened and am glad to hear you're on the mend - thanks for letting me know :-)