QUestion re Macros and Triggers

Copper Contributor

Hi.

 

I'm just feeling my way through the intricacies of excel having set up an on-the-fly stock tracking system at work. End of each month I'm archiving the data as values (for the most part) saving into an archive folder and reusing the original form in the shared location, with some updates. I'm doing this manually at the minute and wonder if there is a way to automate the whole thing.

I'm copying data in a column 'Final Totals' and pasting those totals As Values into the 'Initial totals' field.

 

Thereafter, clearing any recorded movement (which zeroes related cells resulting in the 'Final Totals' resetting to match the 'Initial Totals'). 

 

Additionally, I am editing a cell with the Month/year shown to show the next month, prepping the form for use for the upcoming month (currently it's a general field as I wasn't keen on the set up when the cell formatted to 'Date' - I use "May 2020" the auto-format shows "May-20" - but I am up for change if automation is possible).

 

That process needs to be done for a maximum of 3 sheets in the excel doc.

 

I know I can easily make a macro for the Final to Initial totals part, but I'd like to be able to trigger the other parts when the Initial Totals values are updated. Simultaneous application across the three sheets is desirable but not essential. Happy to apply one sheet at a time.

 

Hopefully, that's not too rambling an explanation...can what I'm looking for be done?

 

Cheers

 

Malc

8 Replies

@iapetustitan 

 

Is it possible for you to upload your actual workbook, without disclosing any confidential info?

 

I ask because in your opening sentence you describe yourself as "just feeling my way through the intricacies of excel," which I take to mean you're acknowledging being something of a beginner with Excel.

 

Assuming that to be true (and there's no shame at all in that; we all began at some point), it occurs to me that you might benefit from having different set of eyes look at what you're trying to accomplish and perhaps come up with an altogether different way to manage the data in question. It might, for example, make more sense to just maintain a single comprehensive database (in the form of an Excel Table), from which you use Excel to extract the current month's data (or any past month's, for comparison).

 

Excel has some wonderful tools for that kind of data extraction, summary display, analysis. Yet beginners often mistakenly try to do a lot of that "heavy lifting" on their own, essentially using Excel as if it were an automated ledger sheet, picturing it in their minds as helping doing the various mathematical calculations, but requiring the user to organize the info in a way to make it useful. Sometimes, that is the case. More often, or at least equally often, if you were to approach Excel by giving it lots of data (creating a database, in the form of an Excel Table), you could then use some of the reporting tools built in to it to "automatically" create the summary or analytical display that you want on a weekly or monthly or annual basis.

 

Hence my request: if you'd be willing to share the work you've already done, it's possible that someone here (I or somebody else) could recommend an altogether different way to think about and design your workbook, a more effective way to accomplish your real purpose.

 

If that doesn't appeal to you at all, that's fine. I'll defer to somebody else to give you aid on your presenting request.

 

 

@mathetes 

THanks for the reply.

 

I've actually figured out a working means to do what I was looking for but I;m interested in what different approaches might be considered for the system we have. I don't have the sheets to hand just now to post but will do so over the next couple of days. 

 

It's very ad-hoc right now, set up in relation to the current global crisis and, for me, if it's to keep going I'm all for getting IT guys involved to build a bespoke program (though not rulling out use of excel or similar either). 

LIke I say, I'll get a copy I can post and get your thoughts.

 

Thanks again.

Malc

@mathetes 

Attached a version of one of the Workbooks I've set up. I've 'zeroed' out detail just to avoid any infosec issues.

In total there are three separate versions of this document for different locations and an additional document that pulls the total data from each of the 3 and presents them alongside a cumulative 'National' data total. That sheet is never touched by a user other than to view/archive for the month via a simple (paste to values) macro.

 

The archive itself is a folder of the individual documents with the 'National' data documents (saved monthly) further linked into an overall Year-to-date Document which pulls data from the National docs and separates into regions as well as showing month to month stock consumption (which managers will pull relevant charts and trends from as they require it)...That's a bit more intensive to zero out info, especially with all the network linked files it references, or I'd post it too. 

 

^That make sense?
Right now it seems to be working well, but I suspect, in the coming months, I'll be asked if we can expand it to cover all stock, not just essential/hard to get at the minute stock.

 

Any comments or suggestions for improvement will be appreciated.

@iapetustitan 

 

Thank you for posting that file and all of that information. It does look like you're doing more than just "feeling my way through the intricacies of excel" -- there are some very sophisticated formulas in there (behind "protection").

 

It would be interesting to take time to get into the intricacies of inventory management, but to do so in any truly responsible way would really require both more time and a lot more access to your full system. I'd hesitate even to offer suggestions from this distance.

 

There are probably people here on the site who've a lot more direct experience with the field of inventory management than I [I've worked with a friend's very small business inventory and sales order database, but nothing more] , so maybe some of them will be able to offer more knowledgeable advice.

 

Have you looked through the various templates that can be found on-line?

Thanks @mathetes 

The 'protection' on the sheets is really just to avoid any mishaps, each person that has access also needs to be able to run the macros to archive the monthly documents so it's really just a 'stop, are you sure?' measure. 

 

I've mostly been searching for tutorials online, not used any templates as I had a sheet already drawn up when I was asked to get involved further (to be honest, I made work for myself by being unhappy with the 'everything on paper' plan the others in the department had come up with - lesson learned...don't help! Pretty much just putting into google what I want to happen and finding a tutorial that delivered. Still not 100% on what SUMIFS actually means but I understand what I need to put in it to get mydesired results - a wee bit of trial and error, linking two tutorials to get what I wanted (especially with that 'Consumed' section...real pain when they asked me for that but theres always someone helpful out there who has the advice you need).

