Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Create a successive numerical list for a range of numbers

1

I would like to know how to create a numerical list that encompasses a range of numbers that will be specified in a cell

For example I have 50 plants that need to be named numerically starting with the number 320.

I want them to look like this

CR0240.320

CR0240.321

...all the way until I have 50 plants named from CR0240.320-CR0240.369

This is how the data will look in my spreadsheet.

607 CR0240 (prefix needed) 50 (how many I need)  320 (starting number)    

Is this possible with a simple formula?

excel
MS Office Version: 2016
posted by: Tracy (rep: 2) on Thu Dec 14, 2017 at 5:42 pm
Answer
Discuss

Answers

0

Please write this formula in row 607.

="CR0240."&TEXT(ROW()-607 + 320,"000")
Then copy down for as many rows as you require.
  1. Change "CR0240." to anything you like, but observe the period at the end.
  2. "607" is the number of the row where you write the first formula. It remains constant as you copy up or down.
  3. "320" is the number you wish to see in row 607. As you see, it is added to the ROW() number and remains therefore constant.
  4. "000" is the format of the number. If the number is 3 (instead of 320) the output will be "CR0240.003". If the number is 1320, the output will be "CR0240.1320".
posted by: Variatus (rep: 4889) on Thu Dec 14, 2017 at 11:38 pm
Discuss
0

Hello, there!

I'm a learner here at grass root level trying just simple things. So, I might have followed the following steps:

1. Cell A1: typing whatever prefix needed.

2. Cell A2: typing number needed (e.g 50 in question).

3. Cell A3 to A53 (as per need): suffix

Finally, in a necessary cell typing the following formula and  dragging it down:

=$a$1&$a$2&a3

If no 50,

=$a$1&a3

Thank you!

(Sorry if I've not understood the question.)

posted by: Chhabi Acharya (rep: 111) on Fri Dec 15, 2017 at 9:56 pm
Discuss

Discussion

What you call a "suffix", actually is a counter. So, if you have the prefix in A1 and the number from which to start counting in A2 you still need an incremental number in each row to add to the start number. In the series of Start number and up, the first number is StartNumer + 0, the next one StartNumber + 1, etc.
If you solve the problem of how to count you may not want A2 at all, depending upon how you manage to count.
Variatus (rep: 4889) Dec 18, '17 at 6:16 am
The question wants a result like this at the beginning:

CR0240.320

But at the end, it seems:

607 CR0240.50.320

So, I took 607 CR0240 as a suffix to be followed by a fix value 50 and, finally incremental number 320 (copied from column A, beginning from A3).

I think, ="suffix"&"50"&... can do but keeping them in a cell facilitates a quick change.

The file attatched later on may clarify what I mean.

Thank you!
Chhabi Acharya (rep: 111) Dec 18, '17 at 9:35 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login