Filtering In Dropdowns With A Prefix
Feb 20, 2010
I'm not a novice but this might be something simple I didn't realize. I am creating a quoting sheet for a steel fab company. I have created dopdowns for the different materials and sizes but the dropdown is way too big. I need to have a column that helps me filter the dropdown more so I don't have to scroll through litterally hundreds of steels to find the one I need. The good news is that most steels have prefixes that make them easier to filter.
For example:
L-3x3x1/2
L-2x2-1/2x1/4
C6@8.2#
C8@11.5#
W12@16.0#
W21@44.0#
What I'm looking for is a way to have a dropdown that has all of the prefixes (L, C, MC, W, PL, etc) in it and when I select one of them the next column (the actual description of the steel as shown above) will only give me the specific steels for that steel shape (L,C,W,etc)
Currently I'm putting one row of say the MC shapes and one row of the C shapes and one row of the L shapes then copying them as needed. It's a lot of work and you can easily make a mistake.
View 12 Replies
ADVERTISEMENT
Jan 23, 2010
UDC 01_001 template black.jpgBatch 01_385 template white.jpg
UDC 01_001 template blue.jpgBatch 01_385 template blue.jpg
UDC 01_001 template grey.jpgBatch 01_385 template grey.jpg
UDC 01_001 template pink.jpgBatch 01_385 template pink.jpg
UDC 01_001 template white.jpgBatch 01_385 template black.jpg
I have 100s of urls with prefix as the left column. I need to rename them to the right column format. UDC 01_001 will take number Batch 01_385, UDC 01_002 will use Batch 01_386 and so on.
I cant figure out how to do this in one quick macro instead of replace function.
View 7 Replies
View Related
Dec 12, 2013
G1 = will be one of these 12 choices: 01 January or 02 February or 03 March...or 12 December
K2 = Date I performed the task
D2 = Name of Inspector
I usually manually input on G2 the name of the report.
Example:
G1 = 12 December
K2 = 12/05/2013
D2 = Chris Mart
Therefore, I need to manually input on D2 = 13-12-MARTCL
Where....
MARTCL comes from a table where I have all the inspectors and their corresponding username.
That table is O2:P63. O1= Title of row (Username), P1 = Title of row (Name of inspector)
13 = year extracted from 12/05/2013
12 = Month of inspection (which can be extracted from G1 or K2)
How do I use cell reference AND VLOOKUP to create 13-12-MARTCL on G2?
View 8 Replies
View Related
Oct 25, 2008
I have a colummn of data (text data). For my purposes I would need to add a prefix text to every single cell contained in that column; for instance, if I have a cell with a text "hellohowareyou" ,
I would need to change it by adding to it a constant prefixation text "es_ES@" so that the final result would be displayed like "es_ES@hellohowareyou" , and so on for the rest of the cells under the same column.
View 6 Replies
View Related
Jan 3, 2009
Say I have a workbook called test1.xls, where the number 1 at the end of the file name is variable. Tomorrow that character could be 2 so the file would become test2.xls etc (or could even be a letter for what it matters).
Is there a way in Visual Basic to recognize that the final character is not fixed but can get different values each time? Or in other words to recognize that the file starts with "test", and that whatever follows that prefix is not relevant?
For example....
If ActiveWorkbook.name = "'test*.xls" Then...
where * (star) could be any character.
As a Windows analogy, I am thinking of the * (star) character when one searches a certain directory for, say, Excel files (*.xls etc). Obviously, in VBE that doesn't work, and the star is just a character like any other.
View 3 Replies
View Related
Jul 1, 2006
I’m making a database to forklifts. Most used code I have found from this forum. I haven’t found a way how to copy existing formulas on summary sheet so that sheet name is also changed in formula. I attached also my workbook, but it’s not in English language but in Estonian. I hope that isn’t problem.
If pressed button on summary sheet (“Pealeht”) then existing blank sheet are copied and renamed. On userform1 is textbox3 which value added to summary sheet, also combobox4 value added to summary sheet. Now I want that if pressed commandButton1 on userform1 then all formulas what exist on summary sheet from F6 to CW6 are also copied to next row and in formula sheet name changed. My second question is what to write that if new sheet created then it goes always to end of existing sheets. Right now I’m using code what copies new sheet after blank sheet but it’s not satisfying me.
View 5 Replies
View Related
Oct 5, 2013
I have a drop down in IE in which four values are there
I will need to select each one at at time to make some change and move to next dropdown
the dropdown in IE should ideally have 4 dropdowns 01,02,03 and 04
However due to vendor errors we may have any of the above missing from dropdown or extra orderpoints in the dropdown like 05
IE.document.getElementById("vendororderpoint").value ="01" is the code to select order point 01
I need an alert in excel if any of the 4 dropdowns is missing.
View 1 Replies
View Related
Feb 13, 2009
I am trying to create a form with dropdowns (see attached file), so that when a particular company is selected in the first drop down, the second and third only display the relevant business units and departments respectively.
The INDIRECT function has confused me and I am not sure how to use it.
View 4 Replies
View Related
Dec 30, 2009
I am trying to wrap my brain around dependant dropdowns and have hit a brick wall.
Using the Contextures web site [url="http://www.contextures.com/xlDataVal02.html#TwoWord"] I can get part of the problem to work but when I try to add a third dropdown i get lost. Attached is a sample of the workbook.
View 5 Replies
View Related
Nov 23, 2008
i have a cell "h23" that must always start with a "j" or a "J"
the trouble is some of my users are only putting in the number
ie 2345 when it should be j2345 or J2345
View 3 Replies
View Related
Feb 11, 2009
I have a table like below
A B
1 x1234 value
2 y1234 value
If I want to look for all values that start with "x", is there a way to do it within a vlookup formula? Something like: vlookup("x*", A1:B2, 2, False). I know there's other ways to do this, but I want it all contained in one formula instead of splitting the A column using LEFT(A1, 1).
View 4 Replies
View Related
Apr 9, 2009
I read the thread below on how to utilize the Subsitute function to remove periods and thought about being able to use it for this. However, I have some phone #'s in my list that contain multiple 1- scenarios in them because the area code or 3-digit prefix sometimes include a 1- also. How do I make the formula only look at the 1- for long distance and not any other 1- found in the phone #? I want to remove all of the 1- for long distance because we are trying to use a new autodialer that is pre-programmed with the 1-.
View 8 Replies
View Related
Mar 21, 2013
VBA code to add prefix "HR" in cell H11. So no matter what I type in cell H11, for example 123456 when I hit enter I would like that in that same cell stands HR123456.
View 4 Replies
View Related
Nov 7, 2009
I have several excel workbooks with many worksheets (over 500 in some). Around a third of these worksheets are named "Exp1", "Exp2", "Exp3", etc.
I would like a macro that will delete all worksheets that do not have names starting with the letters "Exp". I do not want any confirmation dialogue, and as the workbooks vary in size, I would like it to finish when there are no sheets left (except the Exp ones of course).
View 3 Replies
View Related
Jan 11, 2007
I have often used Data Validation List to create selection lists for cells in a worksheet. The problem is the list has to be on the same sheet you want to use it on. Is there any other way to do this so one common list can be used for all sheets? I have a list of accounts I want to use on 12 different Monthly tabs.
View 5 Replies
View Related
Oct 19, 2007
for a small online database I have a column that lists nationalities:
French
Spanish
American
Latin American
(etc etc)
I need to add a prefix to all nationalities, for example:
Artist Nationality///French
Artist Nationality///Spanish
Artist Nationality///American
Artist Nationality///Latin American
(etc etc)
Is there a way I can select the 700 cells and do this in one shot? I can't add formulas because I will have to paste all this in text pad and then upload it.
View 5 Replies
View Related
Dec 7, 2007
I want to add "CR" to the beginning of every cell that already has data. I know I could do =a1 and it would copy all the way down. However each cell has different data and I'd like to keep it that way but add 2 letters in the beginning.
View 3 Replies
View Related
Sep 5, 2013
I have
Userform
Textbox1
Textbox2
textbox1 containing the province
textbox2 containing the city
If didn't click any value of province the city textbox2 don't have any value and if i click the one of the province the city will now have a value . Is that possible
Here is the picture : [URL] .....
View 6 Replies
View Related
Dec 7, 2011
I have two dropdowns, both use a different source lists and both use the same reference cell to deliver the result, My problem is I need the contents of the cell to be deleted and the new result inserted. At the moment if the cell is already filled the new result is put in the next column.
What I would like to happen is to have two dropdowns one above the other (B2 and B4) and the result in a cell below them in B6, which ever dropdown is used the result will fill the cell B6 this cell is then used for vlookup.
View 2 Replies
View Related
Jun 10, 2013
I have this code:
Code:
Sub comments1()
With ActiveSheet
With .Rows("180:186")
.Hidden = Not .Hidden
End With
[code].....
Now, when I run this code, sometimes (but oddly not every time) the dropdowns do not reappear where they were originally placed.
View 1 Replies
View Related
Jan 16, 2009
Biz develop an xls tab that will have dropdowns.
Essentially, one dropdown will filter another dropdown... etc...
Would also want a DISTINCT list for all dropdowns...
I've used the DropDown's from the CONTROL toolbar and although it seems easy enough to supply a datasource, not sure how filter/distinct might work.
View 9 Replies
View Related
Mar 8, 2007
I have a project that has a variable number of worksheets, dependant on the number of models in the job. These worksheets usually only use a single page, but may use two pages if the model is large (not often the case though). I have a "Template1" worksheet that is copied as a whole, to equal the number of models.
To minimize the file size that is generated when copying out the "Template1" worksheet, I have put the template of the second page on a separate worksheet "Template2". Now the initial copy is only of a single page, and the second page is only copied into the new worksheet if needed.
Sub AddPg2()
Dim CrntPg As String
CrntPg = ThisWorkbook.ActiveSheet. Name
Application. ScreenUpdating = False
ThisWorkbook.Worksheets("Template2").Visible = True
Worksheets("Template2").Activate
ActiveSheet.Range("A47:T96").Select
Selection.Copy
Worksheets(CrntPg).Activate
ActiveSheet.Range("A47").Select
ActiveSheet.Paste
ActiveSheet.Range("D58").Select
ThisWorkbook.Worksheets("Template2").Visible = False
Application.ScreenUpdating = True
End Sub
If I do this process manually, I toggle DesignMode "on", and it works, but I if I record the process, toggling DesignMode does not show in the code. how to include the comboboxes and checkboxes in the copy/paste process.
View 8 Replies
View Related
Jun 3, 2014
I have lists of numbers in column A. Based on the number in the cell, I would like to insert RO_ or RP_ before the number. I currently have two buttons for this, one named RO and the other RP and have simply recorded a macro of what I wish to do. However, instead of inserting RO_ or RP_ before the number the macro replaces the hole lot with "RP_10" or "RO_10" as this was the cell i recorded the macro on. How do I make it so it just inserts the Prefix rather than replace the contents of the cell.
Currently my code for one of the buttons is as follows;
[Code] .....
View 1 Replies
View Related
Aug 21, 2009
I need to extract (and then use for SumIfs) only item numbers from the long description. Please see the attached list where item number column shows existing list & next column shows what i want to extract. The exrtacted part if has any trailing or succeeding letters, characters between numbers should stay. for example from "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034" I need to extract " SV9176001/232034" or from " SPICES:BULK SPICES 7100:9054B" I need to extract " 7100:9054B". Can some one please urgently help me on this.
View 9 Replies
View Related
May 24, 2012
Is it possible to create a vlookup but when it look in the table to ignore the letter i have prefixing.
=VLOOKUP(H8,'[1-90 week summary 08-01-10.xls]Sheet1'!$E$2:$F$501,2,TRUE)
H8 contains numbers ie 237 but the Table contains S237. what can i do so the lookup ignores the "S".
View 7 Replies
View Related
May 25, 2012
I need to add a specific prefix (in this case DR- ) to a whole column. The problem is I have some cell that already have the prefix while others don't. I also have some cell with value N/A and I don't want them to get the prefix either
PHP Code:
___C___
DR-1220Â
1222
 1233H
