• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Named ranges, table arrays and vlookup

Costas

Costas

Member
Hi,

I've set up a vlookup into a Jet report that updates an excel table through a link to a separate workbook. It is working fine but when I refresh my jet report it effectively deletes the excel table and recreates it. If both workbooks are open during the refresh (and they need to be) then the link is broken because the table array disappears, so even when the recreated table array is there, the link is gone.
I tried to keep the link to the table array as the contents of a named range cell so the link would be unaffected from the refresh but I can't get my vlookup to work. I tried the indirect function but no joy either. Below is an explanation of what happened.

Before the refresh
=vlookup(A2,'Data.xlsx'!GLAccount[#Data],5,false)

After the refresh
=vlookup(A2,'Data.xlsx'!#Ref!,5,false)

Failed attempt to fix
Created a named range called "MyData" for cell Z1 with contents 'Data.xlsx'!GLAccount[#Data] so the revised formula is =vlookup(A2,MyData,5,false).

Please note that I've asked this question in another forum but have not received an answer yet. Can anybody help me with this please?

Thanks
Costas
 
Hi Costas

I recreated your file with a different offset and the lookup seemed to work OK.

=VLOOKUP(A2,Data.xlsx!Table1[#Data],5,0)

The only thing I see that is different structurally is the name of the table seems missing and the tick marks ' ' in the File name. As the file is one word I don't think you need them. As a hard fast rule I never link workbooks together.

Can you provide the link to the other site so if it is solved we can be informed of that too.

Take care

Smallman
 
Hi Costas

Thanks for posting back. I am glad you sorted it. Keeping files to one word even if you join many words together is another good rule to follow. I am so pleased you sorted it.

Take care

Smallman
 
Hi again Smallman,

I now have a problem related to the above. My links break when the target file closes or is closed. I tried to change my named range cell to include the full path so MyData now is 'L:\Finance\Buds_Fores\TB_Mov.xlsx'!GLAccount[#Data] but it doesn't work with or without the tick marks.

Do I need to keep my file open all the time?
 
You must log in or register to reply here.
Back
Top