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 Programming Code Produces Wrong Value

Discussion in 'Business Applications' started by acf666ingdc, Sep 9, 2011.

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

    acf666ingdc Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    7
    I am writing a food costing program using excel. The program is designed to pull prices from various tabs(different suppliers) to a main Supplier Costing Page containing all the information on products for all suppliers. The code allows the user to enter in the name of the product, then uses the LOOKUP function to search for the product and returns the value in that row. The code I used to pull the information from the different suppliers works properly:

    A19 value Steak Strip Loin C/C St. Silver FRSH

    =LOOKUP(A19,Centennial!$A$6:Centennial!$A$100,Centennial!$R$6:Centennial!$R$100)

    Centennial is the tab for that supplier. It pulls the correct value from the page 0.74, to cell R19
    The problem I am having is that when I try to use this format of code on another tab it does not return the correct value:

    =LOOKUP(A33,'Supplier Costing'!$A$6:'Supplier Costing'!$A$100,'Supplier Costing'!$R$6:'Supplier Costing'!$R$100)

    The code should return the same value of the previous line of code as it is the same product:0.74 to cell R33 this time, however it returns the value of another product on the Supplier Costing tab. Is the LOOKUP function not searching the tab for the whole string? Any help or suggestions would be extremely helpfull. Thanks
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    is the data the same and does it have a space in it at all

    can you supply and example spreadsheet - with dummy data - as this is a public forum
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,792
    As an aside, have you considered using Access instead of Excell, it is much easier because you always use key fields for ID purposes.
     
  4. acf666ingdc

    acf666ingdc Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    7
    What is Access? Ive never used it before.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,792
    Access is Microsoft Office's database program, basically what you are trying to do is a Database, Excel can be used, but it is not as good as Access for "Relating" data.
    If you don't have Access it is would probably be too expensive to buy just for this, although I don't actually know how much it costs.
     
  6. acf666ingdc

    acf666ingdc Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    7
    Here is some dummy code.
     

    Attached Files:

  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    the files referes to an offline file - which i dont have

    so i modified the code here
    seems to work

    it may be to do with the offline file you are using
     

    Attached Files:

  8. acf666ingdc

    acf666ingdc Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    7
    Thanks it's still not helping when I plug it in. Im wondering if there is another function I can use instead of the LOOKUP function. I just read on the help that all the values are suppose to be in order and that would not fit my application.
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
  10. acf666ingdc

    acf666ingdc Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    7
    Thanks a lot it worked with the vlookup
     
  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    excellent - thanks for letting us know
     
  12. 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/1016789

  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