# Indirect Formula!! Help Keeps Crashing Excel

This is a discussion on Indirect Formula!! Help Keeps Crashing Excel within the Excel Questions forums, part of the Question Forums category; Taking a page from this podcast by Bill http://www.mrexcel.com/Excel_Named_F..._training.html I worked up this formula =SUM(INDIRECT(ADDRESS(ROW()-\$G26,COLUMN())),INDIRECT(ADDRESS(ROW()-1,COLUMN()))) Which works but then I ...

1. ## Indirect Formula!! Help Keeps Crashing Excel

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

I worked up this formula
Which works but then I need to add this:

To makke this formula:

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.

2. ## Re: Indirect Formula!! Help Keeps Crashing Excel

Too many volatile functions in a formula...

And, what does this bit

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

3. ## Re: Indirect Formula!! Help Keeps Crashing Excel

Too many volatile functions in a formula...

And, what does this bit

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.

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.

Ok I will see if I can work around it... Any ideas?

4. ## Re: Indirect Formula!! Help Keeps Crashing Excel

Originally Posted by arkusM
That bit gets a value in column 7 and subtracts it from the row to get the top of the sum range.

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.

Ok I will see if I can work around it... Any ideas?
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...

5. ## Re: Indirect Formula!! Help Keeps Crashing Excel

So what is going on is....
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).
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.

It appears that my HTML maker is not working properly so here is the formula that is present:

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.

“Volume_Lookup” is a names range that holds a simple vlookup.

6. ## Re: Indirect Formula!! Help Keeps Crashing Excel

Trying with Erik's table-it...
Code:
```   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```
Code:
```RANGE   FORMULA (1st cell)
F10     *RESTH
F11     K3-Pine
F12     MARSW
F13     MSRLK
G10:G13 1
G14     4
[Table-It] version 09 by Erik Van Geit```

7. ## Re: Indirect Formula!! Help Keeps Crashing Excel

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

Revenue

 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

Excel tables to the web >> Excel Jeanie HTML 4

8. ## Re: Indirect Formula!! Help Keeps Crashing Excel

Aladin if you are still there...

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

SUM(Top_Rng:T_End_Rng)

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.

9. ## Re: Indirect Formula!! Help Keeps Crashing Excel

Originally Posted by arkusM
Aladin if you are still there...

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

SUM(Top_Rng:T_End_Rng)

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.

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.

10. ## Re: Indirect Formula!! Help Keeps Crashing Excel

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

 O P 10 1 141.4 11 1 16.2 12 1 11 13 1 342.9 14 4 15 1 0 16 1 0 17 1 12.1 18 1 69.2 19 4

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•