Incrementing Reference Numbers
Mar 11, 2009
I'm putting together a document where I'm trying to automatically create a reference number for each entry. The reference number needs to consist of a site ID (found in column A), an underscore, and then an incrementing number dependent on how many entries the reference number appears.
For example, in column A, the first time the site ID: 12345 appears, I'd like the reference number 12345_01 to be created. The second time it appears, the reference number would be 12345_02. And so on.
If possible, as illustrated above, the first nine references need to have a 0 before the number - i.e. 01, 02, 03, etc - when it hits the tenth instance the reference becomes 10, 11, 12, etc.
I've tried to solve this with COUNTIF, but cannot get it down.
View 2 Replies
ADVERTISEMENT
Aug 20, 2014
following issue:
The following table is given:
flower
20
rose
flower
21
rose
[Code] ........
Which needs to be turned into:
flower
20
22
rose
flower
31
32
blossom
tree
1
3
apple
The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.
View 4 Replies
View Related
Nov 25, 2008
I am having trouble with the following situation:
on the sheet PANEL in cell A2 I want to reference the information on sheet 480 in cell B5.
Then on sheet PANEL in cell A3 I want to reference the information on sheet 480 in cell B54.
'PANEL'A2 needs to equal '480'B5
'PANEL'A3 needs to equal '480'B54
And so on...
I need the reference to skip 49 cells each time so that I can just copy the formula on down the column.
View 6 Replies
View Related
Sep 7, 2012
Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example numbers 50,60,70,80 are put in cells A6 to D6 (horizontal)
What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6
View 1 Replies
View Related
Jun 6, 2008
Incrementing line numbers is a simple task.
If line Identification starts with
AA
AB
AC, why doesn't the same rules apply?
Highlight the cells then click and drag the corner down.
View 9 Replies
View Related
Feb 25, 2009
I currently have a project i am working on based on a invoice system and in my case it is to do with Van rentals. what i am looking to do is create a macro so that when the user clicks the order van button, it brings them to the order page and automatically creates an invoice number which is automatically added to the invoice database along with the customers details and the date of the invoice in a different sheet.
i have been browsing various forums and tutorial and have come across an a piece of code that is incrementing numbers each time the button is clicked. I can add the first set of details to the invoice database however i am unable to create a new row which copies the formulas and just change the invoice number as it is incremented - it just copies the same number
Now i am a complete novice in using VB and i have knowledge to an extent using excel. Is it possible to do that?
Sub test()
'
' test Macro
Sheets("Invoice List").Visible = True
Sheets("Customer Menu").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Customer Van Order Form").Visible = True
ActiveWindow.SmallScroll Down:=-12
Sheets("Invoice List").Select
ActiveCell.FormulaR1C1 = "='Customer Van Order Form'!R[7]C[6]:R[7]C[7]"
ActiveCell.FormulaR1C1 = "='Customer Van Order Form'!R[7]C[6]"
Range("B2").Select.............
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
Jun 26, 2008
I need to create a purchase order page whereby each item is then transposed into seperate sheets detailing the product along with price etc. I need the po number to be automatically generated to follow on from the previous therefore giving us a continuous po thread. I also need the purchase order as typed to be saved into a seperate sheet / file each time so that the po is always blank when someone else creates a new post
View 4 Replies
View Related
May 14, 2013
Below is the code I am working on. The code works, but I can't get page 2 to increase by 1.
HTML Code:
Sub Macro2()
'
'Marcro TestWorkbooks.Open Filename:= _
"S:financeReportingWORKING FOLDERTTCTest.xlsm" _
, UpdateLinks:=0
Sheets("help").Activate
If ActiveSheet.VPageBreaks.Count
View 9 Replies
View Related
Mar 30, 2009
In a formula, how can I reference the first value in a column of numbers when the row number of that first number is not constant [ie] the row number changes regularly from day to day as I insert new rows at the top of the column each day?
View 7 Replies
View Related
Jan 21, 2009
I have a spreadsheet with Company A's part numbers, then the number for the same exact part from their rival company or competitor. When searching on our website I need for the customer to be able to find the part even if we are out of stock of Company A's part, we can let them know we have company B's part and it is the exact same part. So I am cross referencing the numbers.
In my spreadsheet I have all of company A's parts in column A and their competitor parts in column B. In another column I have cross reference #'s I did with the concatenate function. Where it gets tricky is one part can have 50 or more numbers associated with it. So far, I have been doing the concatenate for each one. I haven't gotten very far as you can imagine. Is there a way I can have it check a column, then when it finds the same exact numbers have it put the numbers from column B all in the same row seperated by a space(my delimiter)? See image at link. I couldn't figure out how to post the spreadsheet. Maybe I am too new to attach files.
[url]
so in the example above, cell E11 has company A's part number, it's own number, then the number above it and the numbers below it. It has every number associated with 74679142.
View 9 Replies
View Related
Feb 3, 2014
I have exported a dataset with references numbers and I want to convert those reference numbers into the titles they refer to. I have a separate sheet that matches the reference number to the title and would normally use a LOOKUP function [=LOOKUP(A2,Key::Table 1::A,Title)] to do this but, in this instance, I have multiple reference numbers in a cell separated by commas. How can I get the function to lookup multiple numbers from the same cell?
View 1 Replies
View Related
May 18, 2014
I have attached an example spreadsheet which should be relatively intuitive over me trying to explain it.
I am wanting to extract all CJ numbers with the corresponding dates from Sheet 1, into Sheet 2. It has to factor in, engineer name (as stated on sheet 2), the Month (as stated in sheet 2 "May") with the "Attended site" date in sheet 1....
If you see the worksheet it should give you the whole picture with a fair amount of clarity.
Book1.xlsx
View 11 Replies
View Related
Jun 4, 2014
I managed to find this great bit of code some time ago and would now like to add to it. The code basically identifies any duplicates in column A and then lists the results in a message box,e.g.:
Duplicate Name: REF12345
Rows: 2,3
Works great for 1 or 2 duplicates but if there are many it becomes quite difficult to keep track. I'd like keep the code as is but add highlighting to the identified cells.
View 14 Replies
View Related
Jan 10, 2014
I have three sheets of data, each sheet has various columns of data, but within each sheet, column one contains a unique reference number (URN), which is a unique six digit number identifying an organisation.
In sheet two I need to create a new column and call this 'Filter Group'. In that column I want to insert a series of IF statements to return a value from all three sheets. The difficulty I'm having is that I can't get the if statements to work specifically for when the URN in sheet one or three match the URN in sheet two.
Sheet two is my main data sheet. Sheet one is called '2 Weeks Previous' and sheet three is called '2 Weeks ahead'. The IF statement I have created so far is as follows:
=IF('2 Weeks Previous'!H:H=1,"ENF grade 1",
IF('2 Weeks Previous'!H:H=2,"ENF grade 2",
IF('2 Weeks Previous'!H:H=3,"ENF grade 3",
IF('2 Weeks Previous'!H:H=4,"ENF grade 4",
IF('2 Weeks Ahead'!B:B<>"",'2 Weeks Ahead'!G:G,
IF('Background data'!H:H=2,"All-Good","NotRequired"))))))
View 8 Replies
View Related
Apr 5, 2006
I have a worksheet(Shed) and the range where numbers will be entered are B3:E46. In another sheet(Location) I have a in column A "Shift #", B "Shift Bus #" C "Location". what I am attempting to do is when a bus number is entered into Shed! ie.. b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and match it to Location! (column B) and from there provide the cell ref in column C. So I could then print Location! that would give me in Shift # order where each Bus # the shift is assigned to.
View 13 Replies
View Related
May 13, 2014
I have attached a screencap of an example source table that I am trying to write a macro to manipulate each day. Starting conditions are that I have data in column A and B. Column A would be reference numbers that may be on one line, or more likely, on multiple lines repeating with varying counts. Column B is the weight associated with each line. I would need a macro that could loop through column A, determine if the ref number has changed, and then calculate/populate column C. Column C is the % of the total weight for the unique reference number. I have illustrated this in the table using column D. This is taking me incredible manual effort to complete right now and I do know VBA, I just don't know how to loop a variable range in this case.
View 9 Replies
View Related
Nov 12, 2012
I have a document that is a subject index for book that has terms that reference page numbers. The structure is for example: Employment, 587, 592, 553, 605, 233 The term is always following by comma and then space and listing of page numbers that subject reference applies. In many cases the page number references are out of sequence and I need them to be in numerical sequence. i.e. Employment, 233, 553, 587, 592, 605 There are many subject terms in index and need to check and fix sequence of page references for each.
View 6 Replies
View Related
Nov 24, 2008
Im trying to create a catalogue for our complaints that come in each day at work. Ive created a basic layout I like, it uses 3 cells for a week day and 1 cell for a weekend day. The date is listed on the side of each day. I would now like to be able to quickly use this format for the rest of the month/year but need a way to do this easily as I cant do each day separately in the future. Ive tried selecting cells and then dragging down but all it does is repeat the previous 7 days and the same dates instead of incrementing the date.
Ive attached an image ...
View 2 Replies
View Related
Jan 10, 2010
Im using the following HLOOKUP formula : =HLOOKUP($B$14,$DA$15:$EH$380,2,FALSE) and I need to drag it down 365 times, but the row index num, ie 2 does not increment with the drag. Is this correct or is there a way of making the 2 increment?
View 3 Replies
View Related
Oct 1, 2008
If I'm in a worksheet and I spot a row I'd like to copy into my second worksheet, how do I arrange to have it increment the rows?
EX: searching through a worksheet, I see a suspicious entry. I want to copy it into another worksheet. I use the macro. I go back to the original worksheet and see another suspicious entry that I'd like to copy. I'd need a macro that would copy the active row from sheet one, and increment the rows on sheet two before pasting.
View 3 Replies
View Related
Dec 14, 2007
I create inventory cards and each needs a unique 4 character sequence number. Currently I have it set up with 4 cards per sheet. If I need to print more than 4 cards, the worksheet will print one sheet of 4, reset itself and increment the sequence number by 4 and continue this cycle until I've printed the number I need.
Currently I have it only using numbers. I start at 1000 and it increments, by 4, up to 9995. Once it reaches 9995 or higher, the increment resets to 1000 and starts over. The problem I am facing is I am now starting to get several cards that have the same "unique" sequence number. What I would like to do is change it from numerical sequencing to alphanumeric sequencing.
This is the code I currently use for the incrementing....
View 9 Replies
View Related
Nov 19, 2008
In one of my rows I am a referencing another sheet:
The formula is =Sheet2!e4. In the row below it I have =Sheet2!E8. Now is there a way to drag these formulas down so they keep incrementing by 4?
View 9 Replies
View Related
Jul 31, 2006
I've got above basic VBA knowledge and learning very quickly (largely due to great forums like this) I'm now creating a userform for the first time. I'm trying to create a New Supplier form and one of the fields is going to be for Supplier ID where it takes the first 6 letters of the Supplier name (entered by the user) & tags on '001' at the end. I've managed to do this but I would like some code to search existing supplier ID's ( Column A on separate sheet) and check whether the ID created already exists and if it does add on '002' instead of '001'.
SO for example..
If user creates a supplier called Microsoft the supplier ID will be Micros001 - this I can do, but if Micros001 already exists then it will generate an ID
Micros002, Micros003 and so on...
View 3 Replies
View Related
Oct 8, 2008
I have a column that I want to insert number 1 through ? incrementing by one. Can I do this simply without having to type in each one manually?
View 4 Replies
View Related
Sep 28, 2009
Is there a way to count how many times a value is changed?
Cell A1 can be either YES or NO. Its' value is controlled by validation
When Cell A1 is changed from YES to NO I want to increment the value in B1 by one.
When Cell A1 is changed from NO to YES I want to leave B1 as it is.
View 6 Replies
View Related
Feb 14, 2014
I have created below vba code but don't understand how to stop the auto incrementing.
[Code] ......
It end ups giving me see below.
Week 5+6
Week 5+7
Week 5+8
Week 5+9
Week 5+10
Week 5+11
And what I want is that it shows in all the lines “week 5+6”
Strange enough when I only enter a number like 5 it doesn’t auto increment.
And if I add a dot behind the 6 then either.
But I just want it AutoFill that what I have inserted in the box!
View 3 Replies
View Related
Mar 5, 2014
I have a piece of code that runs through multiple tables (they vary in length) and replacing values. All in the same worksheet
My code looks like this:
[Code] .....
How I get c to move to the next table and count the rows in that. In my code c stays constant, and I need it to change and match the new row count.
View 6 Replies
View Related
Jul 22, 2008
I have a spreadsheet which I need to remove all the blanks from, but instead of shifting cells up (I figured out how to do that in a macro), I want them to shift left.
I understand the basics of for...next loops etc, but I can't seem to understand how to make the selection in a range string variable. Not sure if this is a good explanation!
The code I have so far is....
View 10 Replies
View Related
Mar 16, 2009
I am running a macro where I pass it starting column and it processes the next 10 columns. How can I pass it "J" and have it increment K,L,M,N,O,P,...?
View 3 Replies
View Related
