exclude TOTAL row and sum TOTAL column based on sheet name

[KalilMe]

Hello
I want summing amount for the whole column (TOTAL) for each sheet and exclude TOTAL row from summing until doesn't sum twice for the same values have ever summed in TOTAL row .
so I want to create the whole report as in REPORT sheet , but should brings the balances from ASD sheet before sum the whole every sheet .
when merge the whole column TOTAL should be for each sheet name as in column B(like FIRST SALES+SALES, FIRST BUYING+BUYING.....and so on) .populate TOTAL FIRST SALES =100,000 from ASD sheet and sum TOTAL column for SALES sheet=1210+462+440+220+330=2662
and show TOTAL row for each sheet as in column A (SALES TOTAL ,PURCHASE TOTAL,.... and so on) with auto serial

and should calculation as in lastrow (NET)
last thing the TOTAL column will be changed in location in others sheets as you note it .
and should clear data for REPORT sheet when run the macro every time .
thanks

[HansDouwe]

For sheet SALES and BUYING, please try

=SUM(J2:J8)/2

and so on

[wk9128]

worksheet or Tab name : REPORT

Cell C4 formula , Then copy this to the blue cell

=SUM(C$2:C3)-SUMIF($A$2:A3,"*TOTAL*",C$2:C3)*2

[KalilMe]

I think misunderstood guys
should sum the whole TOTAL column for each sheet
populate TOTAL FIRST SALES =100,000 from ASD sheet and sum TOTAL column for SALES sheet=1210+462+440+220+330=2662

[wk9128]

Hi KalilMe

From the attachment you provided, I can only see all the worksheet names SALES, BUYING, EXPENSES, RECEIVED, ASD. Summarize all relevant information to the worksheet name REPORT, not to the worksheet name ASD as you said
I don’t know if this is the right understanding? Please explain the requirements

worksheet or Tab name : REPORT

Cell C2 formula

=VLOOKUP(B2,ASD!B:C,2,)

Cell C3 formula

=SUMIF(INDIRECT(B3&"!A:A"),IF(B3="RECEIVED","<>","TOTAL"),OFFSET(INDIRECT(B3&"!A:A"),,MATCH("TOTAL",INDIRECT(B3&"!1:1"),)-1))

Cell C4 formula

=SUM(C$2:C3)-SUMIF($A$2:A3,"*TOTAL*",C$2:C3)*2

COPY THIS RANGES C2:C4 , place to cell C5,C8 and C11

[HansDouwe]

Please put your expected results in your sheet.

[Marc L]

Hi,

as a VBA procedure is useless just using formulas like in post #2 …

[KalilMe]

Please put your expected results in your sheet.

I gave you in OP and inside the sheet .

so I want to create the whole report as in REPORT sheet

example

when merge the whole column TOTAL should be for each sheet name as in column B(like FIRST SALES+SALES, FIRST BUYING+BUYING.....and so on) .populate TOTAL FIRST SALES =100,000 from ASD sheet and sum TOTAL column for SALES sheet=1210+462+440+220+330=2662

[KalilMe]

not to the worksheet name ASD as you said

just match C2 with in ASD sheet with C2 in REPORT sheet .
I will check your formulas.

[KalilMe]

as a VBA procedure is useless just using formulas like in post #2 …

thanks for your advice , not really sure formula or vba .just I posted here.

[Marc L]

A VBA procedure seems superfluous, why over complicating instead of using beginner level worksheets formulas ?‼

Just remove rows indexes like according to post #2 =SUM(J:J)/2 …

[KalilMe]

@wk9128
may check C3
as I said

and exclude TOTAL row from summing until doesn't sum twice for the same values have ever summed in TOTAL row .

so either summing just for last row(TOTAL) and exclude the others values in TOTAL column or exclude last row(TOTAL) and summing the others in TOTAL column . but your formula sums whole values in TOTAL column without exceptions .

[Marc L]

That's 'cause of your bad TOTAL formulas design ! The reason why the sum must be divided by 2 …

As a smart worksheet must use the SUBTOTAL worksheet function for each intermediate total.

[KalilMe]

That's 'cause of your bad TOTAL formulas design !

there is no formula in my data!

[Marc L]

So can you delete each total row in data worksheets or use a SUBTOTAL formula ?

[KalilMe]

So can you delete each total row in data worksheets or use a SUBTOTAL formula ?

surely not !
this way create by macro .

[Marc L]

Such a bad code not respecting Excel rules !
And why this code does not create directly the worksheet REPORT ?!


So two ways without needing to create another VBA procedure, one smart and one dumb.

The smart needs to have TOTAL rows in data worksheets using the Excel worksheet function SUBTOTAL
in order the formulas to be added to worksheet REPORT works forever just using the same function according to Excel design
so you have to modify the original VBA procedure …

