I am at my wits end trying to create either a function or a macro to import data from one worksheet to another. I have total sales for a SKU on one worksheet and on another worksheet, I have the same SKU's but in a different order and cannot readjust the order. In the second worksheet I want it to pull total sales for that SKU from the first worksheet and enter it into the cell on the second worksheet that corresponds to that SKU.
For example:
Worksheet 1:
SKU YTD Sales xxxxxx1 4 xxxxxx2 6 xxxxxx3 8 xxxxxx4 9
Worksheet 2: SKU YTD Sales xxxxxx1 this is where I want the data put into from Worksheet 1 xxxxxx4 xxxxxx2 xxxxxx3
I am trying to eliminate would could end up to be days upon days of data entry. Any ideas?
THANKS!
cant you just VLOOKUP and link the other worksheet?
use a sumif on worksheet 2: Assuming that SKU and YTD Sales are A1 and B1: in ws2 in B2, put =SUMIF('Sheet1'!$A$2:$B$5000,$A2,'Sheet1'!$B$2:$B$5000)
have fun edit: above poster is right as well, the only reason i used sumif is to account for the possibility of the sku being listed multiple times on worksheet 1
thanks! I had tried VLOOKUP numerous times without success but your guys' reassuring comments kept me at it and I finally got it to work.
The best way to do this via excel macros is to create a loop with this kind of structure:
If Workbooks(1).Sheets(1).Cells(rowNum, 1) = "SKU1" Then Workbooks(2).Sheets(1).Cells(1, colNum) = Workbooks(1).Sheets(1).Cells(rowNum, 5) End If
rowNum is a variable that changes every time the loop is finished via rowNum = rowNum + 1 at the bottom of the loop.
The way you want to read this is "If the entry in the first column and rowNum row of my first worksheet equals SKU1, then my second worksheet pulls the value 5 columns over from that entry and plugs it into the first column of the first row."
The idea here is that the loop checks every single row for "SKU1" and when it finds SKU1 it takes the information you want. So what you need to do is make a loop that has a statement like the one above for each individual SKU# so that the loop checks every line for every SKU#. colNum may or may not be something you need depending on whether or not you plan on having multiple dates going.
I know this can be confusing but send me a PM if you have anymore questions. This way is better than VLOOKUP once you put in the initial grunt work of understanding what's going on.
^how is that better than vlookup? trust me, never use VBA unless you really have to. the code needs to be CONSTANTLY mantained, and its a black box for others (no one has time to try and understand your code). For something as simple, no way in hell is vba warranted. it will also be slower..
The reason I would use VBA over vlookup is once you have a loop set up, you could keep updating the sheet and the macro would still work. If you simply use the vlookups, you would have to put a ton more vlookups in.
Perferendis distinctio porro non. Perspiciatis est neque voluptas veritatis fugit non voluptas. Nihil voluptatem qui rerum et ut.
Tempore vitae id veritatis. Non doloremque non et aliquid inventore quisquam omnis sint. Et eveniet maxime quas maiores alias.
Expedita sint expedita id omnis in. Voluptas mollitia exercitationem est consequatur autem eum itaque tempora. Labore beatae quas rerum fuga tempora et sunt ullam.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...