All that said I think I will check out whatever templates are about online, see if there is a better, more streamlined way of capturing the required data. If I could find a way to add new stock layers in aphabetical or just product type order without re-jigging the whole thing I'd like that. Right now, I just tack on the bottom for everybodies ease of use. 

 

Thanks for the reponse.

 

Malc

 

@iapetustitan 

 

What you've done is really quite neat. If I were to make a suggestion, I'm afraid it would involve totally re-thinking the basic design. And I don't want to subject you to that.

 

My problem (I also think of it as "my strength") is that I tend to come at almost all such applications from the perspective of first creating a massive transactional database, from which monthly summaries (or quarterly, or annual) can be produced. 

 

For example, I'm working now on updating my personal budget and expense tracking spreadsheet, the heart of which is a single database, into which I import (via CSV file imports) monthly statements from multiple checking and credit card accounts. It currently runs to over 2,300 rows with transactions only from 2019 and 2020 to date. From that single database, I can produce reports that show income and expenses by category on a monthly or quarterly basis. And those reports are generated very easily by means of the Pivot Table.

 

For my friend with his small business (importing stoneware dining and baking dishes), there's a massive database at the heart, from which we can produce SKU (or UPC) based reports on volumes sold, again on monthly or quarterly bases. We just import statements from the various vendors (akin perhaps to your several stores or regions)...   At this point, we've not been tracking the incoming shipments, but that would be easy to add, just as my personal budget spreadsheet tracks both income and outflow, cash in & cash out, by source, by outflow category.

 

I got here by way of a career (from which I retired in 2002) during which I was the director of a major database system for a major corporation....and I just learned to think of most computer applications in terms of highly organized databases (relational databases, with multiple tables highly "normalized," if you're into that technical side of things)......  so I also carefully distinguish between the Input side of things and the Output. In my experience, a lot of beginners with Excel confuse the two, approaching the collection of data using fancily formatted sheets that could be saved for the output phase. I don't know that this is what you're doing, but it could be; on the other hand, you're collecting data from multiple locations or multiple operations, so you want that to be user-friendly for those entering that data.....so it might make sense.

 

Most of the templates I've seen are not what I'd want to work with; it may be that there are some really professionally designed ones. But you did mention already having IT create for you a bespoke program; I suspect that for the scope that you appear to be dealing with, that probably makes good sense.

 

Anyway, I say all this to say, you've got a big task ahead of you and I wish you well.

@iapetustitanso when I see @mathetes say something is neat I want to take a peek :)  In general I agree with mathetes on good workbook design.  In this case I suspect the 2 tabs are filled in by different people and may make sense to keep them separate.  But the main reason I'm replying here is to encourage you to learn about cell references in excel and in particular how and when to use the '$' sign.  For example you have col T on both sheets have a formula like:

=SUMIFS(E6:E500,C6:C500,N6,H6:H500,T5)

You must have copied and pasted that formula into each cell changing N6 to N7 to N8 … as you went down the column.

There are a few ways to have Excel help you in these cases.  You can:

  • copy 1 cell, select the range you want to apply it and paste
  • select the first cell and drag the little box in the bottom right corner down the whole range you want it to auto fill
  • double-click that little box (works if you have a column next to it already)
  • highlight the range you want and select 'fill down' from the menus 

BUT in order for that to work right you need to let Excel know which of the cell references are 'relative' (e.g. the cell 1 to the left of me) vs 'absolute' (e.g. always look at cell A1).  The default is 'relative' and you add the '$' to make it absolute.  Further more you can make the column reference or the row reference 'absolute' independently. A simple example is if you have the number 1, 2, 3, 4, …. across the top row (row 1) and down the 1st column (col 'A') and you want to create a multiplication table you can create a formula in B2 =$A2 * B$1 which says multiply the cell to my left by the cell above me.  And then as you 'fill down' and 'fill right' excel will auto increment the 'relative' parts and keep the 'absolute' parts. So in B3 it would become $A3 * B$1 because '$A' means always use col A but the 2 was changed to 3 because that was 'relative' (i.e. the same row as 'me' and the formula was in B2 but now it was copied into B3).  The B$1 stayed the same because you copied from B -> B so although it is relative, that didn't change and the $1 says to keep looking at row 1.

phew...

now back to your formula what if you had used:

=SUMIFS($E$6:$E$500,$C$6:$C$500,$N6,$H$6:$H$500,T$5)

Notice all the '$', because the ranges E6:E500 and C6:C500 and H6:H500 are all fixed 'absolute' ranges.  Because you are only filling down 1 column you could ignore the $ in front the columns but in case you decide to copy it I thought it would be good to have.  But NOTICE that $N6 does NOT have the '$' next to the '6' so as you copy that cell formula down the rows it will automatically increment accordingly.

There are lots of other things you can also use and learn like referring to a whole column (E:E) or using named ranges, or the new FILTER function, but learning about the '$' is a 1st.

Keep up the good work.

@mtarler 

Deary me! I thought the $ was an artefact I had created by mistake! Ha. I totally get what you are saying here and will implement in the sheets. I was confused as to why the cells weren't updating to the correct numbers all around while I was copy and pasting formula. Worked for some, didn't work for others. Now I know. Thanks a lot for this.

 

@mathetes thanks for you advice. I agree there is a better way to approach this just waiting for me to find it. I've been thinking along the lines of a retailer style solution (though there is no purchasing on outgoing items it's virtually the same idea). Not come across any even half decent templates to inspire me yet though.  I'm going to keep throwing ideas around and see where they land - ideally a fully centralised sheet with ability easily draw usage trends and stock level projections is where I want it to go, lot to learn though. 

Thanks again folks. Any more advice and tips on the thread here from others will be very much appreciated.