excel 2007 natural language equivalent - indirect?

[xlcomic]

I'm able to do a "natural language" equivalent in Excel 2007, but have been unable to use "indirect()" to create the formula.

Natural language can be done by defining names by top row and left column in something like this:

Sales Expense Profit Comment
Jan 1000 500 500 typical month
Feb 1500 700 800 surge in high-end products
Mar 1200 650 550 product mix still favorable

Natural Language returns the intersection of the 2 ranges:
=Jan Expense would equal 500
=Feb Sales would equal 1500
=Mar Comment would equal "product mix still favorable"

...but when I try to use indirect to build the formula, it doesn't work.

Any ideas?

Thanks in advance.

 

[Norie]

How have you tried using INDIRECT?

PS What exactly do you mean by 'natural language'?

Doesn't that usually involve setting up 'rules' of some sort on how things are structured/related?

Then using those rules to come to conclusions based on input?

 

[gsistek]

Is this the type of thing you're looking for?
Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD></TD><TD>Sales</TD><TD>Expense</TD><TD>Profit</TD><TD>Comment</TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Jan</TD><TD>1000</TD><TD>500</TD><TD>500</TD><TD>typical month</TD><TD></TD><TD>Feb Expense</TD><TD>700</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Feb</TD><TD>1500</TD><TD>700</TD><TD>800</TD><TD>surge in high-end products</TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Mar</TD><TD>1200</TD><TD>650</TD><TD>550</TD><TD>product mix still favorable</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>H2</TH><TD style="TEXT-ALIGN: left">=INDEX(A1:E4,MATCH(LEFT(G2,FIND(" ",G2)-1),A1:A4,0),MATCH(RIGHT(G2,LEN(G2)-FIND(" ",G2)),A1:E1,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
You can change G2 to any combination of criteria.

 

[xlcomic]

Here's the macro code to setup the scenario:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A2").Select
ActiveCell.FormulaR1C1 = "Jan"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Feb"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Mar"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Sales"
Range("B2").Select
ActiveCell.FormulaR1C1 = "1000"
Range("B3").Select
ActiveCell.FormulaR1C1 = "1500"
Range("B4").Select
ActiveCell.FormulaR1C1 = "1200"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Expense"
Range("C2").Select
ActiveCell.FormulaR1C1 = "500"
Range("C3").Select
ActiveCell.FormulaR1C1 = "700"
Range("C4").Select
ActiveCell.FormulaR1C1 = "650"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Profit"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("D2").Select
Selection.Copy
Range("D2:D4").Select
ActiveSheet.Paste
Range("E1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Comment"
Range("E2").Select
ActiveCell.FormulaR1C1 = "typical month"
Range("E3").Select
ActiveCell.FormulaR1C1 = "surge in high-end products"
Range("E4").Select
ActiveCell.FormulaR1C1 = "product mix still favorable"
Range("A1:E4").Select
Selection.CreateNames Top:=True, Left:=True, Bottom:=False, Right:= _
False
Range("D7").Select
ActiveCell.FormulaR1C1 = "=Jan Expense"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=Feb Sales"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=Mar Comment"
Range("C7").Select
ActiveCell.FormulaR1C1 = "Jan Sales"
Range("C7").Select
ActiveCell.FormulaR1C1 = "Jan Expense"
Range("C8").Select
ActiveCell.FormulaR1C1 = "Feb Sales"
Range("C9").Select
ActiveCell.FormulaR1C1 = "Mar Comment"
Range("C11").Select
ActiveCell.FormulaR1C1 = "=INDIRECT(R[-9]C[-2]&+"" ""&+R[-10]C)"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=INDIRECT(R[-9]C[-2]&+"" "")"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=INDIRECT(R[-9]C[-2]&+"" ""&+R[-11]C[-1])"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=INDIRECT(R[-9]C[-2]&+"" ""&+R[-11]C[-1])"
Range("C12").Select
Calculate
Range("C13").Select
ActiveCell.FormulaR1C1 = "=INDIRECT(R[-9]C[-2]&+"" ""&+R[-12]C[2])"
Range("C13").Select
End Sub

My indirect functions don't come up with the right answers.

 

[xlcomic]

Is this the type of thing you're looking for?
Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD></TD><TD>Sales</TD><TD>Expense</TD><TD>Profit</TD><TD>Comment</TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Jan</TD><TD>1000</TD><TD>500</TD><TD>500</TD><TD>typical month</TD><TD></TD><TD>Feb Expense</TD><TD>700</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Feb</TD><TD>1500</TD><TD>700</TD><TD>800</TD><TD>surge in high-end products</TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Mar</TD><TD>1200</TD><TD>650</TD><TD>550</TD><TD>product mix still favorable</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>H2</TH><TD style="TEXT-ALIGN: left">=INDEX(A1:E4,MATCH(LEFT(G2,FIND(" ",G2)-1),A1:A4,0),MATCH(RIGHT(G2,LEN(G2)-FIND(" ",G2)),A1:E1,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
You can change G2 to any combination of criteria.

The match function is an excellent way to arrive at the answers, but I was looking for something that a novice would easily understand. Although my indirect function is not easier to understand if it works, but it is much shorter.

 

[MrKowz]

I would reccommend making two validation lists, one list containing the month (row labels), and the other list containing Sales, Expense, etc (column lablels). This would allow the user to select from a box which things to get, and then the INDEX/MATCH formula would be greatly simplified (this would also be the simplest way to accomplish your needs. An INDIRECT formula is not only incredibly ineffecient here, but would be significantly harder to understand once created):

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD>Sales</TD><TD>Expense</TD><TD>Profit</TD><TD>Comment</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Jan</TD><TD style="TEXT-ALIGN: right">1000</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right">500</TD><TD>typical month</TD><TD style="TEXT-ALIGN: right"></TD><TD>Mar</TD><TD>Sales</TD><TD style="TEXT-ALIGN: right">1200</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Feb</TD><TD style="TEXT-ALIGN: right">1500</TD><TD style="TEXT-ALIGN: right">700</TD><TD style="TEXT-ALIGN: right">800</TD><TD>surge in high-end products</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Mar</TD><TD style="TEXT-ALIGN: right">1200</TD><TD style="TEXT-ALIGN: right">650</TD><TD style="TEXT-ALIGN: right">550</TD><TD>product mix still favorable</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>I2</TH><TD style="TEXT-ALIGN: left">=INDEX(B2:E4,MATCH(G2,A2:A4,0),MATCH(H2,B1:E1,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[xlcomic]

Thanks, that's what I'll do. I appreciate your advice.