Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-13-2018, 03:00 AM
anakonda93 anakonda93 is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2018
Posts: 2
anakonda93 is on a distinguished road
Default How to get Excel to automatically continue a pattern which is form as 001-01-01 ....003-03-03

Hello all!

Can anyone help me and tell how i will get the excel to automatically continue and fulfill mine pattern?

The main idea is that i have to start from 001-01-01

The last value (on the right side) varies from 01 to 03.

Example:After 001-01-03 follows 001-02-01 and after 001-02-03 comes 001-03-01 and 001-03-02....



The second value (in the middle) haves values from 01 to 21. After it reaches 21 the firs number will grow by one and the second value drops to 01.

Example: 001-21-03 after comes 002-01-01 and then 002-01-02....

The first value (left side) can have values to for example 1-100

Example: 100-01-01....100-01-02.

Any ideas?
p.s i have excel 2016
Reply With Quote
  #2  
Old 04-13-2018, 04:15 AM
p45cal p45cal is offline Windows 10 Office 2010 32bit
Competent Performer
 
Join Date: Apr 2014
Posts: 124
p45cal is on a distinguished road
Default

There should be something you can work with at http://www.msofficeforums.com/excel/...nce-excel.html
Reply With Quote
  #3  
Old 04-14-2018, 07:58 PM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 616
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

I don't know about Excel's pattern abilities. I thought at first I'd do it with IF statements, but the more I tested that the more complicated it got. Instead I treated it as a base-number calculation, using ROW() as the sequential number and calculating each of the three columns. See the attached x.xlsx.

I'm used to R1C1 notation, but most folks prefer A1 so I'll give it a try despite its limitations. Columns X, Y and Z calculate the value of each position—except to make the calculation simpler they go from 0 to 2, 0 to 20 and 0 to 99. Then U, V and W add 1 to each value and convert it to a character string with leading zeroes. In column A you concatenate the three values together with hyphens. You can skip U-V-W and do the conversion and concatenation in column A if you'd rather.
Attached Files
File Type: xlsx x.xlsx (29.9 KB, 5 views)
Reply With Quote
  #4  
Old 04-16-2018, 12:01 AM
anakonda93 anakonda93 is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2018
Posts: 2
anakonda93 is on a distinguished road
Default

Hi,

Many thanks for replies. Bob you solved my problem.

Thank you!
Reply With Quote
Reply

Tags
excel 2016

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Outlook 2013 Forms (how to questions)- Quick opening of a form file & Linking form's data to Excel gamin2407 Outlook 0 01-21-2017 10:14 PM
Check box form field automatically checked Eduardo Care Word 8 09-07-2015 03:31 PM
Trouble automatically running user form Lostinvba Word VBA 3 11-23-2013 07:22 PM
Automatically generating paragraphs with form data mkellar Word 1 03-22-2013 10:10 PM
Form field to automatically be added to header? razberri Word VBA 3 02-22-2010 03:48 PM


All times are GMT -7. The time now is 06:21 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft