MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 370|Reply: 8

SOLVED IF Function Question maybe -Autofilling account numbers to...

[Copy link]

1

Topics

3

Posts

9

Integral

Newbie

Rank: 1

Integral
9
Post on 9-14-2016 18:33:59 | All posts |Read mode

NameDate         AccountProductStatus
Ferreira1/11/2016AquidneckBombayPending
Ferreira1/11/2016CapitalJager counter freezerPending
Ferreira1/11/2016City LiqHarpoon Cold BoxPending
Robinson1/11/2016HeritageMezzacoronacomplete
Robinson1/12/2016HeritageMiller Litecomplete
King        1/11/2016ExeterHarpoon Cold BoxPendingThis is an example of the document I work with everyday. It is a booking sheet for my display team. My boss has asked me to include account numbers next to each account name. I do have a master excel list with all account names and corresponding account numbers. Is there a way that I can save all of the account names and numbers in excel's memory, so that I when I start typing an account name, the account number will autofill to the next column. I do not want to have to manually look up each account number and type it in. Does that make any kind of sense? Any help would be much appreciated. Thanks so much.






Last edited by smb311; 01-19-2016 at 02:45 PM.

Reason: SOLVED








Reply

Props Report

14

Topics

436

Posts

1026

Integral

King

Rank: 6Rank: 6

Integral
1026
Post on 9-14-2016 20:06:25 | All posts



You can keep the account numbers and names in another worksheet, then use a VLOOKUP formula to automatically fill in the account number if you type in a valid name. It will not do the lookup until you have typed the entire name and hit ENTER (not when you start typing).
So let's say you have a worksheet called Account Numbers where column A has the list of account names and column B has the corresponding account numbers. In the sheet above, starting in row 2, you can use this formula:


Formula:





  



      =VLOOKUP(A2,'Account Numbers'!A:B,2,FALSE)
      
      

to provide the account number corresponding to the name in column A. Note that the names must be unique!











Reply Support Opposition

Props Report

1

Topics

3

Posts

9

Integral

Newbie

Rank: 1

Integral
9
 Author| Post on 9-14-2016 20:36:15 | All posts



Jeff, thank you so much. I actually got it to work, although it seems to be cap sensitive/font sensitive (I also triple checked my spelling to make sure the account names in both documents were an exact match). The formula only seems to work if I go into my Account Names worksheet and copy and paste the account name into my other worksheet. Even when I use the same exact font, type size as the Account Names worksheet, it still doesn't work, it has to be copy and pasted. I have attached the two documents, if you'd like to take a look. Perhaps, I should just start each document over, using the same font, size, capsize. Or perhaps, there is some kind of hidden setting that I need to turn off/turn on. Thanks again.








Attached Files


  • Display Bookings Jan 2016 Experiment.xlsx
    (61.4 KB, 1 views)
    Download


  • Account Numbers Experiment.xlsx
    (22.5 KB, 2 views)
    Download








  • Reply Support Opposition

    Props Report

    14

    Topics

    436

    Posts

    1026

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1026
    Post on 9-14-2016 21:33:34 | All posts



    First, your file Display Bookings....xlsx has VLOOKUP formulas that refer to a different file, which you did not attach. So I can't verify what you did.
    Second, in the list of account numbers in both files that you did provide, almost all of the account names have spaces at the end. Those will not match a version that does not have spaces. That's why it works when you copy and paste, because your paste includes the spaces. You need to get rid of those spaces. See attached.
    VLOOKUP is not case-sensitive and there is no built-in Excel function that cares about bold, font, or any other formatting aspect.








    Attached Files


  • Account Numbers Experiment+trimmed.xlsx
    (21.7 KB, 7 views)
    Download








  • Reply Support Opposition

    Props Report

    1

    Topics

    3

    Posts

    9

    Integral

    Newbie

    Rank: 1

    Integral
    9
     Author| Post on 9-14-2016 22:34:00 | All posts



    I would have never figured that out. The trimmed version worked perfectly. Thanks so so much.
    Reply Support Opposition

    Props Report

    10

    Topics

    185

    Posts

    432

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    432
    Post on 12-19-2016 10:54:48 | All posts



    What is the criteria for 125% and 150%?




    Reply Support Opposition

    Props Report

    10

    Topics

    185

    Posts

    432

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    432
    Post on 12-19-2016 11:20:18 | All posts



    See if this is what you want


      



          =IF(C5=half day,4.5,24*(E5-D5))
          
          





    Reply Support Opposition

    Props Report

    29

    Topics

    4792

    Posts

    9863

    Integral

    Genius

    Rank: 10Rank: 10Rank: 10

    Integral
    9863
    Post on 12-19-2016 12:57:16 | All posts



    formula in G5  


      



          =IF(C5=FRI,F5-5.5,IF(C5=HALF DAY,4.5,F5-8.5))
          
          





    Reply Support Opposition

    Props Report

    10

    Topics

    185

    Posts

    432

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    432
    Post on 12-19-2016 14:27:22 | All posts



    In F5
    =IF(C5=HALF DAY,4.5,24*(E5-D5))
    in G5
    =IF(WEEKDAY(A5,1)=6,MAX(0,F5-5.5),MAX(0,F5-8.5))
    Reply Support Opposition

    Props Report

    Points policy of this forum

    Archiver|Mobile|Small dark house|Contact us|Excel Bar

    GMT-5, 11-18-2017 14:12 , Processed in 0.166038 second(s), 20 queries .

    Powered by Discuz! X3

    © 2001-2013 Comsenz Inc.

    !fastreply! Top !return_list!