Maybe a tricky one. Using A date and numerical range in the same cell.

[Bazlequin]

Hi All,
I hope you can help.

I have a form I'm trying to build.
I'll explain my numbering first before I get to the point, if I may?
The number I'm working on in the cell looks like this: 1701/001-010
The 1701 is my reference to it being the year 2017 and 01 is January. This will obviously change on a monthly and yearly basis.
Next comes my range of product 001-010. This is also likely to change on a daily basis. (The next one would be 1701/011-014 for example).

Let's say that that identifier (1701/001-010) is in Cell E11.
What I would then like to happen is that Cell A20 would show: 1701/01. A21 would show 1701/02, A22 , 1701/03 and so on.

The next part of the question would be: Because the ranges my differ between one and 25, I only want the number of cells to auto fill with the data that the range covers.
ie. if the range was 1701/001-003
Then I only want 3 cells to be filled. A20 would be 1701/001, A21 1701/002 & A22 1701/003. A23 be blank.
However if the range was 17001-004 then A23 would be filled and so on.
Is what I'm trying to do possible or is the '/' stopping me?

[davesexcel]

You can do lots of manipulating with =Concatenate()

For example:
Using today as the date or having the date in A2

=CONCATENATE(TEXT(TODAY(),"yy"),TEXT(TODAY(),"mm"),"/",TEXT(TEXT(TODAY(),"dd"),"000"))
=CONCATENATE(TEXT(A2,"yy"),TEXT(A2,"mm"),"/",TEXT(TEXT(A2,"dd"),"000"))

The other parts of the question may be better answered if you supplied a sample.

[Bazlequin]

The other parts of the question may be better answered if you supplied a sample.[/QUOTE]

How do I attach a file to my post?
I don't see any tool buttons that allow me to do that?

Additionally, I'm not needing to have the cell formatted as a date.
The Number in Cell E11 in my Example would be what I type in. No formula required there.

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Bazlequin]

Thanks.
File Attached

[JeteMc]

This proposed solution uses a couple of helper cells which may be hidden or moved for aesthetic purposes although they are not part of the form so that may not matter. The first helper cell will get the value of the first number in the range of product using the formula:

=VALUE(MID(E11,SEARCH("/",E11)+1,3))

The second helper cell will get the value of the last number in the range of product using the formula:

=VALUE(MID(E11,SEARCH("-",E11)+1,3))

The range A29:A52 is then populated by the formula:

=IF(G$11+1*(ROW(A1)-1)<=H$11,LEFT(E$11,5)&TEXT(G$11+1*(ROW(A1)-1),"000"),"")

Let us know if you have any questions.

[Bazlequin]

Thank you JeteMc.
Much Appreciated.
Baz

[JeteMc]

Here is another variation of the formula that could be pasted into A29 and copied down without using any 'helpers':

=IF(VALUE(MID(E$11,SEARCH("/",E$11)+1,3))+1*(ROW(A1)-1)<=VALUE(MID(E$11,SEARCH("-",E$11)+1,3)),LEFT(E$11,5)&TEXT(VALUE(MID(E$11,SEARCH("/",E$11)+1,3))+1*(ROW(A1)-1),"000"),"")

Let us know if you have any questions.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you found your first visit to Excel Forum beneficial and that you have a blessed day.

[Bazlequin]

Done.
Thanks Again.