How to read the value of a cell

H

Hell-fire

Hi,

Is there a way to read the value of a cell that has a formula in it? I have
one cell who's formula reads the value of other cells, but some of those
other cells would have formula's in them. Is this possible? Thank you
 
M

Max

Yes, of course. Formula cells can point to & read the returns of yet other
formula cells. Eg: you may have in C1: =SUM(A1:B1) - which returns the sum of
the values entered in A1 and B1. And you could then have in D1:
=IF(C1=0,"",C1) - which checks whether the formula in C1 returns a zero, and
if so, D1 will in turn return a neat "blank", ie: "", otherwise D1 will
simply return the value computed in C1.
 
H

Hell-fire

Hi Max,

Thank you for responding to my post. The problem is, when I use the formula
in the one cell, the other cell isn't reading properly. If I take out the
formula and just place a value in it, it works.

The main cell formula is this:
=IF(AX446=1,IF(OR(AF446="A",AF446="V"),IF(AZ446="○",IF(BA446="-","Ｆ","Ｂ"),IF(BA446="○","I","X")),""),IF(AX446=1.1,IF(OR(AF446="A",AF446="V"),"1"&IF(AZ446="○",IF(BA446="-","F","Ｂ"),IF(BA446="○","I","X")),""),""))

The other cell I want read has this formula:
=IF(AX446=1.1,"-",IF(AND(AX446=1,AF446="A"),"â—‹",IF(AND(AX446=1,AF446)="V","-"," ")))

If I have the above formula in the cell, the main formula doesn't seem to
read the "A" or "V" in a different cell. I hope this makes sense. Thank you
 
M

Max

Perhaps it's just a syntax typo in this formula of yours tripping things up:
=IF(AX446=1.1,"-",IF(AND(AX446=1,AF446="A"),"â—‹",IF(AND(AX446=1,AF446)="V","-"," ")))
Click to expand...

Try correcting it to read as
=IF(AX446=1.1,"-",IF(AND(AX446=1,AF446="A"),"â—‹",IF(AND(AX446=1,AF446="V"),"-"," ")))

Hopefully that will clear it up for you.
 
H

Hell-fire

Hi Max,

Thanks for the response. I changed it, but if I type in 1.0, there is no
problem for the A and V. If I type in 1.1 though, it still not reading A.
If Ican't get it right, then the user will just have to fill in that cell
manually. Thanks again.
 
M

Max

It seems ok to me.

Maybe just take a look at this test file:
http://cjoint.com/?jelJ5hCKXj
hellfire_wks.xls

Post back on what's the problem you have with the "main cell"'s formula
returns (easier to see things with the test file)
 
H

Hell-fire

Hi Max,

Thank you again for your help. The problem is, when I have "1.1" for cell
AX, that should put a "-" in cell BA and I type "A" in cell AF, the output
should be 1B. Now if I type "V" instead of the "A", the output should be 1F.
I get the "-", but seems cell E which has:

=IF(AX446=1,IF(OR(AF446="A",AF446="V"),IF(AZ446="○",IF(BA446="-","Ｆ","Ｂ"),IF(BA446="○","I","X")),""),
IF(AX446=1.1,IF(OR(AF446="A",AF446="V"),"1"&IF(AZ446="â—‹",IF(BA446="-","F","ï¼¢"),IF(BA446="â—‹","I","X")),""),""))

doesn't seem to read the "A" anymore.

No matter which I type, "A" or "V", I still get 1F. I should only get 1F
for having "V" and 1B for having "A".

It works with no problem when I type "1.0" in cell AX. The output I get for
"A" is B and output for "V" is F.

I hope I'm not making this confusing. Thank you.
 
H

Hell-fire

Hi again Max,

I don't know if this will help, but back in July 2nd, I posted 2 questions
that gave me the one formula. It has everything else that is interacting
with the different cells. The subject titles are "Help with editing a
formula" and "Additional help to edit a formula"

Thank you
 
H

Hell-fire

Hi for the 3rd time today,

Unfortunately, not being any good at formula's for Excel, I think I see
where the problem is, but not sure how to solve it.

The problem seems to be in this formula:

=IF(AX445=1,IF(OR(AF445="A",AF445="V"),IF(AZ445="○",IF(BA445="-","Ｆ","Ｂ"),IF(BA445="○","I","X")),""),IF(AX445=1.1,IF(OR(AF445="A",AF445="V"),"1"&IF(AZ445="○",IF(BA445="-","Ｆ","Ｂ"),IF(BA445="-","I","X")),""),""))

