Posts by jpetraityte


    Is it possible to have an if statement and if the statement is false then have a data validation list in the same cell?

    I have a form where the user needs to select a product - each product has a code and description.

    Some users prefer to choose the product by code and others by description - but both need to appear on the form - I can't have the validation lists for both of them as then the user might select the description which does not match the code.

    So, if the user chooses to select the product by Code, then I was hoping to do an if statement in the cell where the description should be - if the cell where the code is supposed to be is not blank then do a vlookup for the description, but if the cell of the code is blank then have the data validation list with the below options - and the other way round.

    Code Description

    SKU1 Apple

    SKU2 Pear

    SKU3 Banana

    SKU4 Grape


    Thanks for your advice Rory.

    I have changed the form completely as attached.

    Is there a shorter code for the validation part of the code? I rather liked the short code for this in my previous version, but I couldn't figure it out how to adjust that part of code to work with this new form.



    hi, I have created the attached VBA form with the help from this forum and its been put into use.

    The form is centrally shared and is being used a number of users. However, few of the users have Apple MAC and it doesn't seem to be working for them.

    Is there any way that the code could be adjusted to work with MACs as well please?


    • Form VBA.xlsm

      (2.22 MB, downloaded 16 times, last: )


    I have "Invoices" table with the values in one currency (GBP) and I have created another table "Currency".

    I was wondering if there is a way using Power Pivot to have a currency slicer, so that if USD or GBP is selected - it converts the "Amount" using the exchange rates in the "Currency" table?

    I have attached my sample data and thanks in advance :)

    hi Roy,

    I have attempted to create PowerPivot, however not sure why the budget figures are not updating properly just showing the total amount on each line.

    I think I have created a relationship between the different tables but still don't seem to work. Could you please have a look at the updated Workbook - Sheet "New Dashboard" and advise what I am doing incorrectly?



    hi Roy,

    I have managed to get the total sum using the below formulas, but they only work when no slicers have been selected.

    Is it possible in the offset function instead of where I have "14", to make it count the number of cells between $A$16 to "Grand Total"?

    =IFERROR(IF($A30="Grand Total",SUM($H$16:$H$29),VLOOKUP(E30,Budget!$A:$I,9,FALSE)),0)

    =IFERROR(IF($A30="Grand Total",SUM(OFFSET($A$16,0,7,14)),VLOOKUP($E30,Budget!$A:$I,9,FALSE)),"")

    How do I make budget formulas dynamic? - sorry it is all a bit new to me :)

    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.


    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 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 :)


    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.


    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?