This one is a thinker!

[idahoexcel]

I have four columns that i need to have the user decide the simple mathematical relationship between them using a drop down menu. Ex. a1+ a3+a4*a5=a7 with the math signs on a drop down list. Then be able to have blanks if needed. The blanks i figured out seperate by using an if statement (IF(N29=0,1,N29)). I am not so sure this is even possible but if it were it would help so much.

 

[iliace]

Like this?

Excel Workbook
	A	B	C	D
2	2	+	2	4
3	2	-	2	0
4	2	*	2	4
5	2	/	2	1
sheet

#VALUE!

</td></tr></table></td></tr></table>

You cannot use EVALUATE directly on the worksheet, but you can use it in a name.

 

[idahoexcel]

Like this?

Excel Workbook
	A	B	C	D
2	2	+	2	4
3	2	-	2	0
4	2	*	2	4
5	2	/	2	1
sheet

#VALUE!

</td></tr></table></td></tr></table>

You cannot use EVALUATE directly on the worksheet, but you can use it in a name.

<TABLE style="WIDTH: 237pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=319 border=0><COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 29pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=39 height=19> 10 </TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 23pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=31>*</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 29pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=39>4</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 23pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=31>+</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 29pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=39>2</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 23pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=31>*</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 29pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=39>12</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 23pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=31>=</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 29pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=39> </TD></TR></TABLE>

Kind of. This is a what i am looking at. I have the math signs on a drop down menu. THis way the user can change the signs depending on what he is converting.

 

[iliace]

Is what I'm getting at. In this example, L1:L4 is a list of +, -, /, and *.

Excel Workbook
	C	D	E	F	G	H	I	J	K
8	10	*	4	+	2	*	12	=	64
9	10	*	4	-	2	*	12	=	16
10	10	*	4	+	2	/	12	=	40.16667
11	10	/	4	+	2	+	12	=	16.5
sheet

#VALUE!

</td></tr></table></td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#0000ff;background-color:#ffFCF9; color:#000000; "><tr><td ><b>Data Validation in Spreadsheet</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Allow</td><td >Datas</td><td >Input 1</td><td >Input 2</td></tr><tr><td >D8</td><td >List</td><td > </td><td >=$L$1:$L$4</td><td > </td></tr><tr><td >F8</td><td >List</td><td > </td><td >=$L$1:$L$4</td><td > </td></tr><tr><td >H8</td><td >List</td><td > </td><td >=$L$1:$L$4</td><td > </td></tr></table></td></tr></table>

 

[idahoexcel]

<TABLE style="WIDTH: 294pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=394 border=0><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl69 id=td_post_1690103 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 23pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.25pt; BACKGROUND-COLOR: #a5a5a5" width=30 height=19> </TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 29pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=39>A</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 23pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=31>B</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 29pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=39>C</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 23pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=31>E</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 29pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=39>G</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 23pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=31>H</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 29pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=39>I</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 23pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=31>J</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 63pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5" width=84>K</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.25pt; BACKGROUND-COLOR: #a5a5a5" align=right height=19>1</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white"> 10 </TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">*</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">4</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">+</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">2</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">*</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">12</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">=</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.25pt; BACKGROUND-COLOR: #a5a5a5" align=right height=19>2</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white"> 15 </TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">*</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">4</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">+</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">7</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">*</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">12</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">=</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.25pt; BACKGROUND-COLOR: #a5a5a5" align=right height=19>3</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white"> 8 </TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">*</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">7</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">+</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">5</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">*</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">2</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #a5a5a5">=</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

This is what i need it to look like when i am done. What would the formula be in the last column K. Sorry, i am not sure i understand the example.

 

[idahoexcel]

i posted that last comment too early. It looks like you made it work. But i do not have the evaluate function or at least i do not know where to look. i have the list part. What is the name "evalme" from? You have some serious skill by the way.

 

[iliace]

It's a defined name. Activate the first cell where you want the evaluation to take place (K8 in my example). Go to Insert->Name->Define. For name, type in EvalMe (or whatever you want it to be called). For refers to, use this formula:

=EVALUATE(C8 & D8 & E8 & F8 & G8 & H8 & I8)

EVALUATE is a carryover from older versions of Excel, and is no longer supported in formulas. You can, however, still use it in defined names (as explained above). All it does is take a string literal expression and evaluates it, which is perfect for your situation. So, EVALUATE("2+2") returns 4.

By adding the name in the active cell, the relative references are preserved. In other words, after you define the name in K8, putting =EvalMe in K8 will evaluate C8 & D8 & E8 & F8 & G8 & H8 & I8. If you do it in K9, it will evaluate C9 & D9 & E9 & F9 & G9 & H9 & I9.

Hope that makes sense.