Some more help to edit some formulas

[Hell-fire]

Hi all,

First I like to thank Max for all the help of my previous post. Second, I
like to thank the others for the help on my other posts I made a few months
ago.

I have a simular excel file that just has an extra addition, but with my
very limited understanding of how you guys make these formulas, I can't quite
make an adjustment that works.

Here are the cells involved and formulas:

Column B: is an identifier number from 1 to 500
Cell D: =CONCATENATE(E28,F28,G28,H28,K28,I28,K28,J28)
Cell E: =IF(BB28="â—‹",IF(BD28="â—‹","B","F"),IF(BD28="â—‹","I","X"))
Cell F: =IF(R28>0,CONCATENATE(0,LEFT(R28,3)),"xxxx")
Cell G: =IF(AB28="R","R",IF(AB28="T","T",IF(AB28="A","A","X")))
Cell H: =B28
Cell K: _
Cell I: =IF(ISBLANK(AC28)=TRUE,"xxxx",AC28)
Cell J: =IF(ISBLANK(AX28)=TRUE,"XXX",LEFT(AX28,3))
Cell AB will have values of either "A", "R", or "T"
Cell AZ will have value of either "1.0" or "1.1"
Cell BB will have value of either "â—‹" or "-"
Cell BC will have value of either "â—‹" or "-"
Cell BD will have value of either "â—‹" or "-"
Cell BE will have value of either "â—‹" or "-"
The "â—‹" is the degree character, just in Japanese its a circle.

With no values inputted for this row Cell C looks like this:

XxxxxX014_xxxx_XXX

For what I need to be done is, when Cell AB has either "A" or "T" and Cell
AZ has 1.0, Cell BB, BC, BD and BE should have "â—‹".
IF Cell AB has "R" and Cell AZ has 1.0, then Cell BB and BD have "â—‹" and
Cell BC and BE should have "-".
Also when Cell AB has "A", beginning character in Cell C should start with a
"B", if Cell AB has "T" or "R", then the beginning character should be an "F".

Now, when Cell AZ has 1.1 and Cell AB has either "A" or "T", then Cells BB
and BCshould have "â—‹" and Cells BD and BE should have "-".
If Cell AZ has 1.1 and Cell AB has "R", then Cell BB has "â—‹" and Cells
BC,BD, and BD has "-".
Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C
should start with a "1B", if Cell AB has "T" or "R", then its a "1F".

So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be
"BxxxxX014_xxxx_XXX" and Cells BB to BE have "â—‹"
or
If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be
"1FxxxxX014_xxxx_XXX" and Cells BB and BC have "â—‹", Cells BD and BE has "-".

For a different file with a simular setup, I was kindly given these formulas:

=IF(OR(AF445="",AX445=""),"",IF(AX445=1,IF(OR(AF445="A",AF445="V"),IF(AZ445="â—‹",IF(BA445="-","F","B"),IF(BA445="â—‹","I","X")),""),IF(AX445=1.1,IF(AND(AF445="A",AZ445="â—‹",BA445="-"),"1B",IF(AND(AF445="V",AZ445="â—‹",BA445="-"),"1F",IF(AND(AF445="A",AZ445="â—‹",BA446="â—‹"),"1I",IF(AND(AF445="V",AZ445="â—‹",BA445="â—‹"),"1X","")))))))

=IF(OR(AX445=1.1,AX445=1),"â—‹","")

=IF(AX445=1.1,"-",IF(AND(AX445=1,AF445="A"),"â—‹",IF(AND(AX445=1,AF445="V"),"-","")))

The Column letters are different, but the sheet format is fairly the same.
I hope I haven't confused or discouraged anyone from looking at this. Thank
you.

 

[Hell-fire]

Hi,

I was able to figure a few of the problems I had, but now have only one
thing that I need help with.
So, if anyone is willing, please help me.

With no values inputted for this row Cell C looks like this:

XxxxxX014_xxxx_XXX

When Cell AZ is 1.0, and Cell AB has "A", beginning character in Cell C
should start with a "B".
If Cell AB has "T" or "R", then the beginning character should be an "F".

Also when Cell AZ is 1.1, and Cell AB has "A", beginning character in Cell C
should start with a "1B".
If Cell AB has "T" or "R", then its a "1F".

So for example, Cell AF is "A", Cell AZ is "1.0", then Cell C should be
"BxxxxX014_xxxx_XXX"
or
If Cell AF has "T" and Cell AZ is "1.1", then Cell C should be
"1FxxxxX014_xxxx_XXX"

Thank you.

 

[Rick Rothstein \(MVP - VB\)]

Maybe this does what you are asking for?

=IF(AZ10="1.0",IF(AB10="A","B","X"),IF(AZ10="1.1",IF(AB10="A","1B",IF(OR(AB10="T",AB10="R"),"F","X"))))&"xxxxX014_xxxx_XXX"

Rick

 

[Max]

Think you could try this in C28:
=IF(OR(AZ28="",AB28=""),"",IF(AND(AZ28=1,AB28="A"),"B"&D28,IF(AND(AZ28=1,OR(AB28={"T","R"})),"F"&D28,IF(AND(AZ28=1.1,AB28="A"),"1B"&D28,IF(AND(AZ28=1.1,OR(AB28={"T","R"})),"1F"&D28,"")))))

I presumed that the conditional prefix (ie: B,F,1B,1F)
is to be concatenated with the string generated in D28,
and that nothing is to show in C28 if either AZ28 or AB28 is empty

It's easier/shorter to just use the ampersand operator: &
to concatenate cells or constants and cells,
instead of the longish CONCATENATE function.
(I used "&" liberally in the above, eg: ... "1F"&D28, ..)

Trust the above does it here for you. If it did help, could you kindly click
the "Yes" button at the bottom as the answer to the question: "Was this post
helpful to you?" before you post your reply to me from where you're reading
this in MS' webpage <g>. Thanks.

 

[Hell-fire]

Hi Rick,

It didn't quite work, but no worries, the solution Max gave me works. Thank
you for looking at my post.

 

[Hell-fire]

Hi Max,

Thanks again for helping me out. The formula works after I took out the D28
from it. Other than that, it is doing what its suppose to do.

Examining the formulas you gave me before has taught me a lot on how the
logic on how they work. Thanks again.

 

[Max]

welcome, Hell-fire.