DR-1220Â
1222
 1233H
[Code] ......
What I need them to be is :
___C___
DR-1220
DR-1222
DR-1233H
DR-1220Â
DR-1222
DR-1233H
[Code] ....
The text need to be search able (no formula ).
View 6 Replies
View Related
Mar 2, 2009
Im basicaly pulling a report wherein the details will have a column as number.
It basicaly should be a three digit number. The tool will have the information as below.
VNO005016017008019020021
But when im pulling out the report into excel, the zeros in the prefix goes away and the report looks like the below
VNO516178192021
I just checked the tools previllages and it seems that we cannot do anyting in pulling the data as it is in the tool.
So wht im trying to do is to save the report in a folder where i have placed a validation sheet which will have references of the parent report.
So when i open the report the reference will pull up the data and in here i would like to add a validation which will add "0" in the prefix if VNO is a 2 digit number or "00" if the VNO is a one digit number.
View 9 Replies
View Related
Sep 21, 2009
I have a huge column of data. This data has few prefixes that I need to remove. I have a list of possible prefixes. Some prefixes are 1,2,3 or 4 characters long. Could you please suggest best way of removing these prefixes (VBA if possible)?
Following are some of the examples of prefixes:
AB
GD
KR
BCD
FP-
TJ-
W
View 9 Replies
View Related
Apr 22, 2014
I have started to create a sales order. I have three columns that I will be working with. I have three lists in a separate worksheet (worksheet 2) in the same workbook. Column A has part numbers. Column B has descriptions of the part numbers in Column A. Column C has prices for the parts in Column A.
I have created a drop down list in Worksheet 1 from the list in Column A in worksheet 2. What I want to do is somehow link column B and C to Column A so that when an item is chosen from the drop down list, the information will pull through.
View 5 Replies
View Related
Dec 17, 2013
I need to setup a dropdown list (I already have the dropdown created) so that once a value in the list is used, it disappears from the list. Now for the monkey wrench...It need to completely re-populate once the list that is being generated from it is cleared.
I also need to copy a value from one sheet to another sheet, however, the location needs to be matched with a corresponding value from the first sheet. Hopefully it will be easier to understand within the workbook (I feel like I'm not explaining it well).
I have attached a sample copy of the workbook and what I am trying to accomplish.
Mens_Dorm_Sample.xlsm
View 2 Replies
View Related
