Link to home
Start Free TrialLog in
Avatar of jose11au
jose11auFlag for Australia

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jose11au

ASKER

Thank you. This one work for me.

=ROUNDUP(ROW()/4,0)