Macros & Formulas Across 4 Workbooks

I have a workbook with four primary worksheets that I need to merge data between. I am a novice at writing formulas and know nothing about macros. I need to do the following (mock spreadsheet attached):

1. When I enter data onto the INVOICES workbook I would like columns C, D, & E to auto-fill using data from the BILLING CODES workbook.
- I wrote VLOOKUP formulas, but Excel is not allowing me to drag them down to populate the cells below. I have to cut and paste in every single cell. There has to be an easier way. The INVOICES workbook could potentially grow to over 500 lines, so I’m thinking that a macro may work better. I want all cells to be empty if there is no data to pull from another workbook.

2. I would also like cells K, L, & M to auto-fill using data from the CLIENT NAMES workbook.

3. I have to track the US and Non-US Budget based on if it is a fee or cost that I’m billing. I have a formula in Column L on the INVOICES workbook that populates with the quarter. Column F on the same workbook indicates if I’m billing a fee (F) or cost (C).

On the BUDGETS workbook, I need to have the “Billed Amt” updated with the amounts in Column G (“Billed Amount”) on the INVOICES workbook based on if it’s a US or Non-US client as noted in Column K. Not only that, but the formula/macro would need to put the amounts into the correct row on the BUDGETS workbook depending if it’s a fee or cost that I’m billing based on Column F on the INVOICES workbook.
Example: Row 2 on the INVOICES workbook is a Fee for $100 on a US client in Quarter 2, so that should be added to cell D5 on the BUDGETS workbook.

The same would need to be done for all Non-US matters with fees and costs on the Non-US Budget.

I was hoping that there was a macro that would allow me to hit the “Update” button for each budget (US and Non-US) that would pull information from the INVOICES workbook based on the data in Columns E, F, K, & L.


I am hoping that all of these things can be accomplished in one worksheet, but need a lot of assistance to get this working. I have reviewed several posts about formulas, but cannot locate detailed instructions on writing the macros that I am needing. Any advice that you can provide is greatly appreciated! I am having the most difficult time on step #3, tracking the individual budgets based on if I’m billing a cost or fee.

Thanks!