2 Attachment(s)
Creating a single Table with different formulas Excel VBA
Hi everyone! I just signed up today to ask for help from y'all. I am a newbie when it comes to VBA. I am literally new like I started just yesterday, y'know. Basically I am trying to create a single table with multiple formulas in it. I can't manually put the formulas because the list in the database sheet is dynamic.
What I am trying to do is this:
Attachment 26435
Categories can be found on the database sheet. Number format of sub categories depend on their respective formats which are indicated on the other sheets.
This is what I have so far: I got this idea from one of the threads here:
Code:
Sub testing()
Set Rng = Sheets("Database").Range("A1").CurrentRegion
Set Rng = Intersect(Rng, Rng.Offset(1))
MCategory = ""
For Each cll In Rng.Cells
MCategory = cll.Value
Set Destn = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1)
With Destn
.Value = cll.Offset(, 1).Value
End With
Next cll
End Sub
I am only able to do the categories part, but I don't know how to do the formulas since they differ depending on whether it's for monthly/weekly and Main Category/Sub Category.
I am using Windows 10 and Office 365 if that's something you need to know.
Attached herewith is my sample file.
Your gracious help is highly appreciated. Thank you so much :)
PS: I also posted here at 1:51AM today: https://www.mrexcel.com/board/thread...l-vba.1131940/
1 Attachment(s)
Calculated Fields Pivot Tables
I understand that calculated fields only apply to columns in a Pivot Table, but is there a way to do as well for rows? Like the main category has a different formula then the sub category also has a different formula. However the sub categories are placed under each main category. They are not on 2 separate columns
Attached is my sample workbook.
I have this formula on Table Sheet:
Code:
=IFS(
AND(LEFT(F$6,3)="MTD",(COUNTIF('Sheet2'!$W:$W,Table!$B8))=1), IFERROR(COUNTIFS(Monthly!$L:$L,"P",Monthly!$J:$J,Table!F$7,Monthly!$A:$A,Table!$B8)/SUMIFS(Monthly!$M:$M,Monthly!$J:$J,Table!F$7,Monthly!$A:$A,Table!$B8),"-"),
AND(LEFT(F$6,3)="MTD",(COUNTIF('Sheet2'!$W:$W,Table!$B8))=0,$D8="Percentage"), TEXT(SUMIFS(Monthly!$K:$K,Monthly!$J:$J,Table!F$7,Monthly!$A:$A,Table!$B$8,Monthly!$D:$D,Table!$B8),"0.00%"),
AND(LEFT(F$6,3)="MTD",(COUNTIF('Sheet2'!$W:$W,Table!$B8))=0,$D8="Decimal"), TEXT(SUMIFS(Monthly!$K:$K,Monthly!$J:$J,Table!F$7,Monthly!$A:$A,Table!$B$8,Monthly!$D:$D,Table!$B8),"0.00 "),
AND(LEFT(F$6,3)="WEE",(COUNTIF('Sheet2'!$W:$W,Table!$B8))=1), IFERROR(COUNTIFS(Weekly!$L:$L,"P",Weekly!$J:$J,Table!F$7,Weekly!$A:$A,Table!$B8)/SUMIFS(Weekly!$M:$M,Weekly!$J:$J,Table!F$7,Weekly!$A:$A,Table!$B8),"-"),
AND(LEFT(F$6,3)="WEE",(COUNTIF('Sheet2'!$W:$W,Table!$B8))=0,$D8="Percentage"), TEXT(SUMIFS(Weekly!$K:$K,Weekly!$J:$J,Table!F$7,Weekly!$A:$A,Table!$B$8,Weekly!$D:$D,Table!$B8),"0.00%"),
AND(LEFT(F$6,3)="WEE",(COUNTIF('Sheet2'!$W:$W,Table!$B8))=0,$D8="Decimal"), TEXT(SUMIFS(Weekly!$K:$K,Weekly!$J:$J,Table!F$7,Weekly!$A:$A,Table!$B$8,Weekly!$D:$D,Table!$B8),"0.00 ")
)
It works only for the first main category and sub categories but if I move on to the new main category it fails already.