Formatting a cell: avoiding f2 & enter combination

G

Guest

Guest
Should be easy to fix: I'm doing a vlookup off two separate sheets that works totally fine when I get the formatting on both databases the same and after i hit "f2" and then the "enter" key. i have to do this for over 16K cells (rows) and it is laborious. is there a simple formula/cell conditioning excercise to make both sheets talk with each other?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
On 2002-02-28 11:36, Anonymous wrote:
Should be easy to fix: I'm doing a vlookup off two separate sheets that works totally fine when I get the formatting on both databases the same and after i hit "f2" and then the "enter" key. i have to do this for over 16K cells (rows) and it is laborious. is there a simple formula/cell conditioning excercise to make both sheets talk with each other?

If you are mixing numbers and text numbers that mix-match, you could either do a quick text to columns over both keys or edit your formala to something like
=vlookup(a1+0,table,2,0)
or
=vlookup(text(a1,"00"),table,2,0)

good luck
This message was edited by IML on 2002-02-28 11:43
 
Upvote 0
Thanks for the tip, but that isn't exactly my problem. I should have been more clear. I have two sheet of zip codes that i am trying to vlookup off of and even though the two sheets are in the same format, the vlookup doesn't recognize them. only after i hit "f2" and then "enter" am i able to get the desired result. HELP! I have over 16K zip codes that I need to query off of in the vlookup.
 
Upvote 0
Sorry if I misunderstood. A quick way to verify this is not the cause of your problem is to put to use the =isnumber() formula on each file. If one it returning true, and the other false, this would be the cause of your problem.


On 2002-02-28 11:49, Anonymous wrote:
Thanks for the tip, but that isn't exactly my problem. I should have been more clear. I have two sheet of zip codes that i am trying to vlookup off of and even though the two sheets are in the same format, the vlookup doesn't recognize them. only after i hit "f2" and then "enter" am i able to get the desired result. HELP! I have over 16K zip codes that I need to query off of in the vlookup.
 
Upvote 0
You are exactly correct. when I ran "isnumber", those that match are true and those that don't are false. Now that I have identified cells which are recognized as numbers and those that aren't, how do i make them all consistent (into numbers)?
 
Upvote 0
On 2002-02-28 13:18, Anonymous wrote:
You are exactly correct. when I ran "isnumber", those that match are true and those that don't are false. Now that I have identified cells which are recognized as numbers and those that aren't, how do i make them all consistent (into numbers)?

If it is static data, the easiest thing to do would be to turn your text numbers into real numbers by either
1) Selecting the data, select text to columns from the data menu, and hit finish
or
2) Put "1" in an unused cell. Copy it. Highlight the text numbers and paste speical multiply.

If the data is dynamic, play with two formula aboves to find the one will match the two data types correctly good luck
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top