Auto Updating Formula

[Anders Salbu]

I'll start this thread with mentioning that I am from Norway, so sorry for my english, but I hope you'll understand what i mean.

I have createt a formula that gets information from another .xls document and the formula is like this:

VLOOKUP(B7;[konverteringsliste.xls]Sheet1!$A$1:$B$769;2;FALSE)

By now, the last cell in konverteringsliste.xls is B769. But it may be updated with more cells, and thats where the problems start. I want the VLOOKUP function to search through the whole excel document. If the list gets updated, the last cell may be B844, and then, my old formula stills searches from A1:B769. Is there any command, formula or function i can do, that will automatically search through the whole excel workbook?

[Gary Brown]

Type:-

VLOOKUP(B7;[konverteringsliste.xls]Sheet1!$A:$B;2;FALSE)

Then you 65,536 entries before you have a problem. This is not necessarily the best solution, but certainly the easiest!

[Anders Salbu]

Thank You..

But is there anyway that i can replace all the old formulas with the new one, instead of doing the whole work over again? I've kind of did the formula typing about 100 times in different worksheets.

If i can get to the formulas instead of the results, and then choose replace, it would be the easiest solution i think.

[Gary Brown]

If you highlight Sheet1!$A$1:$B$769 in VLOOKUP(B7;[konverteringsliste.xls]Sheet1!$A$1:$B$769;2;FALSE), copy it (Ctrl+C).

Open the spreadsheets which have the formula and click on Ctrl+H, paste (Ctrl+V) into Find What, then move to Replace with, again Ctrl+V and edit out the 1 and 769 and click on Replace All.

Move to the next spreadsheet and press Ctrl+H, the values entered before should still be there, so simply click on Replace All, you will need to do this 100 times, but it shouldn't take too long, you could write a macro, but by the time you have wrtten the macro, you would have completed the above.

Gary

[Anders Salbu]

Thanks for the help. I did manage to get it work, by highlighting/selecting all sheets in the bottom. Then use the replace function, so finally, i had to do it four times because i have four different formulas.