Indirect Formula!! Help Keeps Crashing Excel

[arkusM]

Taking a page from this podcast by Bill
http://www.mrexcel.com/Excel_Named_Formulas_training.html

I worked up this formula
=SUM(INDIRECT(ADDRESS(ROW()-$G26,COLUMN())),INDIRECT(ADDRESS(ROW()-1,COLUMN())))
Which works but then I need to add this:
INDIRECT(ADDRESS((ROW(),7))

To makke this formula:
=SUM(INDIRECT(ADDRESS(ROW()-INDIRECT(ADDRESS((ROW(),7)),COLUMN())),INDIRECT(ADDRESS(ROW()-1,COLUMN())))

At which causes Excel to crash...

I am doing something wrong or is there something better I can do?
I am planning on using this like a subtotal.

 

[Aladin Akyurek]

Too many volatile functions in a formula...

And, what does this bit

ROW()-INDIRECT(ADDRESS((ROW(),7))

is expected to do? Does the INDIRECT bit return a (vector of) number(s)?

 

[arkusM]

Too many volatile functions in a formula...

And, what does this bit

ROW()-INDIRECT(ADDRESS((ROW(),7))

is expected to do? Does the INDIRECT bit return a (vector of) number(s)?

That bit gets a value in column 7 and subtracts it from the row to get the top of the sum range.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am trying to dynamically define a range based on a variable table. The table returns the number of values in the table, in this specific case a 14. My formula is trying to define the sum range between the current and the 14 row upward.<o></o>
<o></o>
Ok I will see if I can work around it... Any ideas?<o></o>

 

[Aladin Akyurek]

That bit gets a value in column 7 and subtracts it from the row to get the top of the sum range.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am trying to dynamically define a range based on a variable table. The table returns the number of values in the table, in this specific case a 14. My formula is trying to define the sum range between the current and the 14 row upward.<o></o>
<o></o>
Ok I will see if I can work around it... Any ideas?<o></o>

Getting curious: Care to create a small sample where we could define a range of 4 rows high from the current row? I'd like also to hear where the formula must be entered...

 

[arkusM]

So what is going on is....<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The formula is running a vlookup on the Mnemonics on the left, if there is an error such as in the last line, which is a total... the vlookup returns an error, at which point I want it to do a sum based on the number in the total column (4).<o></o>
The reason I am doing it this way is because this a report that I am trying to minimize/ideally eliminate maintenance on this sheet. So this is based off a pivot table, where the amount of Mnemonics can vary. <o></o>
<o></o>
It appears that my HTML maker is not working properly so here is the formula that is present:<o></o>
=LOOKUP(9.99999E+307,CHOOSE({1,2},SUM(INDIRECT(ADDRESS(ROW()-$G13,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))),Volume_Lookup))<o></o>

This formula is in “H” and “I” for this demo I really only needed one of them... But in the sheet this appears in 4 columns and 50-75 rows.
<o></o>
<o></o>
“Volume_Lookup” is a names range that holds a simple vlookup.<o></o>

 

[arkusM]

Trying with Erik's table-it...

   F       G  H      I       
10 *RESTH  1  141.4  5,843   
11 K3-Pine 1  16.2   642     
12 MARSW   1  11.0   428     
13 MSRLK   1  342.9  14,340  
14         4  511.5  21,253  
Revenue
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
F10     *RESTH
F11     K3-Pine
F12     MARSW
F13     MSRLK
G10:G13 1
G14     4
H10:I14 =LOOKUP(9.99999E+307,CHOOSE({1,2},SUM(INDIRECT(ADDRESS(ROW()-$G10,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))),Volume_Lookup))
[Table-It] version 09 by Erik Van Geit

 

[arkusM]

Ok Trying yet again....
Sorry everybody for the multiple posts...

Excel Workbook
	F	G	H
9	Nova Mnemonic	Total	e3m3
10	*RESTH	1	141.4
11	K3-Pine	1	16.2
12	MARSW	1	11.0
13	MSRLK	1	342.9
14		4	511.5
Revenue

 

[arkusM]

Aladin if you are still there...

I solved this isses by using named ranges in the formula..

SUM(Top_Rng:T_End_Rng)

Top_Rng: =INDIRECT(ADDRESS(ROW()-T_Col,COLUMN()))
T-Col: =INDIRECT(ADDRESS(ROW(),7))
T_End_rng: =INDIRECT(ADDRESS(ROW()-1,COLUMN()))

The names ranges start with 'T' so they are grouped nicely.
This got me around the too many volitile fucnctions issue.
It is funny that I can trick Excel into allowing these volitle functions.

It would appear that this can get you around all sorts of limitions, such as cell character limits.. Interesting thanks for your input.

 

[Aladin Akyurek]

Aladin if you are still there...

I solved this isses by using named ranges in the formula..

SUM(Top_Rng:T_End_Rng)

Top_Rng: =INDIRECT(ADDRESS(ROW()-T_Col,COLUMN()))
T-Col: =INDIRECT(ADDRESS(ROW(),7))
T_End_rng: =INDIRECT(ADDRESS(ROW()-1,COLUMN()))

The names ranges start with 'T' so they are grouped nicely.
This got me around the too many volitile fucnctions issue.
It is funny that I can trick Excel into allowing these volitle functions.

It would appear that this can get you around all sorts of limitions, such as cell character limits.. Interesting thanks for your input.

It seems I can't do a quick reply to your posts...

I want a small sample, no formulas at all, and a desired result specification. If the desired result is some kind of range calculation, state also (the cell) where the calculation must start.

 

[arkusM]

It seems I can't do a quick reply to your posts...

I want a small sample, no formulas at all, and a desired result specification. If the desired result is some kind of range calculation, state also (the cell) where the calculation must start.

The quick reply does not work for me either.

Ok. I have a vlookup formula in P10:Px that looks up a value based on critieria in Col M. When the vlookup returns an error like it would on Row 14 and Row 19, I want to find the sum of (P10:P13) and the sum of (P15:P18) (I am using your lookup(9.9999e+307,Choose({1,2},0... contruct for when the vlookup errors out).


The formula must be able to be copied down in P and moved over to 3 other columns. So in column P in rows 10-13 would be returning the vlookup value (currently displayed) and rows 14 & 19 would return the sum or sub-total of the respective ranges.
Also in Row 20 would provide a total of all the lines excluding the sub-totals.

I hope that is clear.

Revenue

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #fffbf0" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>O</TD><TD>P</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">141.4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">16.2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">11</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">342.9</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12.1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">69.2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4