I am referencing a range on a seperate worksheet that I have defined as "Skills". Skills has (6) columns... the first column is simply an ascending sequence of integers beginning with 1. My formula reads: VLOOKUP(BS11,Skills,3,FALSE). Once again, Skills is a defined range on another worksheet. If I use a similar VLOOKUP formula on the worksheet that the range resides on, I get the answer that I need. Problem is, it won't work on the other worksheet (Pulling my hair out)!!!
2007-02-06 07:20:25 · 2 answers · asked by jerid421 1 in Computers & Internet ➔ Programming & Design
As long as the range named "Skills" is only defined on the separate worksheet, your formula doesn't need to specify the sheet name. The fact that your error message is #NA instead of #Name? means that the formula is recognizing the range named "Skills"
It appears to me that the only reason you can get #NA is that the value in BS11 is not in the 1st col. of the range named Skills. Check the definition of Skills (or select Skills by choosing it in the dropdown box next to the formula bar).
One related possibility. The number in either BS11 or it's corresponding number in the first col. of Skills is formatted as text (in which case it should display as left-justified rather than right-justified).
2007-02-06 11:55:12 · answer #1 · answered by Joliet Jake 3 · 0⤊ 0⤋
You can't just reference Skills as is, you have to also define the worksheet so that it knows where to find it. For example,
vlookup(BS11,Sheet4!Skills,3,FALSE)
2007-02-06 07:56:37 · answer #2 · answered by BigRez 6 · 1⤊ 0⤋