How to create a macro for new files sent each month?

[makeitwork09]

I am using Excel 2007.

We are receiving a file each month that has a slightly different name each month and the number of rows can change each month, however, the routines we need to do will be the same each month. The routines include:

(1) in one of the two worksheets create a sumif if formula (note the number of rows changes each month to perhaps none to a few)
(2) all the following steps are in another worksheet, where the number of rows can change each month, in the same workbook starting in the next available column
     * use an index match array formula to get the records from step 1
     * the next two available columns have formulas from other columns in the worksheet

I am only used to creating macros for a workbook that is used over and over again, not for workbooks that we receive from someone else and that the number of rows can change each month.

What is the best way to approach creating macro code to perform the above steps after the workbook is received each month?

Thanks

 

[SkipVought]

hi,

I'm confused. Do the numberr of rows vary in BOTH workbooks?

Please describe the summary sheet in detail.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Hi Skip

Yes they do vary.

The first worksheet I mention is only a subset of the second worksheet. The people who supply the workbook cannot combine the information.

So let's say the main worksheet (call it cutoff), the second one I mentioned in the original post, had 50 loans with loan numbers. The first worksheet (call it extra prin) I mentioned above can have none, the same amount, more or just a subset of the loans as the main worksheet. The difference is the extra prin worksheet has a column that we need to add to the cutoff worksheet. It cannot be a simple cut and paste because of the reasons mentioned here and because the extra prin worksheet can have the same loan numbers more than once. The sumif I mentioned in the extra prin worksheet in my original post sums a value (additional prin) where the loan numbers are the same.

In the cutoff worksheet the loan numbers are unique and we lookup the value to add the next available column in that worksheet by looking up both the loan number and the principal balance in the extra prin worksheet. We do this by using the index and match functions and clicking CTRL-SHIFT-ENTER to make it an array.

The other formulas we add to the cutoff worksheet are simple arithmetic formulas.

I hope you can follow this.

Thanks

 

[SkipVought]

Do ALL the SUMIF formulas have the exact same references: the kind that can be copied from one cell and pasted in ALL the cells using SUMIF?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

I think the answer is yes. What I did was for the worksheet we are calling extra prin here I created a named range for all the rows, except the header row, and columns. I also created a named range for the loan number and for the additional prin column, then in row 2 I created a formula something like the one below (I'm no longer at work so I'm attempting to do this by memory).

sumif(curt_loan,"="&curt_loan,curt_prin)

Then I copied and pasted that to the last row. Since it possible for the loan to be in the extra prin worksheet more than once, that result is repeated. When I do the lookup in the cutoff worksheet I combat that by matching on the loan number and principal balance because those are unique.

In the cutoff worksheet I have a formula much like te one below

{if(isna(index(curt_table,match(A2&F2,curt_loan&curt_prin,0),16), 0,0,index(curt_table,match(A2&F2,curt_loan&curt_prin,0),16))}

Where {} was created with CTRL-SHIFT-ENTER, then I copy and pasted that formula down to the last row in the cutoff worksheet. The end result was that the summed additional prin is in a column in the cutoff worksheet for it's corresponding loan number. This routine needs to be done each month when we receive the file.