Search:

Type: Posts; User: theBardd; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.04 seconds; generated 42 minute(s) ago.

  1. Replies
    1
    Views
    242

    Re: Excel power query

    In the code generated when you load and transform the files, you should see a step called 'Removed Other Columns' or similar. That should have a Table.SelectColumns function, just add ', "Folder...
  2. Re: Get & Transform with a parameter from Access query

    Pull back all of the data from Balances and then use Power Query to filter to your required CorpId.
  3. Replies
    2
    Views
    731

    Re: Converting IF statement to Power BI

    same as Excel really.

    if [Field 1] <> [Field2] then if [Field 1] > 0 then [Field 1] else [Field2] else if [Field2] = 0 then "N/A" else [Field2]
  4. Thread: new name

    by theBardd
    Replies
    4
    Views
    110

    Re: new name

    ActiveSheet.ExportAsFixedFormat _


    Type:=xlTypePDF, _


    filename:=Activesheet.Range("C2").Value, & ".pdf" _


    OpenAfterPublish:=False, _
  5. Thread: EOM formula

    by theBardd
    Replies
    10
    Views
    221

    Re: EOM formula

    I would recommend that you decide what should happen if there something other than nnEOM or nnFI in J4, and we build the formula to act accordingly.
  6. Thread: new name

    by theBardd
    Replies
    4
    Views
    110

    Re: new name

    Is C2 on that sheet?



    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    filename:=Activesheet.Range("C2").Value, _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True,...
  7. Thread: VBA Request

    by theBardd
    Replies
    1
    Views
    93

    Re: VBA Request

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo wsc_exit

    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range("B:B, D:D, F:F")) Is Nothing...
  8. Thread: EOM formula

    by theBardd
    Replies
    10
    Views
    221

    Re: EOM formula

    I guess it would be


    =IF(RIGHT(J4,2)="FI",C4+SUBSTITUTE(J4,"FI",""),IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM","")))
  9. Replies
    2
    Views
    92

    Re: IFS statement with Data Validation

    Data Validation doesn't auto-populate, it just checks that the input is valid, or provides a list to select from.
  10. Thread: EOM formula

    by theBardd
    Replies
    10
    Views
    221

    Re: EOM formula

    How about this?


    =IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM",""))
  11. Thread: Vba loop

    by theBardd
    Replies
    29
    Views
    299

    Re: Vba loop

    Do you know how to build a loop?


    Do you know how to terminate the loop?

    Do you know how to use the inputbox?

    Do you know how to use variables?

    Do you know how to increment the variable?
  12. Replies
    6
    Views
    141

    Re: Dynamic Average Selling Price Pivot

    I am failing to see why a pivot doesn't work for this.
  13. Replies
    5
    Views
    199

    Re: Using SUMPRODUCT to SUM a Defined Range

    Are the names similar size?

    Can you post a sample of the data and tell us what result you want to make it clearer?
  14. Re: SQL Query on PowerPivot Data Model through VBA?

    As far as I am aware, whilst you can do a lot of stuff with the connections in VBA, the actual model is not exposed to VBA (and remember, it is an OLAP cube!).

    You could build your dashboard using...
  15. Thread: counting

    by theBardd
    Replies
    13
    Views
    259

    Re: counting

    Sub countmontecarlo()
    Dim cell As Range
    Dim lastrow As Long
    Dim n As Long
    Dim i As Long
    With ActiveSheet

    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ...
  16. Re: macro or other ways to arrange columns so that there will be no consecutive duplicates

    Okay, I've read it properly now and see what you are trying to do.

    Take a shot with this (although it doesn't quite work with your first set)


    Public Sub ReorderDups()
    Dim ordered As Boolean...
  17. Re: macro or other ways to arrange columns so that there will be no consecutive duplicates

    I'm using Excel 16 so iut should work, but see if this works better for you


    Public Sub RemoveDups()
    Dim lastrow As Long
    With ActiveSheet

    lastrow = .Cells(.Rows.Count,...
  18. Re: Filter Multiple Pivot Tables Having Different Sources

    No it is not the slicer but the pivottable. Any slicers, including the SP slicer, are immaterial. Slicer SP will update pivot as it attached to that pivot, and it is the update to the pivot 1 that...
  19. Re: Filter Multiple Pivot Tables Having Different Sources

    One thing to note, if you filter PivotTable1 directly, selectin specific items in the pivottable rather than the slicer, that will update all of the others because the slicer gets updates and I check...
  20. Replies
    7
    Views
    219

    Re: Assign Code to Numbers

    NO, it is an Excel formula. Why use VBA when a formula does it, just plug it ito the cells adjacent to the codes.
  21. Re: Filter Multiple Pivot Tables Having Different Sources

    I forgot to say, add a slicer on the column SP attached tio the first pivottable, and select your chosen values from there. That is what triggers the event.
  22. Re: SQL Query on PowerPivot Data Model through VBA?

    Why not just add a pivot table? All fields exposed, filters, slicers, all you could need. You can even add DAX measures and get really smart.
  23. Re: Filter Multiple Pivot Tables Having Different Sources

    Try this worksheet event procedure


    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim Slicer As SlicerItem
    Dim pvt As PivotTable
    Dim pvtItem As PivotItem
    Dim values As...
  24. Re: macro or other ways to arrange columns so that there will be no consecutive duplicates

    Try this


    Public Sub RemoveDups()
    Dim lastrow As Long
    With ActiveSheet

    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

    .Sort.SortFields.Clear
  25. Re: In Powerquery, How to filter a Table using criteria stored in another Table

    This should get you going


    let
    Source = Excel.CurrentWorkbook(){[Name="tblSales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Product",...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4