Sorry, I forgot Rule #6:

6) "CONT" is manually input into col C only if suffix of col B is "A". I don't want "CONT" automatically generated in col C, it has to be input in only one instance on the entire worksheet.

On the spreadsheet, I don't understand:
"and green D row shall be inverse of whateved non-zero sequence 1 to 2 or anything up to 1 to 5"

Also you seem to have your rows and columns reversed in some of the rules.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Sorry, this one is improved.

• http://files.engineering.com/getfile.aspx?folder=b0fbd0a8-0a1c-4bde-81d8-54

ps

The numbers in col D and col E are for indexing, looking up data elsewhere.

ps #2 -

Don't spend any more time on this, I started a fresh approach and am making some headway. It may be very inelegant but it seems to be the right direction.

Just don't waste any more time on this - until I notify otherwise.

Well, all was for naught. I am getting absolutely nowhere. If no one can find the solution to this, I am giving up on this particular functionality in my workbook.

Something like this, perhaps?

D28: =IF(col_c<>"",1,IF(D27<>0,IF(AND("A"<=RIGHT(col_b,1),RIGHT(col_b,1)<"F"),CODE(RIGHT(col_b,1))-CODE("A")+1,0),0))
E28: =IF(col_c<>"",MAX(col_d),IF(E27>0,E27-1,0))

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

oh, btw, I assigned col_d to the entire column, so the example that starts in E28 will actually pull the max from the subsequent example. If you plan on having more than one set of stuff in the same column, you'll need to assign unique ranges.

My markup: http://files.engineering.com/getfile.aspx?folder=e...

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

IR,

Wow, I thought I knew the basics but evidently I have much to learn. That was much more efficient...and I just modified it slightly.

See attached, for the record.

• http://files.engineering.com/getfile.aspx?folder=8ec62c1a-c5bf-4082-b528-ec

IR, I modified "Book15" before receiving and reading your post 1:53 but I believe it works.

Thanks!!!

cool; oddly, I wasn't going to try and solve it, but I was procrastinating on something else

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

IR, thanks again.

At first when I tried to use the info you gave me from your first post 1:48, nothing worked, almost like the cell formula was text instead. Then I tweaked a couple of things and it worked, altho a very slightly concept from what you gave me.

I wasn't familiar with that CODE function and the syntaxes of many things in those formulas were new to me.

Then later when you sent that 1:53 update, that worked too, "right out of the box".