Solved

Incrementing value by 1 from text in cells

Posted on 2012-12-27
2
238 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

948 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now