Error when copying a formula

[nandp12]

Hi,

Hopefully this is an easy one. I'm trying to copy an ELOOKUP formula down a column in a sheet. The formula is losing part of the table array when I carry it down and returning an N/A error. Interestingly, it will work when I copy it across a row.

what am i doing wrong??

thank you so much!

 

[VoG]

Try using absolute addresses for the table, e.g.

=vlookup(a1,$b$1:$e$100,5,false)

 

[nandp12]

I tried absolute addresses and still no luck...anything else?

Thanks for replying!

 

[VoG]

Try posting your formula.

 

[nandp12]

=ELOOKUP(A4,'F:\EXCELWRK\TOM\DOW TEST\[mstar1test.xls]1 100'!$1:$65536,4,0,ELOOKUP(A4,'F:\EXCELWRK\TOM\DOW TEST\[mstar1test.xls]101 200'!$1:$65536,4,0,ELOOKUP(A4,'F:\EXCELWRK\TOM\DOW TEST\[mstar1test.xls]201 300'!$1:$65536,4,0,ELOOKUP(A4,'F:\EXCELWRK\TOM\DOW TEST\[mstar1test.xls]301 400'!$1:$65536,4,0,ELOOKUP(A4,'F:\EXCELWRK\TOM\DOW TEST\[mstar1test.xls]401 500'!$1:$65536,4,0,ELOOKUP(A4,'F:\EXCELWRK\TOM\DOW TEST\[mstar1test.xls]501 600'!$1:$65536,4,"VALUE NOT AVAILABLE"))))))

Okay, here it is! The part that is not copying is $1:$65536 - it's saying #REF!

thanks, again!!

 

[Jonmo1]

Yep, pretty hard to troubleshoot someting we can't see...


Hey Vog, you'll probably get a #REF Error with that formula...

 

[VoG]

Hey Vog, you'll probably get a #REF Error with that formula...

Not probably ... definitely

 

[nandp12]

sorry, I'm still learning! Okay, I posted it so hopefully I did that correctly. Thanks everyone!

 

[Jonmo1]

OK, I think we both thought it was a typo when you said ELOOKUP. We figured you meant VLOOKUP.

There is no built in function called ELOOKUP. It must be a custom function created in VBA.

Nonetheless, with the $ in there, it should lock the cell refs.

Can you explain EXACTLY step by step in detail..
EXACTLY What happens, and EXACTLY what steps are you following to Copy the formula and produce the error?

 

[VoG]

Maybe it is this http://www.mrexcel.com/forum/showthread.php?t=20443