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?


--
Anders Salbu
------------------------------------------------------------------------
Anders Salbu's Profile: http://www.excelforum.com/member.php...o&userid=32259
View this thread: http://www.excelforum.com/showthread...hreadid=520075

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!


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=520075

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.


--
Anders Salbu
------------------------------------------------------------------------
Anders Salbu's Profile: http://www.excelforum.com/member.php...o&userid=32259
View this thread: http://www.excelforum.com/showthread...hreadid=520075

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


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=520075

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.


--
Anders Salbu
------------------------------------------------------------------------
Anders Salbu's Profile: http://www.excelforum.com/member.php...o&userid=32259
View this thread: http://www.excelforum.com/showthread...hreadid=520075