Different Continuing Serial Numbers In Excel Depending On Day And Month?
Dec 10, 2012
I want to make continuing serial numbers in excel in order to print labels to be able to track my deliveries. i know i can drag the fill cursor to automatically make it go the next number. however i also want my serial number should represent the month and the day the invoice was printed.
for example i want the serial number should be
column a (Jan 01) column b (Jan 02 ) column c (Jan 03)
a1-01-01 a1-02-01 a1-03-01
a1-01-02 a1-02-02 a1-03-02
a1-01-03 a1-02-03 a1-03-02
The first number represents the month (example -a1 for Jan)
The second number represents the day and
The third number represents the invoice
How can i make that when i pul the cursor down it should continue with the invoice number
However when i continue right it should move the day number?
View 2 Replies
ADVERTISEMENT
Dec 23, 2008
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
View 4 Replies
View Related
Jan 14, 2009
I have a layout for printing as attached. This is a front carton label. Depends on the carton qty, I need to print many times of those pages whether changing the carton times before every print out. For instance I print from 1 to 550. Each time I modify the carton numbers and printing it, that means 250 print job! I want Excel prints all(between) pages with given first and last carton numbers , is there a way to do it?
View 4 Replies
View Related
Jan 13, 2007
That is what my spreadsheet looks like. I have about 130 in the sheet I'm working on now. A is my date cell, B is my total for that date, C is the cell I have made for the =Month(serial_number). The problem i'm having is how do I get it to not put a number in my "C" cell when there isn't a month present?
This sheet will change weekly, so I've went down to 300 with my month formula. When entering the formula in the cells that do not have the month, it automatically inserts a 1 that throws the count off for my entire sheet. How can I leave the code for my month in without having it add a number when there's no month?
View 5 Replies
View Related
Jan 22, 2009
I have a dynamic list of items which I have to assign serial numbers to.
If the serial numbers go in sequence, it would have been easy.
But the problem is, they don't.
They go from A1 - A9, B1 - B10, C1 - C10 ....
Is there a way I can assign an array to ensure that once the macro hits A9, it starts from B1, and once it hits B10, start from C1?
View 9 Replies
View Related
Jan 11, 2013
I have one page in excel to print. But I want to print it 1000 times and on each paper i need another serial number. How do I do this?
View 4 Replies
View Related
Oct 16, 2004
Sheet4 in my book looks like this:
.............A.......................B................................C..................
I......Invoice No…..…Starting Serial No……...……Ending Serial No
2…………ND12………..6011000000013652……. ..6011000000103652
3…………ND13………..6011000000116526……....6011000000246977
4…………RF4……….….6006000001255724……..…6006000011249564
5.........RF4...........6005000000023652.........6005000000095624
note that the last 4 numbers of the series are randomly generated for security reasons and are not part of the range of the the serial No’s
So Invoice ND = 10 consecutive numbers, Invoice ND13 = 14 and Invoice RF4 = 1000 and RF4(again) = 8
The list on this sheet is a mile long
What I need is a “search box” with two windows in it
In one box I will type a serial number and in the second box I will get the Invoice number
The challenge is that the serial number could be between Starting and Ending serial No’s thus if I were to type serial number
6006000001086598 in the first box the answer in the second box will be : RF4
View 9 Replies
View Related
Nov 20, 2008
I have in my sheet from C13 to C350 many serial numbers which I manually type them in. These serials are 5 digit numbers, and I was just wondering if there's a function or formula that will not allow duplicate numbers in that column.
View 9 Replies
View Related
Oct 3, 2013
I am trying to get Excel to write a series of incremented serial numbers to an external program.
I need 10 per line, then for the code to send ENTER and start writing on the next line. Following is my current code:
For i = 0 To qty - 1
SendKeys prefix_sn & next_sn + i & ","
Next i
View 6 Replies
View Related
Aug 4, 2014
I have a spreadsheet with a list of about 3800 lines of dollar values in "K" that correspond to various serial numbers in "E".
These serial numbers come up multiple times at different place throughout the 3800 lines.
I know by using SUMIF I can calculate the total value of each serial number, but is there a way to bring the serial numbers across to correspond to the sum of the dollar values?
View 5 Replies
View Related
Apr 16, 2014
I have repeating 7 names in A column.(Like a name group)
Each group names repeating after a blank(empty) row.
I want to write to B column for each repeating names from 1 to 7 as number value.
View 2 Replies
View Related
May 31, 2014
I have a userform for keeping records and would love to incorporate a new feature. I would want to generate a random serial number for each entry made with the userform.
View 2 Replies
View Related
Oct 10, 2008
I've modified this macro I found on this forum, all it does is compares 2 lists of 6 digit serial numbers and and then tells me which numbers are in list 1 that are not in list 2.
However, I want to modify it so that it also gives me the numbers in list 2 that are not in list 1 (put into column F). I then need it to cut the matching serial numbers (in columns A and B) from sheet 1 and paste them into sheet 2.
View 5 Replies
View Related
Jan 20, 2010
Put serial numbers against row like above either by the help of formula or either by VBA code.
SrDePNo.Truck No.Vehicle Type
16384LZR7793SM
46385TLC964C
56386LES7540T
66387LES4982T
76388LES 6509T
86389K4470T
96390LWC3070SM
-6391LWC3070SM
106392AJKA 7595T
116393RIC8879T
126394W1649T............
View 9 Replies
View Related
Feb 27, 2014
my spreadsheet has duplicate serial numbers in column A and the corresponding row in column B has mutliple descriptions for the same serial number. I need to combine those descriptions into 1 cell rather than having multiple rows for the same serial number. is there an IF formula that I can combine with a concatenate that will capture what I need?
View 1 Replies
View Related
May 26, 2006
i have a list of serial numbers in a column, say column a, for example, and i have corresponding data in columns to the right of this, say just column B for example- I also have a header on column B which counts the data in column b. (just a COUNTA, counting occurences of 'x' )
my issue is this: i need to limit the range of the COUNTA dynamically- by the number of serial numbers in column A. for example: if i have 10 serial numbers, i want my COUNTA range to be B1:B10 (excluding header) and... i need this to be a built-in formula, NOT a VBA macro. i need it to update automatically, as soon as another serial number is entered. i've tried using references to named ranges and all sorts of language tricks, and i cant seem to get it to work. (such as:
' =counta(b1:namedrange1)
i'm going to have to do the same thing with COUNTIF's, so if that is also easily explainable,
View 9 Replies
View Related
Oct 3, 2012
i Am trying to get this project underway and can attempt this manually in excel but will take ages. I was wondering if there is a code I can use. I am trying to get a 7 character alphanumeric serial number for units as a 4(alpha) and 3 numeric number. like say AAAA001 , i want to list on a row all the numbers on a sheet right upto ZZZZ999
View 5 Replies
View Related
Mar 4, 2009
I'm trying to get the pink cell in THIS sheet to match up with the correct MONTH'S TOTAL in the pink highlighted column in THIS sheet. i couldnt figure out how to explain it any clearer.
View 6 Replies
View Related
Oct 1, 2009
I have a spreadsheet with a year’s worth of data in. The columns are months (Jan, Feb, Mar etc) and the rows are people (A, B, C etc). There is a YTD column at the right.
I will be updating a cell at the top with the current month.
I want to be able to sum the YTD figures depending upon the date at the top. For example, the date at the top is Aug, so I want to sum Jan to Aug, if I change the date to Sep then I want to sum Jan to Sept. This way I will be able to automatically calculate YTD on a pre-populated spreadsheet, just by changing the date each month.
View 5 Replies
View Related
Oct 24, 2011
My data sheet has a Month column, and those months are simply numbers one to twelve. They are formatted as plain old numbers. The data is coming from somewhere outside Excel.
How the numbers look in the data sheet doesn't matter. But I use this data to generate pivots, wherein the months are the columns. I want it to say Jan, Feb, etc. across the top of the pivot.
I realize that I could just loop through the raw data and convert the numbers to text. However, I want the pivot table to recognize this data as dates, so that I can do date-related stuff, such as the date grouping described on p. 85 of Jelen's Pivot Table Data Crunching book.
If I go into the raw data and try to convert the cells to the mmm format, they all convert to January.
I'm aware that formatting doesn't translate from raw data to pivot table anyhow. But it's not clear to me how I can use the date field grouping functions without somehow showing Excel that these are dates. Or is the pivot table smart enough to realize that just by the names, even if they are formatted as text?
View 6 Replies
View Related
Feb 7, 2013
I need to convert a date into a serial number. The serial number must return the format DDDYYNN where DDD is the day of the year, YY is the year, and NN is the sequential build number. As an example, if 10 units are built on 2/6/2013, the serial numbers would be 0371301, 0371302 ...0371310. Cell A1 would contain the current date (2/6/13) in this example. Column B1 - B10 would be the sequential serial numbers generated by the formula.
View 3 Replies
View Related
Jun 3, 2014
Is there anyway that when I choose (for example, Jan) the result shows the sum of January numbers. On the other hand, if I choose Feb, the results would be the sum of February. See Mar on cell C8 and Feb on cell E15.
A
B
C
D
E
1
1st
2nd
3rd
2
Jan
1
2
3
[Code] .......
View 2 Replies
View Related
Dec 8, 2011
Excel 2010 O/S XP Macro for serial number input all serial numbers have JTC0. Any VBA code to place in macro to have a cursor appear after the four characters in the macro below?
Example: JTC0| < cursor ready for input
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+s
'
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "JTC0"
End Sub
View 4 Replies
View Related
Mar 26, 2014
I've just started working on an FTE calculator and wish to populate a 12 month calendar with FTE depending on the start and finshed dates.
FTE Calculator non nursing.xlsx
I've attached the file. In Column D the user would select the month the staff start and in the Column E the month the staff will finish. I would like the fte that is calculated in Column Z then to populate in the 12 columns AB:AM (Jul to Jun) with corresponding month start and finish.
View 2 Replies
View Related
Mar 25, 2009
I need to sum numbers depending on their source. Consider the following sources
Source .....................#
M D11 24 ......... 5
M D11 23 ......... 6
M D02 25 ........ 7
I want sum of all numbers whose source has D11 in its name (Answer 11). What's the best way of doing so?
View 5 Replies
View Related
Feb 16, 2012
I am trying to add a dynamic date rang to the heading of a table. I can find the earliest and latest dates, and want to concatenate them in the heading...
January 1 2012 to January 31 2012
The day and year (and the other bits) are easy, but is there an easy way to convert 1 to January, without going through a vlookup.
View 14 Replies
View Related
Sep 23, 2009
If Then Else not continuing. What is wrong with
View 5 Replies
View Related
Jul 11, 2008
From cell A1 to A10 I have the figure 5412587
In cell B1 I want the figure in Cell A1 to concatenate with the figure one but in cell B2 instead of figure one with figure two etc. etc.
So B1 will be 54125871, B2 54125872, B3 54125873 etc. etc.
the formula would be Concatenate(A1&1). I want the "one" in this formula to be increasing so in B2 will be Concatenate(B1&2) et.c etc.
The problem is that I dont want to add an extra column to replace the 1, 2, 3 etc. in the formula.
View 9 Replies
View Related
Jan 16, 2010
I have an Excel 2007 Workbook that is used to do a variety of different reports. Each spreadsheet within this Workbook is a different report. All reports need the same Header format that is linked with new data that is entered with each new report. The reason I need a header is because each report may vary in size from 1 page to 6 pages, it all depends on how much is reported on and a single completed report will use a combination of other reports.
Here below is a format that I am trying to achieve
(Linked Data) ‘Employee names located top center
Date: (Linked Data) ‘With Date: texted before link, located left index
Address: (Linked Data) ‘With Address: texted before link, located left index
Or should I just use
=Sheet1!A1:C1
On Sheet1 as source to other sheets, but if I do this how do I use this like a header for continuing pages
View 2 Replies
View Related
Jun 21, 2012
so on sheet 1 i have a list in column b (as below) and in column G I want to put a note "not included" if the cell matches what is in sheet 2:
AAA
BBB
CCC
DDD
EEE
On sheet 2, column A I have a list of items that I want to not include which I want to be able to add/remove/adjust over time. For example:
DDD
EEE
FFF
LLL
I can get it to put in the correct text into sheet 1 with the following but I don't know how to get it to look through all values on sheet 2 and then only enter the "not included" on sheet 1 if it is listed on sheet 2.
So far I have
Sub checksheet()
For Row = 1 To 428
For col = 2 To 256
[Code]....
This only does it for what is written in cell A3 on sheet 2 and does not keep going down the list in sheet 2.
I think I need to define the range better on sheet 2 and ask it to look through that, but im not too sure.
View 4 Replies
View Related
