Periodically adding additional text to current formula

[HCornelian1]

Hi,

I am currently running a vlookup indirect / match formula which retrieves information from several external worksheets, but I don't know how to automatically update the formula to account for any new external worksheets?

Ideally, I need to write a macro which can add additional lines of text to the current formula to automatically account for the new external worksheets, rather than having to update the formula manually.

Could someone please help me with this? My Excel skills are basic and I don't know where to start. Any help would be very much appreciated!

Thanks

[kev_]

Welcome to the forum

Please post your formula

What is its cell reference?
Is it then copied down from there to the last row in that column?

[HCornelian1]

Thanks for replying so quickly!

The formula is as below:

=IF(AND($N$19=$L$37,$K$18=$J$35),VLOOKUP($N$19,INDIRECT("'["&$P$35&"]"&$L$43&"'!"&$L$45),MATCH($M20,INDIRECT("'["&$P$35&"]"&$L$43&"'!"&$L$46),0),FALSE)....

The formula manually repeats itself numerous times, but only the references to cells J and P change, so the next instance would be the same but with reference to J36 and P36 and so on.

However, There is a significant number of rows below this and I don't have the capacity to update the formula manually, so I am looking for a formula / macro to automatically add the next cell down ie. J37 and P37 without removing and of the current formula as I will still need access to historical data.

Is there a possible way to do this?

Thanks very much

[kev_]

Late EDIT
just spotted that I forgot to alter the text I pasted in these 2 lines

Please Login or Register  to view this content.

Should of course be cell containing:
"$L$45" (VlookUp range)
"$L$46"(Match range)
The macro works fine - just the message text is wrong
--------------------------------------

see attached workbook
Run the macro with {CTRL} t

Running the macro
The macro asks the user for 4 new references and replaces the following cell references with ones appropriate to the additional workbook:
"$P$35" (Workbook name)
"$L$43" (SheetName)
"$L$45" (VlookUp range)
"$L$46"(Match range)
- when inputting you must include the $ signs (it is searching and replacing the string)
- upper or lower case is fine (I have wrapped the input boxes in UCase but it was not strictly necessary - Excel would have changed them itself)

Results appear A1 to B2
Cell B1 has been named "ModelAddOn" - this already contains the segment formula
Cell B2 has been named "NewAddOn" - this will contain the amended segment
Cells A1 and A2 will contain the formula text so that you can compare the 2 strings

Please Login or Register  to view this content.

Completing the job

You only provided a segment of the formula, telling me that you are confident in finishing this off.
To generate a formula that VBA and Excel would treat as such, I added a ")" at the back end of the segment
2 variables have been declared in anticipation: oldFormula & newFormula

If what I have done is correct you will need to concatenate the latest formula with the segment (probably removing my extra bracket and adding it at the back end of the total string)
If you need further help let me know.

To build up the string it was much easier to put it originally in Excel, drag it into VBA and then manipulate it - you should do the same with the latest formula