INDIRECT function not returning cell value instead of cell formula

[KeLeLa]

This seems like it should be an easy answer but I've been struggling with it for a while. I haven't been able to see that anyone else addressed it.


I have the following formula:


=IF(AND(V147="YES",V137<0),-V137+(V104+V105+V108+V109+V135)+(V117+V118+V119+V120+V121+V122)+(V125+V126),0)


V137 contains the formula =+V133+V135. (I have tried changing this to =SUM(V133:V135). It made no difference).


Rather than checking if the value in V137 is less than zero, it is checking whether either V133 or V135 is less than zero.


If I change the formula to include the INDIRECT function as follows,


=IF(AND(V147="YES",INDIRECT("V137")<0),-V137+(V104+V105+V108+V109+V135)+(V117+V118+V119+V120+V121+V122)+(V125+V126),0)


it has no effect. It is still comparing V133 and V135 to zero.


I tried taking out the quotation marks around V137. But then, it returns #REF!.


Any thoughts?


Thank you.

 

[DaveTurton]

Your first formula appears to work fine for me. It could be neater using named ranges, but is working.


I am only getting the calculated result if the value of V137 is less than 0 regardless of the individual values of V133 and V135

 

[Luke M]

First, just to clarify, this:

=V133+V135

is not the same as:

=SUM(V133:V135)


the former adds only the specified 2 cells, while the latter sums every cell in the range given (3 cells).


Now, let's simplify your formula a little:

=IF(AND(V147="YES",V137<0),SUM(V104:V105,V108:V109,V135,V117:V122,V125:V126,-V137),0)


I would be curious if there's some logic as to why those specific cells are getting added (perhaps we cna use a SUMIF?) but it's not important atm.

Your formula right now will do the big sum if V147 = "YES", and the value of V137 is less than 0. There is no way using XL formulas to get the formula from another cell. If you want to check the values of V133 and V135, we'll have to add them to the Boolean logic check in the IF function.


If the current value in V137 is less than 0, and your formula still isn't doing what you want, perhaps you could paste a copy of your data to this thread? If you could also include what data/info you would like to see, that would be most helpful.

 

[Faseeh]

Hi KeLeLa,


Welcome to the forums and upload a sample file as said Luke (indirectly) :D


Faseeh

 

[KeLeLa]

Luke, I prepared a small sample worksheet to post for you. I lost my way, . . . , how do I send the worksheet?

 

[Faseeh]

@ KeLela,


You can go to any public file hosting servers like 2shared, filedrop etc and upload file and post its link here.


Faseeh

 

[KeLeLa]

I'm still in process on this. But, I was looking over the properties of the file, which was created before I came to this company. I am currently working with Excel 2010 on my computer. However, this file is based on a template that was originally created in 2002.


Based on the functions I am attempting to use, does that in itself present obstacles that I am running into?


Thanks.

 

[Hui]

KeLeLa


a few comments


By having:

V137 =V133+V135

and [code]IF(AND(V147="YES",V137<0)

You are saying If V147 = Yes and the sum of V133&V135 is less than 0

which implies that one or both of V133 and V135 must be less than 0 to be true


I would simply your formula as:

=IF(AND(V147="YES",V137<0),SUM(V104:V105,V108:V109,V117:V122,V125:V126,V135,-V137),0)[/code]


Secondly check that V147 has "Yes" or "YES" but not "Yes " or " Yes" (without the quote marks)

You may have leading/trailing spaces that are confusing you


Also check that the formula doesn't have a leading or trailing space in the "YES" part


Please also check that calculation is set to Automatic,

Goto the Formula Tab, Calculation Options and check/change


To post a sample file, Refer:

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[KeLeLa]

I think the difference from 2002 to 2010 must have caused some formatting issues.


I did two things.


First, I changed the formula to an IF statement inside of an IF statement, instead of using the AND statement. (This may have been unnecessary if I had done the second thing first).


Second, at someone elses suggestion, I went to the cells that were still having problems. I cleared the contents of the V147 and then went in and re-formatted the cell (to a number format). Then, I re-keyed YES into V147.


Now the formula works.


Thanks for all of the help.


I have three more lines of formulas with problems. So, I may be back soon.


Thanks again.