Fill down variable data

[71Neb]

I have a column that includes two letters, EZ, then the date, followed by rack number, for labels.
These decrement down the column as such:
EZ12151736, EZ12151735, EZ12151734....
When the last two numbers reach 01, it starts over with the previous day:
EZ12141736, EZ12141735....
I have this done for a two week period.
I would like to be able to change the length of the sheet to a variable amount of days.
I can't do one week without either changing the entire sheet manually or throwing away 250 labels.
Is there a way to automate this, by entering a start date, and possibly number of racks per day?

 

[Joe4]

Welcome to the Board!

Will you two week time period ever cross months and years?
Are you open to a VBA solution?
If so, please let us know which cells the Start Date and Number of Racks Per Day values will be in, and what cell you would like to start this list.

 

[71Neb]

Thank you for the reply
This could/will cross over months and years. It wouldn't be difficult to avoid it. I could do a new one on the 1st.
VBA would be great. That is what I have been trying. I am "fairly" VBA competent.
I have to have the word Lot in A1. Then the list starts in A2.
I am just trying to simplify it for the person that is going to be using it, so they can punch in a date and repeat number, and go.

 

[71Neb]

Sorry, missed a question.
The start date will always be today(). Then it will count down however many days required. These can be anywhere on sheet 2.
I am going to put this input data on Sheet 2. Then copy the column to Sheet 1

 

[mopp1]

Assuming E1 contains start date, E2 is number of racks, E3 is starting row (in your case this would be 2), and E4 contains the Prefix (in your case EZ) - then you can use this formula and drag down as far as you like. If these parameters are in cells other than E1:E4 then change the formula to specify the correct locations.

=IF(ROW()<$E$3,"",$E$4& TEXT($E$1-INT((ROW()-$E$3+1-0.1)/($E$2)),"mmddyy")&TEXT($E$2-MOD(ROW()-$E$3,$E$2),"00"))

 

[Joe4]

mopp1's formula looks like it might do what you want (pretty cool formula!).

Let us know if that works for you, as I don't want to try creating a VBA solution if fact satisfies your needs.

 

[71Neb]

That works great. It is easy enough to drag down.
Thank you very much.

 

[71Neb]

One more question, if I may?
I have the input data on Sheet 2. The result is on sheet one. It works great.
I would like to automate the fill down process.
I assume that there is a way to fill down a set number of rows?
I want the user to input a number on sheet 2, and the formula on sheet 1 will fill down that many rows.

 

[71Neb]

I have this that works, but I need the Sheet1 A10 to be whatever is in Sheet2 C9. A2 won't change.

Sub FillDown()


Worksheets("Sheet1").Range("A2:A10").FillDown


End Sub

 

[71Neb]

One more question, if I may?
I have the input data on Sheet 2. The result is on sheet one. It works great.
I would like to automate the fill down process.
I assume that there is a way to fill down a set number of rows?
I want the user to input a number on sheet 2, and the formula on sheet 1 will fill down that many rows.