Linking Partial Data - Macro
Nov 12, 2009
I have had much assistance with this complex (I think it's complex) macro in the past, however, there are still a few bugs that need to be worked out in order for it to be completely efficient. Any help would be really appreciated, and I'll do my best to explain what's going on at this point:
I have a worksheet called the 'Data Entry Page'. You will find it attached. Here are the requirements:
The PO Number cell will only allow ten digits with a dash, and will receive values such as 1200012345-1.
The Part Number cell will receive a value which will usually be six digits long with a dash, and will receive a value such as 654321-1.
The Identification Type cell will receive at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):
I
I I
I I I
IV
IA
I IA
I I IA
IVA
or (some combination examples):
IA & I I I
I I & I I IA
I VA & I IA
View 13 Replies
ADVERTISEMENT
Jan 5, 2009
I have two cells on Sheet 1 - Cell A1 and Cell A2.
Cell A1 is the first cell that will receive either a number, or nothing. When it receives a number, the number will always contain five digits. (For example: '23456'.)
The contents of Cell A2 depend on the contents of Cell A1 - If Cell A1 contains a five digit number, then Cell A2 will display that same five-digit number with '-1' following it. (In our case: '23456-1'.) If Cell A1 contains no number, then Cell A2 will just be another ordinary cell allowing the user to enter whatever he/she desires.
The extension in Cell A2 should remain editable at all times - if the user wishes to change the '-1' to a '-2', for example, he/she should be able to do so without any error messages appearing. However, the user must not be able to change any of the first five digits in Cell A2 as long as Cell A1 contains data. A message should appear stating that changes to the first five digits should be performed in Cell A1 - which would then change Cell A2 accordingly.
Also, if the contents of Cell A1 are erased for any reason, Cell A2 should keep the five digit number, but lose whatever '-x' extension it contained.
Can this be done using Data Validation?
View 14 Replies
View Related
Jan 6, 2009
The merged Cell B6:G6 will receive a ten-digit number followed by a dash and then one or more numbers. (For example: 1234567890-123)
Cell B15 will then receive data shortly afterwards. I already have a validation macro for this cell which allows either 'I' or 'I I I'.
Upon exiting Cell B15, merged Cell B16:H16 needs a macro which will check Cell B15 and if it contains 'I', Cell B16:H16 will display the data from the ten-digit number entered in Cell B6:G6 minus the first five digits. (For example: 67890-123)
Now the data in Cell B16:H16 can only be somewhat editable hereafter. It can be erased or replaced with numbers in smaller or greater digit combinations than five before the dash (i.e. 67890-123 can be replaced with 123456-7), and digits can be added after the whole group (i.e. 67890-123 & SEE DWG) without any error messages. But if any five-digit number with a dash and some numbers exist in Cell B16:H16, they must correspond with the number in Cell B6:G6 minus the first five digits.
However, if Cell B15 ever receives a 'I I I' afterwards, all data in Cell B16:H16 must be erased. Cell B16:H16 can never contain data if Cell B15 contains 'I I I'.
Also, if the data in Cell B6:G6 changes later on, the corresponding digits in Cell B16:H16 must change as well, even if there are digits after the whole group.
So here is an example of what a good macro would do for me: ...
View 14 Replies
View Related
Oct 21, 2008
I want to write an macro where it searches for text in a column, but the text may be a partial string. The text is in column B, with account numbers in column A, like this:
Column A Column B
1100 Jay
1101 Jack
1102 Jackson
1103 Jacksony
1104 Jefferson
For example, I want to search on Jack in column B. In the above example, I want it to find accounts 1101, 1102, and 1103, and to copy that information to another spreadsheet, say, sheet2.
If possible, I'd like it to copy the first account number and name, 1101 Jack, and if that's not what the user wants, they click next and they see 102 Jackson and so forth.
View 9 Replies
View Related
Apr 16, 2013
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
View 1 Replies
View Related
Apr 5, 2012
I am looking for a macro that will insert a new row and copies the formulas from the row above, but not from all the columns.
This is the current situation:
A
B
C
D
E
F
G
H
I
J
K
L
M
Name
Project Nr.
Work Order
hours
[Code] ......
What I want is that if column G contains "11374340" an empty row will be inserted above and the formulas from column E until M to be copied a row down. And the sum function in the last row of M needs to be changed automaticly as well (if possible).
I already have the macro to insert a new empty row which is as follows:
Sub InsertRows()
Dim i, LastRow
LastRow = Range("G" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "G").Value = "11374340" Then
Cells(i, "G").EntireRow.Insert
End If
Next
End Sub
View 4 Replies
View Related
Jan 23, 2013
I have a spreadsheet full of data and I need to extract only those lines of data which can be identified by the last three letters in a cell. I am adding an example spreadsheet which highlights the rows I am trying to extract based on the contents in column B but only where column B ends in KY.
Example data extract requirement.xlsx
View 5 Replies
View Related
Apr 9, 2009
I am looking for a macro to delete entire rows based on the a partial text contained in column A. For example, I have a list of names that I need to keep but I need to delete the rows in which column A contain cells starting with the following.
BOOKLET 201, PROMO
LOTTO, PRIZE 2
PBT 2, STW WEEK 4
the numbrs in these cells increment therefore I can not make it a specific to the cell but need to select based on Booklet, Lotto and PBT.
View 5 Replies
View Related
Mar 12, 2012
I have data below, what I am trying to do is to put an "x" if the data has a partial match. This is what I am using below.
HTMLSheet1 ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOL 2Electrical Magnet SchoolELECTRICAL 3Sports Magnet SchoolCOLUMBUS SCHOOL 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOL 6Abe Lincoln SchoolLINCOLN SCHOOL 7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOL 9Abe Lincoln SchoolLINCOLN SCHOOL 10Abe Lincoln SchoolLINCOLN SCHOOL Spreadsheet
[Code] ...........
What I would like is this
HTMLSheet1 ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOLx2Electrical Magnet SchoolELECTRICALx3Sports Magnet SchoolCOLUMBUS SCHOOL 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOLx6Abe Lincoln SchoolLINCOLN SCHOOLx7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOLx9Abe Lincoln SchoolLINCOLN SCHOOLx10Abe Lincoln SchoolLINCOLN SCHOOLxSpreadsheet FormulasCellFormulaC3=IFERROR(IF(SEARCH(A3,B3),"x",""),"")C7=IFERROR(IF(SEARCH(A7,B7),"x",""),"")
View 6 Replies
View Related
May 20, 2014
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
View 11 Replies
View Related
Jul 18, 2006
I need to match up the columns of 1 spreadsheet with those of another. I have a partial address that only lists city and state in one workbook. I need to match these partial addresses up with the master list that contains full addresses. It seems like it should be an easy thing to do, but I can't figure it out.
View 9 Replies
View Related
Jul 31, 2014
Is there a way I could achieve copying cell's content minus some characters?
For example in the cell A1 would be: "two apples"
I would put into the B1 cell something like =A1(-4charactersincludingspace) - the result in B1 should be "apples"
Is it understandable what I mean?
View 1 Replies
View Related
May 12, 2014
[Code] .....
My data are ABC:apple and im looking in a column where data are like these : apple, melon, lettuce.
View 1 Replies
View Related
Jun 2, 2006
I have set up data validation on a cell that produces a
drop down list of part numbers.
Is there a way that I can enable a partial part number search?
If I type in 354, all part numbers that start with 354 will be in the drop down list for me to choose from.
View 9 Replies
View Related
Sep 4, 2006
My database includes data about period (column A), a product code (column B), Turnover previous year (column C), Trend (column D) and Turnover this year (column E). In column C i want to have the turnovers from a particular product code from the previous year. The difficulty is:
1. that the product code can vary
2. not all periods are availabe.
How can i formulate a formulate that shows the turnover from the previous year, and in case there is no data from the previous year it should show "n.a.". This formula must also take into account that product code can vary. In my example: C3 should show 1296, C4 should show 877, C5 should show 884 and C6 should show 960. In case the turnover of period 200104 was not available C3 should show n.a.
View 3 Replies
View Related
Dec 18, 2013
I am trying to search partial txt with in data range.
View 4 Replies
View Related
Feb 19, 2008
I have data in columns a - d; the number of records can vary from a few to a huge number which (to me) makes the problem more complex.
I need a macro that will copy every 3rd record into rows e - h; and every 5th record into columns i - l, etc.;
View 9 Replies
View Related
Sep 16, 2006
is it possible to reference part of a data series from a chart to a combo box?
View 3 Replies
View Related
Apr 18, 2008
I have two worksheets. Both worksheets have two columns. Worksheet 1 column A looks like this:
1
4
7
8
Worksheet 1 column B looks like this:
x
x
x
x...................
What I'm looking for is a way to find that in worksheet 2 in column B a 'x' appears behind 1,4,7 and 8.
View 3 Replies
View Related
Dec 27, 2012
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
View 3 Replies
View Related
Jun 30, 2006
I have two questions:
a. Check the code below:
Dim news1 As String, news2 As String
news1 = "new"
news2 = "polygon"
Dim countie As Integer
For buddie = 2 To b1.UsedRange.Rows.Count
'If b1. Cells(buddie, 1).Value Like "new*polygon" Then
If b1.Cells(buddie, 1).Value Like news1*news2 Then
countie = countie + 1
n1.Cells(buddie, 10).Value = "test"
End If
Next buddie
I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet.
How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values.
b. If i have a value in the cell:
/new/blahblah/anycra/polygon
how do i split it so as to assign two variables to have 'new' and 'polygon'.
View 9 Replies
View Related
Mar 22, 2007
I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number.
Col A Col B
20567 3M Electrical, Inc.
I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both...
Col A Col B
20567 3M Electrical, Inc.
21789 3M Tape Division
Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?
View 9 Replies
View Related
Apr 6, 2007
I am a beginner with VB but i am wiz when it comes to forumlas but i have been learning how to do VB from your Forum. My Question: I have this code on two seperate option buttons
'First Memebership Option
Private Sub MemFeeFix1_Click()
[$F$7:$Q$7].Value = [$F$7]
End Sub
Private Sub MemFeeVar1_Click()
[$G$7:$Q$7].Value = "0"
End Sub
'Second Memebership Option
Private Sub MemFeeFix2_Click()
[$F$8:$Q$8].Value = [$F$8]
End Sub
Private Sub MemFeeVar2_Click()
[$G$8:$Q$8].Value = "0"
End Sub
Basically i created to buttons; one is a fixed button "MemFeeFix1" that will copy a value into 11 other cells because these values represent months in a year. Ie: Chamber Expense $12 so then i click the option button under the Fixed Column then it copies it into then next 12 months. I couldnt fit my worksheet as it is too big. Anyways i have another button "MemFeeVar1" in the Varable Column and if click variable then it places a "0" into the following 11 months.....
View 2 Replies
View Related
May 24, 2007
i deleted the macros i was using . just looking to start over.
what i want is for the button to brign up the graph when clicked and hide the graph when clicked again.
being such a newbie to this i'm just fumbling in the dark. with a bic lighter. that my companios keep blowing out cause they think its funny.
Frustration, confusion, headacke
View 9 Replies
View Related
Jan 10, 2014
I am trying to implement the idea of creating an application with two workbooks: a hidden code workbook (CodeWb) that holds all of the forms and macros, and a separate visible data workbook (DataWb) that uses the macros in CodeWb. The DataWb houses a little bit of VBA code to open the CodeWb, which then supplies the programming power to handle click events, etc., performed by the user in the DataWb.
I have advanced to the point of being able to open CodeWb from DataWb and trigger execution of a macro named "DataFileOpen" in CodeWb. This macro initializes things in DataWb and creates buttons there on a particular sheet, using the line:
Application.Run "CodeWb.xlsm!DataFileOpen"
So far so good. This works fine. My problem is that nothing happens when I click on the buttons created in DataWb, with their event handling code residing in CodeWb. Obviously, I'm missing something that maintains a connection between the two workbooks.
How can I use the code in CodeWb to handle all events occurring in DataWb?
View 2 Replies
View Related
Aug 18, 2014
I have a list of items in Column A and size info on Column B i want to being in the info to another sheet with a vlookup but the problem is that I have a lot of items with different colors so after the item no. it has a dash and a letter or two for the color so i want to bring into my new sheet all info.
I have in my old sheet for all items regardless of color so for instance in my old sheet i will BR1000-EM and then in my new sheet i will have BR1000-R and BR1000-SA and BR1000-YC how do i make a vlookup it should only lookup the values in both sheets only till the dash (i cant use a certain no. like left,6 because the item no can have more then 6 but it always has a dash when it has a color code) also not all items have dashes so the dash is not always there but when its there i would like that the lookup should stop by the dash.
View 6 Replies
View Related
Jul 16, 2014
You have two Workbooks open. Workbook 1 and Workbook 2.
You COPY all the data from Workbook 1 and PASTE the data as a 'Link' into Workbook 2.
You then save and close both Workbooks.
You then open Workbook 1 and edit the data. You save and close Workbook 1.
QUESTION
Will the linked data in Workbook 2 update automatically (i.e., without opening the file)?
View 4 Replies
View Related
Jul 28, 2014
I have a database sheet with a ticket list which has various columns like business area, ticket number, location, status etc. I am creating a new sheet where i want to highlight only the tickets that are in pending status and I dont want all the columns from the main sheet but just a few. How can I link it so its automatic.
View 4 Replies
View Related
Aug 18, 2014
I am trying to develop the best way to link two tables in two separate sheets in the same workbook in Excel. One table in the main sheet has a large amount of data in it, and the table in the second sheet collects selective date from the table in the main sheet. This makes it easier for the user to view the more important data from the main table. I developed IF functions which display the data from the main table into the new simplified table. The simplified table takes the data of each row from the main table in row order (row 1, row 2, row 3, etc.). Now the issue I am running against is that when I insert a new row within the main table, the second table does not see the newly inserted row.
Here is an example:
Say that both tables are sychronized upto row 40, and I decide to insert a new row with new data after row 25 in the main table. The simplified table will not see the new row because it is already past row 25, and the next row it will recognize from the main table will be row 41. Is there a way to have a VBA macro link the amount of rows in the two tables and have it so the simplified table will even display the data for the new rows in the main table?
View 8 Replies
View Related
Sep 2, 2009
A B C
1Timber$200A-rating
2Metal$500C-rating
3plastic$100B-rating
A1:A3 was input into combo box list. If I select an option in combo box, how can I make B11 and B12 to update data accordingly? (ie, if Metal was selected, B11 should show $500 and B12 should show C-rating)
View 4 Replies
View Related