If for some dark reason the sources worksheets must stay as poor as they actually are
then the dumb way is to use the worksheet function SUMIF but may fail in some cases …

So what's your choice ?

[jindon]

Sub test()
    Dim a, b, i As Long, n As Long, t As Long
    a = Sheets("asd").[a1].CurrentRegion.Value
    ReDim b(1 To UBound(a, 1) * 3, 1 To 3)
    For i = 2 To UBound(a, 1)
        n = n + 1: t = 1: b(n, 1) = t
        b(n, 2) = a(i, 2): b(n, 3) = a(i, 3)
        n = n + 1: t = t + 1: b(n, 1) = t
        b(n, 2) = Split(Trim$(a(i, 2)))(1)
        With Sheets(b(n, 2)).[a1].CurrentRegion
            b(n, 3) = Application.Sum(.Columns(.Columns.Count)) / _
            (2 + IsError(Application.Match("total", .Columns(1), 0)))
        End With
        n = n + 1: b(n, 1) = b(n - 1, 2) & " TOTAL": b(n, 3) = "=sum(r[-2]c:r[-1]c)"
    Next
    n = n + 1: b(n, 1) = "NET": b(n, 3) = "=r[-1]c-r[-4]c"
    With Sheets("report").[a1].Resize(, 3)
        .CurrentRegion.ClearContents
        .Value = Array("ITEMS", "NAME ACCOUNT", "TOTAL")
        .Rows(2).Resize(UBound(b, 1)) = b
    End With
End Sub

[KalilMe]

And why this code does not create directly the worksheet REPORT ?!

the ideas don't coming at one time.

The smart needs to have TOTAL rows in data worksheets using the Excel worksheet function SUBTOTAL
in order the formulas to be added to worksheet REPORT works forever just using the same function according to Excel design
so you have to modify the original VBA procedure

this is better choice .
but need more work and need write the code from scratch then

[KalilMe]

Hi jindon
I will check your code and comeback to inform you how goes.

[KalilMe]

thanks jindon for your code .
works as I want it .

[KalilMe]

Hi Jindon
sorry I came back
I no know if you can help .
I need get rid of error subscript out of range in this line

With Sheets(b(n, 2)).[a1].CurrentRegion

I have two cases
1- if I don't have balance in ASD sheet then should consider the value is zero even if there is no account name in column B
example :
if I have sheet name is STOCK and the TOTAL column =10000 before ASD sheet and There is no equivalent in the ASD sheet
then will show in REPORT sheet like this
in column B =stock and column C =10000 and under it in column A=STOCK TOTAL , column C=10000
2- if there is balance in ASD sheet in column B= FIRST STOCK ,column C=2000 and there is no equivalent in before ASD sheet
then will show in REPORT sheet like this
column B =FIRST STOCK ,c=2000 and under it in column A=FIRST STOCK TOTAL , column C=2000 .
what I mean sometimes what is existed in ASD sheet couldn't be equivalent in before ASD sheet and what is existed in before ASD sheet couldn't be equivalent in ASD sheet.
thanks

[KalilMe]

Hi jindon
I'm still need your help to adapt this project
so if I don't have any balance in ASD for the existed sheet then should show as in row 11,12 for report sheet for RECEIVED sheet not existed the balance in ASD sheet, and if there is balance in ASD sheet but there is no sheet name is matched with the balance in ASD sheet then should show in report sheet as in rows 13,14.
thanks

[jindon]

Is this the last change?
If not, reveal everything in one workbook.

[KalilMe]

Is this the last change?
If not, reveal everything in one workbook.

ok I will post new file soon.

[KalilMe]

the first case as I explain in post #23
the second case if I select the year then should sum for just is relating with year as I posted in YEARLY sheet when select year from F1 but should be in REPORT sheet. as to first balance from SD should show in all of about if select year or not.

[jindon]

try the atached.
when F1 = "", all years.

[KalilMe]

it's perfect !
but I would show the first balance from ASD sheet for already existed sheets when select the year or not .
should ignore year from ASD sheet whether select year or not as I said

as to first balance from SD should show in all of about if select year or not

[jindon]

No idea about what you are trying to say.
It is working as you showed in the sample workbook.

Upload a workbook with your desired result.

[KalilMe]

I highlighted rows should show when select 2024 year.
should ignore year from ASD sheet when brings first balances when select any year.
always should show first balances regardless what's the year.

[jindon]

1) change in "test" sub procedure.

    GetList dic, ws, myYear

to

    GetList dic, ws

2) replace "GetList" sub procedure with

Sub GetList(dic As Object, ws As Worksheet)
    Dim a, i As Long, s As String
    a = ws.[a1].CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If a(i, 2) Like "*? ?*" Then
            s = Split(Application.Trim(a(i, 2)))(1)
            dic(s) = Array(a(i, 2), a(i, 3))
        End If
    Next
End Sub

[KalilMe]

Great!
many thanks for adjusting.