1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel: control name and fill in price macro

Discussion in 'Business Applications' started by Willum, Jul 9, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    Hi everybody,

    I got a pretty difficult question.

    I need a formula/macro, which does the following:

    In Cells E1:E20 names of products are filled in.

    In Cells in column B also names are filled in.

    When a name in Column B matches with one of the names in E1:E20 behind the cell in column B (column C) the price of that product has to be set. The price stands after the productsname in cell G1:G20.

    So how can I make a macro, that puts in the column C the correct price which stands in cell G1 till G20, regarding matching names in front of it.

    Thanks in advance!
    Willem
     
  2. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Hi
    Could you post a sample of you workbook, it will be easier to find the answer
    You could use function such: mach or vlookup!
     
  3. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    If we suppose the range E1:E20 are the names, and the range G1:G20 - prices, when you could use something like this:
    Code:
    C2=LOOKUP(B2,$E$1:$G$20,3,FALSE)
     
  4. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    Well, here is an example of it.

    So in the yellow cell, a name has to be entered. And behind this cell, the budget and actual price has to be filled in automatically from the schema above.
     

    Attached Files:

  5. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Here is an option to do this!
    Only you should always type in the cells in column B, the name as it is in the range where prices are set!
    You can do this using data validation, on the cell data should be inputed!
     

    Attached Files:

  6. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    Yes I was planning to do that, make a menu for those cells where people can select a data from the above by the prices
     
  7. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    Thanks, it works great this way ! I have added a list (Names from above) to each yellow cell in column B. Now they can't make any typing mistakes, so thank you very much!

    Didn't think the formula was that 'easy'.
     
  8. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    It would be the best way, cause if the product name will be different from the above ones you'll get an N/A error!
     
  9. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    Yes I already made it. It works great now. The menu (list) adds itself when a name in one of the above cells is changed. I got it totally working now. Only when you let it blank, the code gives an N/A error. This isn't preventable by making it say '0' then? Now I have one cell added with the name Empty with values (prices) 0 behind it.
     
  10. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Change the formula to :
    Code:
    =IF(B26<>"";VLOOKUP(B26;$F$6:$I$18;3;FALSE);0)
     
  11. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    thanks, works great. Thanks for al your help.
     
  12. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    you are welcome :cool:
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/728729

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice