#VALUE! error nesting IF function
 

In A1 I have a formula that can have 7 results: a,b,c,d,e,f,g

In range A65:A95 I have the results I want if "a", B65:B95 results if
"b" and so on.

What I want is if A1=a, then A2:A32 will fill up w/ the range from
A65:A95

What I came up w/ for cell A2 was

=IF(A1="a",A65),IF(A1="b",B65),IF(A1="c",C65),IF(A 1="d",D65),IF(A1="e",E65),IF(A1="f",F65),IF(A1="g" ,A65)

I'm getting a #VALUE! error. What am I doing wrong or is there a
better way to do this?

Thanks,


--
fastballfreddy
------------------------------------------------------------------------
fastballfreddy's Profile: http://www.excelforum.com/member.php...o&userid=33986
View this thread: http://www.excelforum.com/showthread...hreadid=540116

#VALUE! error nesting IF function
 

ok it's been a long day, I realized what I was doing wrong, but I still
want to know if this is the best option?

Here's my formula

=IF(A1="a",A65,IF(A1="b",B65,IF(A1="c",C65,IF(A1=" d",D65,IF(A1="e",E65,IF(A1="f",F65,IF(A1="g",G65)) )))))


--
fastballfreddy
------------------------------------------------------------------------
fastballfreddy's Profile: http://www.excelforum.com/member.php...o&userid=33986
View this thread: http://www.excelforum.com/showthread...hreadid=540116

#VALUE! error nesting IF function
 

Hi!

This will work as a replcement for your formula:

=INDEX(A65:G65,MATCH(A1,{"a","b","c","d","e","f"," g"},0))

But I have no idea what your tying to do with this:

In range A65:A95 I have the results I want if "a", B65:B95 results if
"b" and so on.

What I want is if A1=a, then A2:A32 will fill up w/ the range from
A65:A95

Biff

"fastballfreddy"
<fastballfreddy.27iqny_1147141801.1655@excelforu m-nospam.com wrote in
message news:fastballfreddy.27iqny_1147141801.1655@excelfo rum-nospam.com...

In A1 I have a formula that can have 7 results: a,b,c,d,e,f,g

In range A65:A95 I have the results I want if "a", B65:B95 results if
"b" and so on.

What I want is if A1=a, then A2:A32 will fill up w/ the range from
A65:A95

What I came up w/ for cell A2 was

=IF(A1="a",A65),IF(A1="b",B65),IF(A1="c",C65),IF(A 1="d",D65),IF(A1="e",E65),IF(A1="f",F65),IF(A1="g" ,A65)

I'm getting a #VALUE! error. What am I doing wrong or is there a
better way to do this?

Thanks,


--
fastballfreddy
------------------------------------------------------------------------
fastballfreddy's Profile:
http://www.excelforum.com/member.php...o&userid=33986
View this thread: http://www.excelforum.com/showthread...hreadid=540116

#VALUE! error nesting IF function
 

Perhaps

=IF($A$1={"a";"b";"c"},INDIRECT(A1&"65"))

would be a better clue

--

fastballfreddy Wrote:
ok it's been a long day, I realized what I was doing wrong, but I still
want to know if this is the best option?

Here's my formula

=IF(A1="a",A65,IF(A1="b",B65,IF(A1="c",C65,IF(A1=" d",D65,IF(A1="e",E65,IF(A1="f",F65,IF(A1="g",G65)) )))))


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=540116

#VALUE! error nesting IF function
 

thanks for everybody's replies. My formula did work, but Biff's formula
is what I was looking for. Thanks again.


--
fastballfreddy
------------------------------------------------------------------------
fastballfreddy's Profile: http://www.excelforum.com/member.php...o&userid=33986
View this thread: http://www.excelforum.com/showthread...hreadid=540116

#VALUE! error nesting IF function
 

Hi,

Create a range name "A" for your range A65:A95
Create a range name "B" for your range B65:B95 etc

in cells A2:A32 (as a group), enter this array formula =indirect(A1)
(while pressing Shift+ Ctrl+Enter)

So now, when you enter "a" in cell A1, this wll populate your cells A2:A32
with values found in cells A65:A95

Cheers!

André





"fastballfreddy"
<fastballfreddy.27iqny_1147141801.1655@excelforu m-nospam.com wrote in
message news:fastballfreddy.27iqny_1147141801.1655@excelfo rum-nospam.com...

In A1 I have a formula that can have 7 results: a,b,c,d,e,f,g

In range A65:A95 I have the results I want if "a", B65:B95 results if
"b" and so on.

What I want is if A1=a, then A2:A32 will fill up w/ the range from
A65:A95

What I came up w/ for cell A2 was

=IF(A1="a",A65),IF(A1="b",B65),IF(A1="c",C65),IF(A 1="d",D65),IF(A1="e",E65),IF(A1="f",F65),IF(A1="g" ,A65)

I'm getting a #VALUE! error. What am I doing wrong or is there a
better way to do this?

Thanks,


--
fastballfreddy
------------------------------------------------------------------------
fastballfreddy's Profile:
http://www.excelforum.com/member.php...o&userid=33986
View this thread: http://www.excelforum.com/showthread...hreadid=540116