Strange Excel Quirk?

Spencer25

Board Regular
Joined
May 3, 2006
Messages
76
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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
 

Forum statistics

Threads
1,175,987
Messages
5,900,740
Members
434,848
Latest member
kubersood321

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top