Incrementing value by 1 from text in cells

Hi Guys,

I need your assistance. I have the following four rows of text in Exel from A1 to A4
 
WHEN LENGTH(TRIM(TRANSLATE(substr(re.FIELD1,2,6), ' +-.0123456789',' '))) is NULL AND u.sales_b = substr(re.FIELD1,8,2)
THEN substr(re.FIELD1,2,6)
WHEN LENGTH(TRIM(TRANSLATE(substr(re.FIELD1,2,6), ' +-.0123456789',' '))) is NOT NULL AND u.sales_b = substr(rtrim(re.FIELD1),-2)
THEN SUBSTR(re.FIELD1, 2, LENGTH(re.FIELD1) - 3)

I would like to increment the value of re.Field1 by 1 every time I drag the cells so it displays re.Field2, re.Field3, re.Field4 and so on but the formula below works only when displaying re.Field2 but after this I get disperse values rather than incrementing by 1. I have the next four rows of text from row A5 to A8.


="WHEN LENGTH(TRIM(TRANSLATE(substr(re.FIELD"& ROW()-ROW($A$4)+1 & ",2,6), ' +-.0123456789',' '))) is NULL AND u.sales_b = substr(re.FIELD"& ROW()-ROW($A$4)+1 & ",8,2) "
="THEN substr(re.FIELD"& ROW()-ROW($A$5)+1 &",2,6)"
="WHEN LENGTH(TRIM(TRANSLATE(substr(re.FIELD"&ROW()-ROW($A$6)+1 &",2,6), ' +-.0123456789',' '))) is NOT NULL AND u.sales_b = substr(rtrim(re.FIELD"& ROW()-ROW($A$6)+1 & "),-2)"
="THEN SUBSTR(re.FIELD"&ROW()-ROW($A$7)+1 &",2, LENGTH(re.FIELD"&ROW()-ROW($A$7)+1 &") - 3)"

Can anyone please help with this problem.


Thanks in advance.
jose11auAsked:
Who is Participating?
Advertise Here
 
Rgonzo1971Connect With a Mentor Commented:
Hi

You could replace

=ROW()-ROW($A$4) with

=ROUNDUP((ROW()-ROW($A$4))/4,0)

or maybe better
=ROUNDUP(ROW()/4,0)


Regards
0
 
jose11auAuthor Commented:
Thank you. This one work for me.

=ROUNDUP(ROW()/4,0)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.