Need Help With Dynamic VLOOKUP Target Array

I have a reporting template that is to be used by a number of project managers that refers back to a Excel dump file. Part of the issue is that this dump file is created by a third party software and does not allow for customization. The dump file has to be formatted a certain way. Unfortunately, we can't create a VBA macro and put it into the default template for Excel that it is constantly available as it would then be accessible to everyone that uses this third party service. So the macro that has been created opens and reformats the dump file so that the VLOOKUPs will function properly.

Here's the problem. The macro runs from the reporting template and when the reformatting of the dump file is complete, the array for the VLOOKUP is now altered. The reformatting consists of removing columns and rows, moving other columns around, and converting a column of data from data type to another. In all, 5 rows are deleted, 2 columns deleted and 1 column moved. Now, the number of rows of data can fluctuate. But, the concern at this point is how can I get the data array in the VLOOKUP to not change when the macro has completed running. An example is that the VLOOKUP can look like this: VLOOKUP($A2,'\ExternalFilelocation\ExternalFile.xls'![Sheet1]$A$2:$S$700,6). After the macro runs, the VLOOKUP now looks like this: VLOOKUP($A2,'\ExternalFilelocation\ExternalFile.xls'![Sheet1]$C$7:$U$706,6). It has been altered to accommodate the "change" in the dump file. Yet, the array should remain the same.

Now, part of the macro does identify the bottom-right corner of the array. But I can not seem to get the information to stick in the VLOOKUP array. I have tried Indirect() and Address() where the macro writes to certain cells the column and row numbers of the bottom-right corner of the array. But, I get errors in the formula when I do that.

I really need this VLOOKUP (or something similar) to work on this dump file as the number of rows does change from one day to the next. Therefore, the array in the VLOOKUP can't be necessarily hardcoded. It has to be, in some form or fashion, dynamic like the dump file.

Is there a way to do this?

Rattler0812

I can see where that would be the easiest. But, unfortunately my PM's aren't exactly looking for the easiest solution for me. But they do need the easiest solution for them which is the single click of a button on their report form that gets the dump information, reformats it and then takes them back to their report. All with one click.

So, can someone point me in the not-so-easy way? My PM's really need to just click one button and the macros do all the work. The total number of rows in the dump file will vary from one day to the next. So I am needing some way to lock in that format so that it looks at the dump file but refers to the cell where I have put in the number for the very bottom row.

My current format is:
VLOOKUP($A2,'E:\ExternalFileLocation\[ExternalFile.xls]Sheet1'!$A$2:$S$700,6)

A method that I recently found was to use Indirect() in the following manner:
VLOOKUP($A2,INDIRECT("'E:\ExternalFileLocation\[ExternalFile.xls]Sheet1'!$A$2:$S$" & X1),6)

Where X1 is the cell where the bottom row number exists. I know the range will start in cell A2 and go as far over as column S. So that is something I can hard code because the format will always be the same. But the number of rows is the dynamic part. I already have it in my original macro where the bottom row number is placed in cell X1. But I can't the VLOOKUP or INDIRECT functions to use it.

Rattler0812