When AX = 1.1, there should be a "-" in BA. Cell BA should then be ignored
and the formula should then lood at AF for either "A" or "V". If it sees "A"
then there should be a 1Bxxxxxxxxxxxx, if there is a "V" then there should be
1Fxxxxxxxxxx. From what I'm seeing though, its reading the "-" and inputting
1F, ignoring cell AF completely.

If I leave BA blank or input anything else but the "-", I get my 1B. I
guess I need to have help re-adjusting the formula. Thank you for your help
though on the other part.
 
M

Max

.. I guess I need to have help re-adjusting the formula.

Try this revised formula (2nd part was re-fashioned):
=IF(AX446=1,IF(OR(AF446="A",AF446="V"),IF(AZ446="○",IF(BA446="-","Ｆ","Ｂ"),IF(BA446="○","I","X")),""),
IF(AX446=1.1,IF(AND(AF446="A",AZ446="○",BA446="-"),"1Ｂ",IF(AND(AF446="V",AZ446="○",BA446="-"),"1Ｆ",
IF(AND(AF446="A",AZ446="â—‹",BA446="â—‹"),"1I",IF(AND(AF446="V",AZ446="â—‹",BA446="â—‹"),"1X",""))))))
 
H

Hell-fire

Hi Max,

Thanks again for your help. I haven't inputted the new formula, but I
noticed that I have been an idiot. Well its not the first time unfortunately.

I was so focused on AZ and BA that I didn't realize that what makes the
difference for the output of B or F is cell AF which we enter either "A" or
"V".

I don't know why the original creator of this sheet didn't base this off of
cell AF. Would this be able to simplify that long formula? Thank you.
 
M

Max

I haven't inputted the new formula ..
Click to expand...

why not just plug it in and test it out?

(don't input, just copy it direct from the posting and paste into the
formula bar, then clean up the line breaks)

.... if it works fine and you want to shorten it,
pl take this up in a fresh, new posting ..
 
H

Hell-fire

Thanks Max. Take a nice long break and enjoy.

Max said:
why not just plug it in and test it out?

(don't input, just copy it direct from the posting and paste into the
formula bar, then clean up the line breaks)

.... if it works fine and you want to shorten it,
pl take this up in a fresh, new posting ..
Click to expand...
 
M

Max

welcome. as a closure, pl post back here on the results you get when you
plug in that last revised formula
 
H

Hell-fire

Hi Max,

I plugged in the formula, it works and gives me the result. But when I
apply it to the other cells with no data, I get "FALSExxxxXxxxxxxxxx". If I
put 1 or 1.1 in cell AX, then the FALSE goes away.

I know this is becoming a pain.

On the sheet, the rows with no data in Column C is like this:

xxxxX431_xxxx_XXXxxxxx this goes up progressively.

To me having FALSExxxxX431_xxxx_XXXxxxxx showing up wouldn't bother me, but
to the others that also use this sheet, it would bother them.

I'm trying to figure out how to get it not to show the FALSE. Just wish I
knew more on how to make these formula's. I don't know how you guys do it,
but I sure envy your ability. Thanks again.
 
M

Max

Think this should do it:

=IF(OR(AF446="",AX446=""),"",IF(AX446=1,IF(OR(AF446="A",AF446="V"),IF(AZ446="?",IF(BA446="-","F","B"),IF(BA446="?","I","X")),""),IF(AX446=1.1,IF(AND(AF446="A",AZ446="?",BA446="-"),"1B",IF(AND(AF446="V",AZ446="?",BA446="-"),"1F",IF(AND(AF446="A",AZ446="?",BA446="?"),"1I",IF(AND(AF446="V",AZ446="?",BA446="?"),"1X","")))))))

I bolted on a front IF check to the earlier:
=IF(OR(AF446="",AX446=""),"", ...
 
H

Hell-fire

Well Max,

That did it. It works like a charm now. Wish I could buy you a drink for
all your help. Didn't notice until now that your in Singapore, was wondering
before why you were able to answer my question so quickly. For me I'm in
Japan and for the reason they use the degree character is, it represents a
circle in Japanese font.

Anyway, thank you very much, I know this is probably been a bigger headache
for you than for me. Especially with all those IF statements. Please take
care and enjoy
 
Top