Reference to range containing text return #VALUE!

Question

I define a range Users containing user names (text). A simple reference =Users returns #VALUE!. I use Use in Formula on the Formulas tab to insert the reference. According to Inside Out the error message #VALUE! occurs when referencing a cell containing text in a formula that accepts only numerical input.

Is it only possible to reference ranges containing numerical values?

Answer 1

This is normal behavior.

The Named Reference always returns an array of values {"Michael";"George";"Henry"} (as shown in column 2 of the Name Manager).

If the Name happens to be in the same row as the original, it will show the value from that particular row.  If not, it will show a #VALUE! error.

You can confirm that all three names are being returned by selecting the formula in the formula bar, and hitting <F9>

If you want to return a particular member of that array, embed the Name in an INDEX function:

=INDEX(Users,1)

will always return Michael, no matter what row it is on.

Answer 2

The INDEX function i just what i needed. Thanks a lot.

Answer 3

Hi Martin,

Thank you for posting your query in Microsoft Office Community.

As per your query, please provide us a screenshot consisting of the cell information which will be helpful for us to understand the issue. Please click on the below link to post a screenshot:

http://windows.microsoft.com/en-us/windows/take-screen-capture-print-screen#take-screen-capture-print-screen=windows-8

Please post to us if you need any further assistance with Office products.

Thank you.

Hi Sagnik

Thank you for replying.

I have inserted four screenshots

1. The name Users is defined
2. Users is referenced on the same sheet. No problem
3. Users is referenced on a second sheet. The first two cells return #VALUE!. The next three cells shows the range. Apparently the range is moved two cells down.
4. Name Manager shows name, value and reference.

This time i was not able to provoke an error on the same sheet as where the range is defined, but i has happened on earlier occasions.

Because i would want to apply Users on a sheet other then where i is defined, this is a problem. Your help on this matter would be appreciated.

Answer 4

Hi Martin,

Thank you for posting your query in Microsoft Office Community.

As per your query, please provide us a screenshot consisting of the cell information which will be helpful for us to understand the issue. Please click on the below link to post a screenshot:

http://windows.microsoft.com/en-us/windows/take-screen-capture-print-screen#take-screen-capture-print-screen=windows-8

Please post to us if you need any further assistance with Office products.

Thank you.