Numerical Sequence with different conditions

Hallo,

I´m still new in this forum and using Excel too.

I have solved a problem with numeric sequeces here in this forum one already, but I want to improve it, as I now have a workbook that I can attach.

There are different conditons for column A, B and C, which I want to make "smarter".

Column A: Should be a numerical sequence that skipps a cell always at the second cell of the beginning of a new block
Column B: Should repeat the same number, while being in the same block, than add 1 to the next block. The solution used until now is to count the blanks, so I extend the formula until the and and skroll back erasing the cell I want manually, so that the sequence is right.
Column C: Should be such as column B, but skipping certain blocks, those named "Sprechen Sie jetzt" and "Rollenspiel".

Can someone help me? Ill attach the workbook, there the one willing to helpo me probabely will be able to recognize the pattern.

Thanks a lot

Please!!!!

Hello again Can column D be used to determine where changes must occur? That is, if you enter a value in column D, columns A,B, and C will automatically adjust?

Hallo again.
Column D cannot be used unfortunately. It is no reference. The lenght of each block is not fixed and depends on the content. There is not really a pattern on the other columns but A, B and C. Thats the problem, it would be much easier if there was somewhere to refer on.

Perhaps this:
A5 =IF(B4="","","WB"&TEXT(AGGREGATE(14,6,--RIGHT(A$4:A4,3),1)+1,"000"))
B5 =IF(AND(F5=F6,F5="Narradora principal"),"","WB_BL"&C4)
C5 =C4+(B5="")

The problem is I have sheets with "WB" and other without. Those with it, are easier to work on, because they are much shorter. If we consider only those without "WB", what would you suggest?

If the number is always the LAST 3 characters of the column A codes, I can read the "prefix" off of your initial values.

Again, if initial values are on row 4:
A5 =IF(B4="","",LEFT(A$4,LEN(A$4)-3)&TEXT(AGGREGATE(14,6,--RIGHT(A$4:A4,3),1)+1,"000"))
B5 =IF(AND(F5=F6,F5="Narradora principal"),"",LEFT(A$4,LEN(A$4)-3)&"_BL"&C4)
C5 =C4+(B5="")

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

Nice, it worked perfectly on Excel. Buuut, there is a new problem. Unfortunately, Google Spreadsheet does not recognize AGGREGATE function. What do I do? is there another way to solve this?

Now it worked nicely. Though I have a last problem with column C.

If I want to skip all the block that is named "Sprechen Sie jetzt" OR "Rollenspiel", how can I do it? I don´t want to associate the value in column C with a value on Column B, because as soon as I have the first block skipped, the values on column B and C in the same row don´t match anymore.
I tried =IF(C4+B5=""),G5="Sprechen Sie jetzt"OR"Rollenspiel",""), but obiously it didn´t work, I guess because I have to count than instead of using values in C and B.

The rest is just perfect now!

OK, slight change- Initial values FOR ALL 3 COLUMNS now required on row 3. These can be ENDING values from previous sheet OR properly formatted codes with numeric parts set to zero.
1) Each column now increments its OWN COUNT VALUE instead of sharing.
2) Column C now suspends increment and shows blanks for excluded titles.

A4 =IF(B3="","",LEFT(A$3,LEN(A$3)-3)&TEXT(MAX(INDEX(--RIGHT("000"&A$3:A3,3),0))+1,"000"))

B4 =IF(F4="Narradora principal",IF(F4=F5,"",LEFT(B$3,FIND("BL",B$3)+1)&MAX(INDEX(--(0&MID(B$3:B3,FIND("BL",B$3)+2,4)),))+1),B3)

C4 =IF(B4="",IF(OR(G5="Sprechen Sie jetzt",G5="Rollenspiel"),"",MAX(C3:C$3)+1),C3)

Row\Col	A	B	C	D	E	F	G
1	PEN COD.	COD. B	CD	LEC	PAG	LOCUTOR
2						Inserção:	(VINHETA)
3	WB052	WB_BL5	5	<--Initial Values		Inserção:	WIZ_DE_4
4	WB053		6	121	2	Narradora principal	Lektion 121
5		WB_BL6	6			Narradora principal	Richtig oder falsch
6	WB054	WB_BL6	6			Narrador 1
7	WB055		7	122	3	Narradora principal	Lektion 122
8		WB_BL7	7			Narradora principal	Richtig oder falsch
9	WB056	WB_BL7	7			Narrador 1
10	WB057	WB_BL7	7			Narradora 1

Perfect perfect perfect. Thank you very much.

Could you teach me one last thing? Is there a waz to enter a value in a cell and display another value? or blank it?
So if I enter the values for row 3 I can hide them on column A and B and on cloumn C I can display the actual value + 1?

I thought of using a hidden row, say, hide row 3 and insert another row, so I can display on row 2 and the new row (which I think that should be row 4 than) the same value as in C5.

Sorry, I don't understand. Please attach a workbook with simulated results HIGHLIGHTED to show what you mean. Include a description, too.
(This is always the best way to get a quick, accurate answer.)

Never mind. I simply added the reference row and than I hid it.

I worked perfectly. Thank you very much!

You're most welcome! If complete, please mark your thread as SOLVED (Thread Tools up top). Thanks -Lee

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee