Show variable changes when new sheets are added daily compared to every previous day's sheet

[msdclark]

Part of my issue is attempting to explain what I need, so bear with me in advance.

I use a reporting system (Microstrategy) where I export product sales data into Excel. The reporting structure for Microstrategy only calculates to monthly data changes, yet I need to analyze product sales made from one day to the next. Each day that I pull the report it does update with previous day's product changes. I need to pull this report on Monday and compare to Friday of the last week. I will pull the report on Tuesday and compare to Monday. I will pull the report on Wednesday and compare to Tuesday. And so on.

I am using unique product numbers. There are columned sections for each market, then underneath this heading here are numerous columns showing what categories changed for each product.

If there are 20 packages of widget A sold in any market as compared to the previous day's business then I need to highlight this someway. In addition to the "sales" of widget A, there are transfers between markets, adjustments made for broken or misplaced product, etc. I need each of these categories highlighted to show changes so I can immediately pull a different report and drilll down to see exactly where the product was sold.

I can figure out a formula to show these changes by comparing worksheets. However, I am stumped because I will be adding new worksheets each day which leads me to believe I would have to update the formulas each day. It seems quite time consuming just to see changes in product movement on a daily basis.

 

[mikerickson]

Perhaps this UDF.

Function RangeOnLastSheet(RangeAddress As Variant, Optional SheetOffset As Long = 0) As Range
    Application.Volitile
    If TypeName(RangeAddress) = "Range" Then
        RangeAddress = RangeAddress.Address
    End If
    With ThisWorkbook
        With .Sheets(.Sheets.Count - SheetOffset)
            Set RangeOnLastSheet = .Range(RangeAddress)
        End With
    End With
End Functionl

It will return the indicated range from the right most tab of the workbook. So =SUM(RangeOnLastSheet(A:A)) will return the sum of column A on the last sheet.
Adding a new sheet will change which sheet is summed.
The SheetOffset argument can be used to look to other sheets. SUM(RangeOnLastSheet(A:A, 1)) will return the sum of column A on the second tab from the right.

Note that the RangeAddress argument can be either a string or a range, allowing for absolute/relative dragging. If RangeAddress is a range, it doesn't matter which sheet it is on, the range returned will be from the rightmost (less offset) sheet.

 

[msdclark]

I could have sworn I asked another question and was still awaiting an answer. I apologize for not responding sooner.

Currently, I add the new worksheet to the left of all the tabs. Do I just need to reverse my order and start adding at the far right end of the tabs?

 

[mikerickson]

If you want this UDF to work from the left of the tabs, change to this line.

With .Sheets(1 + SheetOffset)