Solved

Incrementing value by 1 from text in cells

Posted on 2012-12-27
2
251 Views
Last Modified: 2012-12-27
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.
0
Comment
Question by:jose11au
2 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 38723049
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
 

Author Comment

by:jose11au
ID: 38725438
Thank you. This one work for me.

=ROUNDUP(ROW()/4,0)
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question