• 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.

Help with adding named range to an external reference

E

ecawilkinson

New Member
Hi,

I have a situation where I have main workbook that reads from workbooks with a different year number in the name. I just cannot seem to get the syntax correct.
What I need is a cell with code something like ="V:\" & Year_No & "\[16F_" & Year_No & "_Summary.xlsx]Final_Page!$A$3. I know this syntax is incorrect and the hardcoded version would be ='V:\CP4\[16F_CP4_Summary.xlsx]Final_Page'!$A3, with CP4 being the company year number. I just need to know the correct syntax that would allow me to insert the named range Year_No into the formula to avoid hard-coding the reference.

Thanks,
Chris
 
I know how to fix it using INDIRECT but my company has a programming culture where it is almost a cardinal sin to use volatile functions ever. I have tried to explain that you can use volatile functions without much cost in terms of processing if you do it right, but it is like a religion to them. But thank you very much for your prompt reply. I will just haver to do a few bench tests to see if using INDIRECT has any drastic effect.

Thanks,
Chris
 
You must log in or register to reply here.
Back
Top