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