Transfering data between workbooks

[neokeelo]

Hello. I am a beginner in Excel. I can edit formulas and doing basic work but this project is beyond me. I will explain it and keep it simple. I need to know the best way to approach this be it Macros, VBA code or whatever.

There are 3 workbooks. PlannedVol.xls Caculate.xls and Final.xls The process is as follows.

Step 1.In the Final.xls workbook I choose the plant location and the month from a drop down box. The parts that are corresponding to the month chosen and the plant location in PlannedVol.xls are then transferred to Calculate.xls.

Step 2. In Final.xls, the user selects the list supplies needed for those parts, those are then transferred to Calculate.xls

Step 3 The totals are calculated and sent back to Final.xls.

So you can see I am mostly needing to send and receive ranges of data between different workbooks. If it is easier, I can combine Calculate.xls and final.xls into one and just make them separate worksheets. What is the best way to start this project?
Thanks for the advice.

[pb71]

Can you upload some example workbooks?

[neokeelo]

ok I posted a small example Excel document.

Basically in sheet 2, I want the user to select the Plant and month from 2 drop down box lists (A2 and B2). Then the program should look in Sheet 1 columns D through C and copy the correct part number and it corresponding volume for that month.

So for example, If I select Findlay, March.

The program should return the part number 123BBB and a volume of 2156 in sheet 2.

[pb71]

neokeelo,

I've attached a workbook for you to look at. It transfers the data from Sheet 1 when you change the values in the drop down boxes on Sheet 2.

See attached.

In case your not familiar with VBA:

Select Tools - Macros - Visual Basic Editor (or hit Alt+F11 to open the Visual Basic Editor).

[neokeelo]

Thanks a bunch! This is exactly what I needed for my first step, I will study the code and try to replicated it.

[pb71]

neokeelo,

I've amended the VBA and attached a new workbook. This takes the Sheet 2 table being empty into account and stops the header row from being deleted.

[neokeelo]

Thanks but since I am not yet proficient enough with VBA o edit the code you wrote and apply it for my purpose, is there any way to do this same thing using a VLOOKUP inside an if statement?

[pb71]

I've attached an example (using your original workbook) that doesn't use VBA. It uses an array formula and the functions INDEX, HLOOKUP and MATCH.

It wouldn't be very efficient, however, if you had a very large number of records.

[neokeelo]

Pb71, thank you for your help with this project. I tried to edit your code but in the end I copied our data into the worksheet you wrote and was able to make it work by changing just a few factors. This helped me learn VBA some so thanks.

[pb71]

neokeelo,
Great that you got it working. I've added the link to the related thread where the VBA is commented for reference (which also includes one amendment to the VBA).

http://www.excelforum.com/excel-gene...this-code.html