CODEWITHSUNDEEP
excelexcel-formula
Tim Edwards
Tim Edwards

Reputation: 1028

Excel =Column() giving #NAME? error

=COLUMN()

This was working fine yesterday but today it gives me #NAME? as the result rather than the column that the formula is in.

ColumnError

What is going on today that's different from yesterday?

Edit: Even more bizarrely if I enter the =COLUMN() using the fx (insert function) button then it works fine.

Upvotes: 1

Views: 104

Answers (1)

Ralph
Ralph

Reputation: 9444

Thank you for copying over the formula from your Excel here. Otherwise I never would have found it.

The problem is that you have some non-standard characters in your formula which do not show. Doing a 1:1 comparison of your formula with a formula I have written myself into Excel using AscW() I came up with the following result:

Character     Letter        AscW(yours)   AscW(mine)
 1            =              61            61 
 2            C              67            67 
 3            ?              8204          79 
 4            ?              8203          76 
 5            O              79            85 
 6            L              76            77 
 7            U              85            78 
 8            M              77            40 
 9            N              78            41 
10            (              40            
11            )              41

As you can see, your formula is by two letters longer. There is a AscW(8204) and a AscW(8203) between the C and the O of the word Column. These characters do not show. Yet, they are there.

Also note, that this is not the case everywhere in the Column() you have posted in the above question. The first =COLUMN() at the top of your post is fine and works normally. Furthermore, the first Column in this formula 

="Sheet1!"&ADDRESS(IF(ROW()-3<1,1,ROW()-3),IF(COLUMN()-3<1,1,C‌​OLUMN()-3))&":"‌​&ADDRESS(ROW()+3,COLUMN()+3)

is fine. Merely the second column in this formula contains these extra characters. So, I'd like to suggest that you re-type the formula (manually) without copying it from anywhere and you should be fine.

Upvotes: 2

Related Questions