Posts by jpetraityte

Thank you so much for your help it works perfectly

Hi

In my data sample I have 15 customers and 10 products ( in real life a lot more and it keeps changing all the time). What I am trying to achieve in Column B is to have a formula which repeats the same customer number for the number of rows there are products (15) and then increase the customer by 1 and repeat again for the same number of rows. Repeat the same process until customer number is 15 and then restart everything again from customer number 1. Effectively repeat the same process 12 times which represents 12 months.

So far, I have managed to come up with the below formula, but not sure how to improve it so that it restarts after customer number 15 is reached instead of keep increasing by 1.

Colum G in the attached is the way I would want the formula to work, so any help is much appreciated

=INT((ROW(B1)-1)/10+1)

Thanks,

Jurate

Hi,

I am struggling to come up with a formula to calculate total cost of goods value based on qty available in different locations and taking into account orders already taken for the same product, so I am hoping someone in this forum could help me out

I am attaching sample data - "Cost of Goods" column D is qty ordered and the "Unit Rate" tab are various rates I would like to use. So firstly I would like to consider rates in the warehouse, then in transit and once no stock is available in these locations use the latest rate card to place new orders.

Thanks in advance.

Hi,

In the attached file I am trying to allocate stock provision to the full stock list, but got an issue where we have same stock item but in a different location - I can't get rid of the location and the provision does not have location allocated to it.

It doesn't really matter to which location the provision is allocated as long as the full amount of it is allocated.

So far, I got to the below formula, but this does not take into account any provision allocated in any of the above rows. In this case issue on Item 1 & Item 5.

Can someone please help me to improve the below formula to take into account the amounts already allocated in the rows above?

=IFERROR(IF(VLOOKUP(A2,'Stock Provision'!$A:$B,2,0)=D2,D2,IF(VLOOKUP(A2,'Stock Provision'!$A:$B,2,0)<D2,(VLOOKUP(A2,'Stock Provision'!$A:$B,2,0)),IF(VLOOKUP(A2,'Stock Provision'!$A:$B,2,0)>D2,D2,0))),0)

Thanks,

Jurate

Hi Alan,

Would you be so kind to show it on my sample data? I have tried it, but was not sure how to do it. "Combination" table only has one field "Amount" to choose from - Actual/Budget/PY does not come as separate fields in Power Pivot either?

Thanks,

Jurate

Thanks Carim,

This is very useful, but I was hoping then to be able to have a calculated field to get the variances, for example "Actual VS Budget"= Actual -Budget

Do you know any way around that please?

Thanks,

Jurate

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?

Private Sub Worksheet_Change(ByVal Target As Range)

    ThisWorkbook.RefreshAll
    
End Sub

Thanks,

Jurate

Hi Roy,

Apologies, I usually attach the file that I am working on and was rushing late last night Going forward I will make sure that I have wrapped the VBA code in code tags.

I have tried copying your code to my attached file but it didn't seem to want to work.

Please could you have another look?

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?

Sub UK()


Sheets(Array("Argos", "Sainsbury", "Tesco", "Game", "Smyths", "DSG", "GEM")).Copy




ActiveWorkbook.SaveAs Filename:="Price File-UK" & ".xslx"


End Sub

Thanks,

Jurate

This worked perfectly. Thank you!

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

This is perfect, thanks so much for all your help.

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,

I have two power pivot tables one below another (2 rows between them) and they are both connected to the same slicer options.

So I was wondering if there is a way to maintain the two row gap between the power pivots even when the various slicer selections are made?

Thanks,

Jurate

Thank you Roy.

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?