CHARACTER REFERENCING 7

[Harley78]

I have been beating my head and cannot seem to figure it out, hopefully someone with more smarts than I can help me.

if I have a cell that contains characters,

e.g. Cell A10 contains OA/01 and now I am in Cell D4,(That already has a formula assign) I want to have this cell note that the second character (in this case "A")is Alpha and therefore, place a N in Cell D4.

IS THIS POSSIBLE? OR am I asking to much from Excel?

Thanks for any input or suggestions.

 

[Harley78]

Hey Joerd, thats correct, so if False, I want it to look at the original formula
(=IF(C7="D", "?", IF(C7="A", "?", IF(C7="K", "?", IF(C7="S", "?", IF(C7="AD", "-"))))), Only do I want it to be N when the e7 cell when False.

And thanks to all again for your help!!!! You helped tremendously

 

[yogia]

Hey Joerd, thats correct, so if False, I want it to look at the original formula
(=IF(C7="D", "?", IF(C7="A", "?", IF(C7="K", "?", IF(C7="S", "?", IF(C7="AD", "-"))))), Only do I want it to be N when the e7 cell when False.

Hi Harley:

Here is one way ...

=IF(OR(C7={"A","D","K","S"}),"?",IF(C7="AD","-",""))&IF(ISNUMBER(MID(E7,2,1)*1),"","N")

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Harley78]

Hi Yogia,

That looks much better, and it seems that it should work, but it looks like there would be a dash (Hyphen, depending on where your from I suppose, lol), or question mark at the beginning of each. But it is definately what I was looking for,,,,

Thanks for all your help peoples!!!!!!!

 

[yogia]

Hi Yogia,

That looks much better, and it seems that it should work, but it looks like there would be a dash (Hyphen, depending on where your from I suppose, lol), or question mark at the beginning of each ....

Hi Harley:

Does it work according to your specifications now? ... if some thing is still missing, please provide a clearer description and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Harley78]

Hi Yogia,

It operates exactly how I want it, however, it shows either a ?N or -N, trying now how I can convert those to just plain N

 

[yogia]

Hi Harley:

Let me see if understand what you are saying ... because I may have already taken care of what you are trying to do now.

See the formula ...

=IF(OR(C7={"A","D","K","S"}),"?",IF(C7="AD","-",""))&IF(ISNUMBER(MID(E7,2,1)*1),"","N")

this will put ? if C7 houses A, D, K, or S, and it will put - if C7 houses AD

However I had extended your requirement in the sense that what if cell C7 did not house A, D, K, S, or AD, then it will put nothing in there.

So if cell C7 houses anything other than A, D, K, S, or AD, and the second character in E7, is ALPHA, then the formula will result in simply ...

N

Does it meet your requirement or are you looking for still something else?

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Harley78]

Hi Yogi,

I thought it would just show N according to your formula, but mine isn't show ing this result.

It is doing exactly what I want, but my final result is reflecting either a ? then N (?N) or - and N (-N)when it is alpha. Thats what I was trying to reflect earlier, sorry about that..

 

[yogia]

Hi Harley:

Please check your formula and make sure it is ...

=IF(OR(C7={"A","D","K","S"}),"?",IF(C7="AD","-",""))&IF(ISNUMBER(MID(E7,2,1)*1),"","N")

or you may want to copy the formula in this post and see if this works for you.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Harley78]

Hi Yogi,

I did copy the last one as well as this one... and still the - or ? comes before the N. I opened a new workbook and started fresh and still it does it. So yours doesn't do it? hmmmmmm I'll keep plugging along and check the formating of the cell, but with a new workbook/worksheet, its tougher to figure out why mine is doing it.

again, so many thanks

 

[yogia]

Hi Harley:

Please post what do you have in

1) cell C7
2) cell E7
3) formula cell

You may want to directly copy from your worksheet and post in your response.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Harley78]

Hi Yogi,

Here is what it looks like now..... I listed the formula's at the bottom of the cell. Let me know if you require additional info...


C E H N

DT Rev Mat Disp
Cur / New Type INV

7 A 01/02 HALB ?
8 A 0M/01 HALB ?N
9 AD 01/02 DOKU -
10 AD OM/01 DOKU -N
11 D 01/02 HALB ?
12 D OM/01 HALB ?N
13 K 01/02 HALB ?
14 K 0M/01 HALB ?N
15 S 01/02 KMAT ?
16 S 0M/01 KMAT ?N

H7 = =IF(C7="D", "HALB", IF(C7="A", "HALB", IF
(C7="K", "HALB", IF(C7="S", "KMAT", IF
(C7="AD", "DOKU")))))

N7 = =IF(C7="D", "-", IF(C7="A", "?",
IF(C7="K", "-", IF(C7="S", "-",
IF(C7="AD", "-")))))

 

[yogia]

Hi Harley:

Thanks for posting data in your columns C, E, H, and N. However, I am not following you here ... what are you trying to show me. Where is your formula that results in N?

If you still want to pursue it, please describe clearly ...

1) your source data
2) your expected result(s) and the basis for that result
3) what do you see as the proble? ... what doesn't jibe?

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[Harley78]

Ok, Yogi,,, I totally screwed that one up..


Anyway what my goal is.... Cell N7 should contain N if it is Alpha ( Hence, your formula)

=IF(OR(C7={"A","D","K","S"}),"?",IF(C7="AD","-",""))&IF(ISNUMBER(MID(E7,2,1)*1),"","N")



The N7 formula above is a mistake that is actually cell I7 Sorry for the confusion

 

[yogia]

Hi Harley:

Whether that formula is housed in cell I7 or N7 doesn't matter. The result of the formula is based on entries in cell C7 and E7. From what I understood from you, the formula does exactly what you said you want it to do.

So, I suggest, you take your time, check the formula with various entries in cells C7, and E7 and figure out what is happening. And if you still have a problem, post back and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com