Creating a single Table with different formulas Excel VBA

[AaliiyahN82]

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:

a.jpg

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:

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/

[AaliiyahN82]

Hmm any help is super appreciated.

[paulked]

Hi and welcome to the forum.

I think you need to explain yourself better... or at least say what you want with an example!

[AaliiyahN82]

HI Paul thank you for replying.

I have a sheet called Database. It contains all information about categories and sub categories like their target numbers per sub category, as well as the format of each target (like if they are in decimal or percentage). This list changes a lot.

In my Table sheet, I need to list each category with their sub categories below them. Then I need to compute their values based on the weekly and monthly sheet. However, the main category rows have different formulae versus the sub category rows. They also differ on the number format.

[AaliiyahN82]

Hi everyone! Maybe another one also has an idea like paulked?

Any help is truly appreciated! Thank you!

[SamT]

Some suggested Procedures (examples only)

Sub CellFormat (cll As Range, FormatType As String)
   If LCase(FormatType) = "percentage" then Cll.Numberforemat = ...
   If LCase(FormatType) =  "decimal" then Cll.Numberforemat = ...
   If LCase(FormatType) =  "bold" then Cll.Font = ...
   'Room to add more conditions if/when needed
End Sub

[AaliiyahN82]

Hi Sam thanks for responding
I am actually more concerned about the formulae part because they are a lot and they differ for categories and sub categories and whether they are for weekly or monthly --- and they are all in a single table.

I was thinking if this is possible using calculated fields in a Pivot? Is it?

[SamT]

Excel Cell Fomulas are Strings in VBA.
Precede the formula with an apostrophe in the cell and it becomes a String an not a Formula '=SumIf(blahblah). Put those in Column B and in Column A place the names/uses of the formula Cat1SubCat1Wkly then you can use the same method as above.

[AaliiyahN82]

Hi Sam. My apologies, that got me confused ��
I am literally new to all this, I literally started like a day before posting here so I really have no idea about what you're talking about ��

Would you mind giving me a rough example?
Does this require loops? Or something?

[SamT]

https://analystcave.com/top-3-best-e...ded-vba-books/

https://www.amazon.com/Best-Sellers-...gbs/books/4047

https://www.wallstreetmojo.com/vba-books/

What I strongly recommend for VBA Developers is to use Office XP or 2003 because they have built-in help files linked to every VBA Keyword. VBA is still basically the same as it was when created for Excel 5

https://www.ebay.com/itm/MICROSOFT-O...YAAOSwoxVdZAQ3

https://www.ebay.com/p/127902608?iid=303407744277

https://www.ebay.com/p/128261044?iid=303373874962

https://www.ebay.com/p/127813290?iid=233561961107

[snb]

If you create a pivottable based on the data in sheet weekly, no VBA is needed.

[jazz2409]

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:

=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.

[AaliiyahN82]

Hello, my project partner Jazz Homer posted a new thread regarding this. I think she was able to explain this better than I did.

[AaliiyahN82]

If you create a pivottable based on the data in sheet weekly, no VBA is needed.

The thing about Pivots is that while we can create calculated fields, it will be a stand alone field instead of being values using existing data as columns

[AaliiyahN82]

Hello, if there's anyone who can help me and my project partner Jazz Homer. Please. Thank you.

I also posted Jazz's post here: https://superuser.com/questions/1546...s-pivot-tables

[SamT]

I think your data structure is completely FuBar and needs to be completely rebuilt from scratch.

I looks to me as if you are trying to save (no cost) Real Estate by consolidating as much information for Users as possible on as few Excel Worksheets as possible.

I have attached a sample of a well designed, at least IMO, data Table. Note that the Workbook Name contains the Year it is to be used in. I did break one rule of data tables in that I included both Monthly and Weekly data tables on one sheet.

[jazz2409]

Sorry what is FuBar?
This is what they gave us to use.. But I am willing to change it if it's going to make things better (which I think it will)

Actually yes it's for a real estate company 🤣

[paulked]

I'd Google Fubar rather than ask for an answer here

[jazz2409]

Sorry but is there any chance that I can still continue with my sample workbook? I already asked if they are willing to change the view but they are not

They really want the sub categories be under each main category and both have different formulae like the one I indicated above Also they want both MTD and 8-week view just like that

Initially they just wanted to include main categories in that table but suddenly they wanted the sub categories be included under each main category and then look up the value from the two sheets

[jazz2409]

I'd Google Fubar rather than ask for an answer here

I just did.. It's my first time to encounter such term