Formatting a cell: avoiding f2 & enter combination
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Formatting a cell: avoiding f2 & enter combination

  1. #1
    Guest

    Default

     
    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?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Guest

    Default

    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.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Guest

    Default

    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)?

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com