Temperamental Indirect function

[BigDelGooner]

I have a list of the following values...

Monthly_Returns!$A$138:$A$138
Monthly_Returns!$A$136:$A$138
Monthly_Returns!$A$133:$A$138

However the INDIRECT function only works for the first one on the list.

Any ideas why that might be the case?

 

[MrKowz]

What is your entire formula?

 

[BigDelGooner]

Thank you for your reply

To get the values above, this formula...

IF((MATCH(Date_To,Monthly_Lookups,0)+1)-Q4<6,ERROR.TYPE(1),IF(ISERROR(P4*1),"Monthly_Returns!$A$"&(MATCH(Date_To,Monthly_Lookups,0)+1)-Q4&":$A$"&MATCH(Date_To,Monthly_Lookups,0),"Monthly_Returns!$A$"&(MATCH(P4,Monthly_Lookups,0)+1)-Q4&":$A$"&MATCH(P4,Monthly_Lookups,0)))

But when using the indirect function all I am doing is =indirect(A1)

 

[MrKowz]

Gotcha - the problem is that you are trying to return a range into a single cell.

To illustrate this, try going into a sheet and typing =A1:A4. It will only return the value in the first cell.

Are you wanting to populate a list based off of the the range returned? What is your expected result?

 

[BigDelGooner]

I thought it might be that but the indirect function works with this one...

Monthly_Returns!$A$6:$A$138

So guessing thats not the problem.

Basically I am trying to use the PRODUCT function to compound some performance numbers. Depending on the inputs the range will change dynamically...

Thanks for your help.

 

[BigDelGooner]

The range above being the desired range of performance numbers.

The end formula would be something along the lines of...

={product(1+offset(indirect(RANGEMENTIONEDABOVE),0,1)-1}

 

[MrKowz]

Can you provide some sample data, as well as your desired output?

Here is a few methods in which the INDIRECT function can be used (and illustrating the first-cell return)

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">98%</td><td style="text-align: right;;">98%</td><td style=";">A1:A4</td><td style="text-align: right;;">0.98</td><td style="text-align: right;;">0.470635</td><td style="text-align: right;;">3.37</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">87%</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">92%</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">60%</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><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet4</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=A1:A4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=INDIRECT(<font color="Blue">C1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=PRODUCT(<font color="Blue">INDIRECT(<font color="Red">C1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=SUM(<font color="Blue">INDIRECT(<font color="Red">C1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[BigDelGooner]

Try this.

Copy and paste these values into column A in a new worksheet...

<TABLE style="WIDTH: 161pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=215 x:str><COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7862" width=215><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16 width=215>Monthly_Returns!$A$138:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$138:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$138:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$136:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$133:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$130:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$127:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$115:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$115:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$103:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$103:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$91:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$91:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$79:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$79:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$19:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$19:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$6:$A$138</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$6:$A$17</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$18:$A$29</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$30:$A$41</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$42:$A$53</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$54:$A$65</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$66:$A$77</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$78:$A$89</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$90:$A$101</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$102:$A$113</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$114:$A$125</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=16>Monthly_Returns!$A$126:$A$137</TD></TR></TBODY></TABLE>

In column B enter the basic INDIRECT formula for each (=INDIRECT(A1)) etc.

All values now have a #REF error. If you change the name of the worksheet to 'Monthly_Returns' you will then see that some come up with the value 0, some have a #value error and a couple bring up the values I would expect to see.

Does this help?

 

[BigDelGooner]

Also not sure why changing the sheet name to 'Monthly_Returns' makes a difference because this is an absolute reference?!

 

[MrKowz]

I see what you are seeing now. That is definitely strange. However, the question lies, why do you need to use simply =INDIRECT(A1)?