Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Formula to reference

  1. #1
    Guest

    Default

    Hello,
    I am working on a calculator in excel using two seperate sheets. (data sheet and calcualtor) I cannot come up with a formula for the calculator to grab the info from the data sheet no matter what cell it is in and bring it into a calculator. I am trying to make this as dynamic as possible, so when I change anything in the data sheet the calculator will never miss a beat. I tried something like this:

    =SUM(COUNTIF($D$5:$R$23,104)*_104p)+COUNTIF($D$5:$R$23,105)*_105p+COUNTIF($D$5:$R$23,107)*_107p

    ($D$5:$R$23,107) = This equals my calculator and if someone types 107 in, it references
    *_107p which is the rename of the cell in the data sheet and what is in it. so lets say the type 107=56. They will see the 56 pop up on the calculator

    The problem I am having is I get #NAME$, so this makes the calculator not work. I also have 113 different cells in the data sheet so as you can see it is one long formula, is there anyway to make this formula smaller and work. Thanks in advance for your help!

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe I am braindead, but I don't get it. Could you re-read your post and make sure you didn't make any typos? Like the 107=56?
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I know it is very confusing. Ok let me try it a different way. I have a product and the product has a product number (107) and it has a price of 56.00. When I type in 107 in the calculator it displays in the amount of the calculator it gives 56.00 or what ever is in the cell on the data sheet

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if you're getting #NAME? i think your formula is broken. try removing the SUM part of it - you're already using "+"s to add your countifs and that is probably confusing things.
    also, out of curiosity i tried naming a cell *_107p and it wouldn't let me. how did you do it?
    one more thing - i can't see how your cell names fit into the formula - there don't seem to be any operators (eg *,/, etc)or functions which apply to them. or have i missed something really basic?

    [ This Message was edited by: anno on 2002-02-24 17:07 ]

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you mean *the calculator*? Perhaps you just need a vlookup formula. You can type in a part number and it will return the price for you right into an Excel cell.

    Suppose you have Sheet2:

    A...............B...............C
    Item............Desc............Price
    101.............Widget..........56.99
    102.............Snarf...........48.99
    103.............Boink...........64.99

    Select columns A, B, and C and hit Insert-Name-Define and call it PriceSheet.

    In Sheet1, you have an invoice (maybe, or maybe it's your *calculator*):

    A...............B...............C
    Item............Desc............Price
    101.............Formula1........Formula2

    Where:

    the 101 is typed in by you.
    Formula1 is =vlookup(a2,PriceList,2,0)
    Formula2 is =vlookup(a2,PriceList,3,0)

    Am I making sense for you? Hope this helps.
    ~Anne Troy

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your suggestion, I have tried that and I get the same error.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cordova
    too easy to get posts and replies mixed up with this new board format. did you mean my suggestion didn't work, or dreamboat's? i also think vlookpup is is a good alternative but dreamboat was quicker on the draw than me. if you want to stick with your formula though, i have suggested another couple of things in an edit of my original post.
    good luck

  8. #8
    Guest

    Default

    Anno,
    Thank you for the suggestions, I have tried your suggestions and I get the same error. Also about the rename I am sorry I should of said defined the cell. My brain is thinking one thing and my fingers are typing something else.

    Dreamboat and Anno
    I have tried the lookup, it does not work well either thank you very much for this suggestion but if you look at my formula COUNTIF($D$5:$R$23,104)*_104p---The $D$5:$R$23 this is my calculator, The ,104 is my product code and the *_104p is actually what is in the cell on the different data sheet. (just a quick side note I have 113 different products). I have my formula off to the side away from the calculator. So if someone was to type in between D5 and R23 and they put in the product code of 107 below the calculator is an amount field it will show them the price and they can keep on typing in the cal. and the price just builds with the different product codes the put in I hope this is a little clearer everytime if input the formula in the forula field I get #NAME$

Some videos you may like

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
  •