QUestion re Macros and Triggers

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.

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.