Formula to reference

G

Guest

Guest
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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$
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top