Posts by jpetraityte

    Hi,


    I have three tabs in my file with the same headings, but the "Amount" represent different things - Current Year Actual/Budget/PY figures. I was trying to combine them into one using Power Query (using "Combined" column) - so that I have a table with all the same heading plus a different column for each "Amount", so I could then later do variance analysis using Pivot Table (Summary Tab).


    For whatever reason it didn't want to work, would anyone in this forum have any suggestions how this could be achieved? Either Power Query or any other method would be much appreciated.


    Thanks,

    Jurate

    Hi


    I have created three power queries to have a different view of the same information, but for whatever reason, any new data entered in either of the Amazon tabs gets only updated in one power query which I have created last - please see attached file.


    I am new to the power queries and not sure if it's possible to have multiple power queries from the same data source? I would be grateful if you could review my power queries and let me know what might have caused this issue.


    Also, not sure if this should go on a separate thread, but I have researched that if you have the below VBA code in the data source tabs, power queries should automatically update themselves, but I find that it is very slow. Any thoughts on how to make the refreshing faster?



    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. ThisWorkbook.RefreshAll
    3. End Sub



    Thanks,


    Jurate

    Hi


    I have a workbook with the price lists for various customers and I am trying to copy into a different workbook certain tabs only. I was able to separate by using the below code, but I also want to paste as values only. Please could you help me to adjust the below code?



    Thanks,

    Jurate

    Hi


    Could anyone please help me to update my existing VBA code so that the row with the new product gets formatted in the same way as row above and all the formulas are copied too.


    I thought because I have my price lists data converted into a table it should automatically do that for me anyway, but it does not seem to be the case.


    Please see my file attached, if you run a macro to add Product 11 to each price list - you should see what I mean :)


    Thanks,

    Jurate

    Hi Alan,


    This is great! Thank you so much for the solution.


    The only thing I forgot to mention, sometimes there will be "IN TRANSIT" instead of the actual date in the delivery date column. I noticed that then the power query returns blank rows.


    Is there an easy fix for this? If not, not to worry I can still perfectly work with the file.


    Thanks,

    Jurate

    Hi,


    I am just wondering if anyone in this forum could help me with my issue.

    On a weekly basis, I receive a report from a supplier ("Report" tab), where they state the delivery dates and various PO numbers in Column E.

    I then use delimited option to separate each PO individually ("Report (2)" tab).

    I have a different report from our internal system with the same PO numbers for which I need to find out the delivery date using the report received from supplier.

    Is it possible to lookup a value which potentially could be in any column between E & Y, but still return the result located in column Z.


    Until now I have been doing multiple VLOOKUP's, but it's rather time consuming so I was hoping that maybe there is an advanced VLOOKUP formula which can find out what I need in one go.

    Any other suggestions would also be much appreciated.


    Thanks,

    Jurate

    Hi,


    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

    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.



    Thanks,

    Jurate

    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?

    Files

    • Form VBA.xlsm

      (2.22 MB, downloaded 77 times, last: )

    Hi,


    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?


    Thanks

    Files

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