Posts by jpetraityte

    Hi Roy, I am not sure if understand either of you suggestions.


    Column H is not part of the pivot table and comes from a different source data.


    I have already the formula below in Column H to show the budgeted figures.


    =IFERROR(IF(A16<>"",VLOOKUP(E16,Budget!$A:$I,9,FALSE),""),"")


    I was thinking of a way to edit this formula, say if A16="Grand Total" then sum column H...the sum total in H should be in the same row as "Grand Total" in Col A - which will vary with slicer selections.


    So far I had no luck so not sure if that is even doable :) Any further suggestions are much appreciated.

    Yes, I want the total for the Column H, but the position of it will vary depending on which slicers will be selected, so I was trying to come up with a formula that references to "Grand Total" in Column A.

    Sorry, I have one more question :)


    how could I adjust my formula in Budget - Column H so that once it finds "Grand Total" in Column A it sums everything above that in Column H?


    Thanks

    Jurate

    Thanks Roy, that seems to have done the trick :)

    I have been researching about possible combining the two tables first and only then creating a pivot table, but that option looked too complicated for me :)

    Hi,


    I am trying to create a dashboard which compares actuals sales VS budget also combined with slicers so people can make their own selections.

    I have two tabs, one for the actual invoices to date which will be updated every day, and the other one for budget which is set at the start of the year.

    The pivot table and the slicers from "Invoices" tab and then I use the formulas to calculate the Budget and the %Achieved.

    However, the issue I have come across is that to date I have no invoices to Sainsburys or West Region or certain towns but I still want them to appear in the slicers and the dashboard so I could see the full view of the budget set for the month.


    I would be very grateful if you could help me to find a solution to overcome this problem please.


    I have attached my sample data.

    Files

    The save button now does exactly what I wanted. thank you very much!

    the is an issue with reset function as the forms does not reset automatically after it is posted into the database.

    also any chance you could format "Amount" text Box to so that I have #,##0.00;-#,##0.00 format?

    For some reason my checks were not working properly, it was still posting the form into the database even though not all of the information was entered...so I am happy to change with your version as it is much shorter :)


    However, I have tried to run the VBA and even all the fields are complete it brings the message "Please complete the from box". I have attached updated workbook. I would be very grateful if you could have a look at it again.


    thank you so much!

    hi Roy, thanks for taking your time to looking at this. So, where do I have to position the above code? It has to be linked to "Save" button right?

    I actually need to make sure that all the fields are complete before saving the pdf form as well as posting to database tab.


    Re border - in excel for example you can apply "thick outside borders" to a cell or range of cells. So, I was wondering if I can apply somehow this border to a Text Box because when you print out the form the bottom border of text box is hardly visible.


    Thanks a lot, Jurate

    Hi,


    Please could someone review the VBA code in the attached workbook and let me know how the code could be changed so that the form doesn't get posted into database until all the fields are completed?


    Also, is it possible somehow to put a border around Text Box & Combo Box so it looks nicer when the form is printed out.

    How can I format "Amount" text Box to so that I have #,##0.00;-#,##0.00 format?

    Thanks

    Files

    • Form VBA.xlsm

      (2.23 MB, downloaded 111 times, last: )

    Hi,


    I’m really new to VBA, but trying to simplify some of my tasks.


    I’ve got a worksheet with lots of different tabs with price lists for different customers. The price list has different categories.


    So what I’m trying to do is to have a different sheet where I will enter any new product that some or all the price lists need to be updated with.

    I want to insert a new row below a certain product category name and then in column A of the new inserted row add a Product ID.


    So far I have managed to just insert the row using the below code.


    Sub NewRowInsert ()

    Dim SearchText As String

    Dim GCell As Range

    SearchText= “Nintendo Category”

    Set GCell= Cells.Find (SearchText).Offset (1)

    GCell.EntireRow.Insert

    End Sub


    Has anyone got any suggestions on how to enter a product code in the newly inserted row please?


    Thanks