Error when applying name range in formula

[skyking]

Can someone point out if this is Excel 2010 limitation or other reason?

I have the working formula as below:

=VLOOKUP(CONCATENATE(B1," ",C1),'C:\Users\Documents\C&N\WorkFlow\[WorkFlowMgt65.xlsb]Wages & Bonuses'!$D$14:$T$50,17,FALSE)

However, when I replace the named range "[WorkFlowMgt65.xlsb]Wages & Bonuses'!$D$14:$T$50" with its name as below:

=VLOOKUP(CONCATENATE(B1," ",C1),'C:\Users\Documents\C&N\WorkFlow\[WorkFlowMgt65.xlsb]Wages & Bonuses'!Employee,17,FALSE)
and get "#REF!".

 

[anandvarma]

that is because of space in the named range,
in the named range spaces are replace with _

try with substitute function to replace spaces with _

 

[skyking]

anandvarma,

Sorry, it is not as "Wages & Bonuses" is the worksheet but not a range.

The named range is $D$14:$T$50 in worksheet "Wages & Bonuses" in workbook "WorkFlowMgt65.xlsb"

 

[anandvarma]

take a look at the following function in Morefunc at excel addin

indirect.exe
you can refer a closed work book cell reference

 

[mikerickson]

Is the name Employee defined in the workbook that holds the formula? or is it a name in WorkFlowMgt65.xlsb?

 

[skyking]

mikerickson,

Thanks for your attention.
Employee ($D$14:$T$50) is defined as a name range in worksheet "Wages & Bonuses" in workbook "WorkFlowMgt65.xlsb".

 

[mikerickson]

If you have WorkFlowMgt65.xlsb open when you make the replacement, it should work.

 

[skyking]

<table id="post2799483" class="tborder" align="center" border="0" cellpadding="6" cellspacing="0" width="100%"><tbody><tr valign="top"><td class="alt1" id="td_post_2799483" style="border-right: 1px solid #FFFFFF">It seems not working!

**** WorkFlowMgt65.xlsb is open ****

=VLOOKUP(CONCATENATE(B1," ",C1),'C:\Users\Documents\C&N\WorkFlow\[WorkFlowMgt65.xlsb]Wages & Bonuses'!$D$14:$T$50,17,FALSE)

After I replace the named range $D$14:$T$50 with Employee as below:

=VLOOKUP(CONCATENATE(B1," ",C1),'C:\Users\Documents\C&N\WorkFlow\[WorkFlowMgt65.xlsb]Wages & Bonuses'!Employee,17,FALSE)

It changes to
=VLOOKUP(CONCATENATE(B1," ",C1),'C:\Users\Documents\C&N\WorkFlow\[WorkFlowMgt65.xlsb]!Employee,17,FALSE)
and get "#REF!".
</td> </tr> <tr><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr> <td class="alt2" style="border: 1px solid #FFFFFF; border-top: 0px">

</td> <td class="alt1" style="border: 1px solid #FFFFFF; border-left: 0px; border-top: 0px" align="right"> </td></tr></tbody></table>