Vlookup cell limit?

[mljohn]

I have data in a sheet in Excel 2007 that I am trying to move to another sheet in the same workbook.

I am using Vlookup to move the info from one sheet to the other.

However it seems that if the data in the original sheet cell is too large I get a #VALUE! error.

If I copy and paste the info from the cell it is fine.

How do I copy the information (hyperlink) in the original sheet cell to the destination sheet cell?

Here is the formula that I am using.

=HYPERLINK(VLOOKUP(A67,'verified1-4-12'!B:E,4,0)) verified1-4-12 is the original sheet name


Thanks

Matt

 

[shg]

What's in col E?

 

[mljohn]

A hyperlink address to a website.

I use it to update prices.

Matt

 

[shg]

Not a rigorous test, but I put 1 in B1000000, http://www.mrexcel.com in E1000000, and the formula and the hyperlink formula worked fine.

 

[mljohn]

OK?

In my original sheet the hyperlink is all there but when I use vlookup to move it to the other sheet the long links don't work.

any thoughts?

Maybe you know of another way other than Vlookup to copy the links to the other sheet.

Matt

 

[shg]

the long links don't work.

Ah, I read your question as the length of the table was a problem, not the length of the URL.

How long are they?

Use tinyurls?

 

[mljohn]

All of the links that Vlookup doesn't work with (returns a #VALUE! error) are all the links that are over 255 characters.

Thanks

Matt

 

[shg]

Yup, HYPERLINK is limited to 255 characters.

How about tinyurls?

 

[shg]

Someone here could surely help in automating generation of tinyurls for your long urls. You could keep both in the table, in case the tinyurl breaks.