Formula Range Name Not Working

[Luther]

I'm trying to create a range name that is defined as a formula rather than it being a range of cells. The following formula works fine when I enter it into a cell but when I try to define it as a range name I get "#Value".

=indirect(address(test_sales,column()))/(indirect(address(test_ee,column())))/12)

test_sales - refers to row 13 (which equals 27002)
test_ee - refers to row 107 (which equals 447.4)
column - is simply the column formula which refers to whatever column it's in. In this case column would equal 71.

I'm using Excel 2000 and Windows 7 Professional. This is my first posting so if I have made any errors in "how to" or "what to" post just let me know.

Thank you in advance for your help.

 

[shg]

If I understand what you're trying to do, you could do this instead:

Select any cell in col A

Define test_sales as =A$13

Define test_ee as =A$107

Define Luthor as =test_sales/test_ee/12

Then enter =Luthor wherever you like

EDIT: NB where the dollar signs appear in the names ...

 

[Luther]

Thanks for the reply. It does work if I use that approach. This worksheets is part of an interactive graph. So the user would select what they want to graph. Based on their selection the test_sales might be row 15 or 16 and test_ee could be row 110. That part seems to be working ok. What seems to be causing the problem, I believe anyway, is the column formula. When I enter it in parts it does fine but when I enter the entire formula into the Defined Range Name it errors.

Maybe it would help if I explained what I want to happen. I have data rows of sales, backlog, employees, cogs, etc. Then I have a section where I want to do strictly calculcations based on what the user selects. So rather than having several ranges for the various calculations, I would like to be able to have one range for calculations that would bring back the result based on the user selection. There would be "if statements" to tell what "range name formula" the cell should equal. Everything works fine except I cannot get the column formula to work within the defined range name.

I hope that clarifies it a little more. Thanks

 

[shg]

I would start by not using INDIRECT (which is volatile).

test_sales could be defined, for example, as

=INDEX(Sheet1!$1:$1048576, Sheet1!$A$1, 0)

Then in any random cell on Sheet1, =test_sales would return the value of the row specified by A1 and the column in which the formula appears (an implied intersection)

 

[Luther]

Thank you... that is exactly what I was searching for. I guess I didn't make the connection of an implied intersection using the index function. Thanks again

 

[shg]

You're welcome, glad it helped.