Formula to reference

Feb 24th, 2002, 05:28 PM

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!

Anne Troy · Feb 24th, 2002, 05:35 PM

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?

Cordova · Feb 24th, 2002, 05:55 PM

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

anno · Feb 24th, 2002, 05:59 PM

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 ]

Anne Troy · Feb 24th, 2002, 06:01 PM

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.

Cordova · Feb 24th, 2002, 06:06 PM

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

anno · Feb 24th, 2002, 06:12 PM

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

Feb 24th, 2002, 06:41 PM

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$

Feb 24th, 2002, 05:35 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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

Feb 24th, 2002, 06:01 PM	#5
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 24th, 2002, 05:28 PM	#1
Guest Posts: n/a	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!

Feb 24th, 2002, 05:55 PM	#3
Cordova New Member Join Date: Feb 2002 Posts: 3	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

Feb 24th, 2002, 05:59 PM	#4
anno Board Regular Join Date: Feb 2002 Posts: 202	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 ]

Feb 24th, 2002, 06:06 PM	#6
Cordova New Member Join Date: Feb 2002 Posts: 3	Thanks for your suggestion, I have tried that and I get the same error.

Feb 24th, 2002, 06:12 PM	#7
anno Board Regular Join Date: Feb 2002 Posts: 202	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

Feb 24th, 2002, 06:41 PM	#8
Guest Posts: n/a	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$