Hi all, when I do a vlookup, the value returned is "#ref" - even though the cells from both sheets are formatted the same. If I put an apostrophe in front of the number being looked up - nothing happens. However, when I remove the apostrophe (back to the way it was) the cell returns the proper value. Any ideas?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Strange Excel Quirk?
- Thread starter Spencer25
- Start date
Dufus
Board Regular
- Joined
- Aug 19, 2006
- Messages
- 176
Spencer25,
If you have taken data from another source and added it to your sheet to be used in a lookup, you can get this behavior. It's caused by the laying on of data on top of a format. The data doesn't necessarily have the same format as the cell underneath. What happens when you change from text (adding ' ) and then back is what needs to happen to all the data. That is hit F2 and then Enter. That gets the data into the cell in the format of the underlying cell. To do this for a lot of data, you want a shortcut. You want to copy a blank cell and Paste Special, Add to the entire range of data. This will add zero to all the cells in much the same way as hitting F2 and then Enter.
Dufus
If you have taken data from another source and added it to your sheet to be used in a lookup, you can get this behavior. It's caused by the laying on of data on top of a format. The data doesn't necessarily have the same format as the cell underneath. What happens when you change from text (adding ' ) and then back is what needs to happen to all the data. That is hit F2 and then Enter. That gets the data into the cell in the format of the underlying cell. To do this for a lot of data, you want a shortcut. You want to copy a blank cell and Paste Special, Add to the entire range of data. This will add zero to all the cells in much the same way as hitting F2 and then Enter.
Dufus
Upvote
0
Similar threads
