Replace string crashes Excel

[nickmax1]

I have a formula in almost every sheet of my workbook in the range L18:N190 (so thats 522 of the below formulas) Now I have currently have 29 sheets with these 522 formulas in them and I need to change the below filename from Sublog.xlsx to Sublog.xlsm (as it is now a macro enabled file) but using the bog standard CRTL + H method crashes excel ( i am assuming a memory issue)

Is there any way to write a macro that could do this more efficiently?

=IF(OR($F18="New",$A18=""),"",IF(ISERROR(VLOOKUP($AI18,[Sublog.xlsx]Page1_1!$A$3:$AN$8000,38,FALSE)),"",VLOOKUP($AI18,[Sublog.xlsx]Page1_1!$A$3:$AN$8000,38,FALSE)))

[buran]

Hi, try to edit links in the workbook i.e. Data tab->Connections group->Edit Links

[nickmax1]

still takes forever and a day! and now it has crashed...

also I may also want to be able to use the macro to change other things on a workbook scale, not just links but also cell refs etc.

[Izandol]

I think this formula is not so efficient. Also, I do not understand why you have this in 3 columns when all column references in the formula are fixed?

You may try:

=IF(OR($F18="New",$A18=""),"",IFERROR(INDEX([Sublog.xlsm]Page1_1!$AL$3:$AL$8000,MATCH($AI18,[Sublog.xlsm]Page1_1!$A$3:$A$8000,0)),""))

If you do return different columns from the Sublog workbook, it will be better to put the MATCH formula into its own column.

[nickmax1]

The only difference in my formulas for each colomn is the colomn they are Vlookuping in the Sublog. So the formula in colomn L looks up COL 38, M looks up COL 33 and N COL 35

I am not familiar with using the match formula....how does it differ from mine?

[Izandol]

Your formulas refer to almost 320,000 cells in a different workbook but only need 16,000.

It will be better to use a separate column for MATCH - let us assume column O:
O18: =IF(OR($F18="New",$A18=""),"",IFERROR(MATCH($AI18,[Sublog.xlsm]Page1_1!$A$3:$A$8000,0),""))

then you may use:
L18: =IF($O18="","",INDEX([Sublog.xlsm]Page1_1!$AL$3:$AL$8000,$O18))
M18: =IF($O18="","",INDEX([Sublog.xlsm]Page1_1!$AG$3:$AG$8000,$O18))
N18: =IF($O18="","",INDEX([Sublog.xlsm]Page1_1!$AI$3:$AI$8000,$O18))

[nickmax1]

A little confused by your post above. Not to say your formula doesnt work but I do not understand it. What is your first formula returning exactly (the match one). As I read it, it says if Col F is New, A is blank do nothing - else match AI18 with Col A of the sublog and then returning what...? The formula currently returns a row reference that is CLOSE to but not quite the row it should be matching, or is that just a coincidence?

[nickmax1]

ah i see why the rows arent coming back right. My data starts at row 6 in the sublog...

[Izandol]

It does not return row number - it returns position within rows 3 to 8000 where match is made. So if match is in row 3, formula will return position 1.

[nickmax1]

OK i see. thanks, i have used your original formula, my spreadsheet is so complicated already with many other workbooks relying on my structure to remain intact. But by using your first match formula you have reduced the size of my workbook by 40%!

[Izandol]

You are welcome.