Auto Naming Excel Sheets and Function Across Sheets

Here's the scenario I faced. I was given an Excel file which

• consists of 120 sheets of data in uniform template, that
• any of them belong to only one of 3 main categories, and
• under each category, any of them belongs to only one of 3 regions, and
• I have to summarise them in a single "Summary" sheet.

Here what I've done. Because the Excel sheets are named somewhat arbitrary, I firstly need to rename them to a more consistent grouping.

Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) Dim nameln As Integer Dim shname As String . nameln = Application.WorksheetFunction.Find(" ", Range("A1"), 13) shname = Left(Range("A1"), nameln - 1) ActiveSheet.Name = shname & " " & shname2 ActiveSheet.Next.Select Application.Wait (Now + TimeValue("0:00:01")) Range("I1").Value = "" End Sub

After that, it is easier for me to use the SUM function across multiple sheets.=sum('Sales Oil Reg1 Cntrct1:Sales Oil Reg3 Cntrct120'!F20)

Cell F20 in each sheet represents the total sales for each Contract, so in the above formula, it summarises all the sales from all contract from all region.

Here is another useful trick- because I made the auto naming to include its main category and region, I can do the function for only the sheets under each category (or region) by the following=sum('Sales Oil*'!F20)

By the way, once you press Enter after typing in the formula, what Excel did was it analyse the shorthand formula and rewrite them if you were to do manually like =sum('Sales Oil Reg1 Cntrct1'!F20, 'Sales Oil Reg1 Cntrct2'!F20) and so on.

Access Form: Cancel Changes Button

When you enter values in Access forms, and later decided to discard them, there is no ready button that can do such thing. The Cancel button as provided by Access form wizard just simply closes the form. As anybody familiar with Access, once you typed something in forms that are bound to tables, those values are automatically registered as new record.

Small Concept

Designing an ALU model is a straightforward problem. Still, making the model works, requires a colossal amount of understanding in logic synthesis – that is how a block of several gates can be arranged to achieve a certain operation such as addition or subtraction.