Conditional Drop-Down For Multiple Lines
Jul 29, 2014
I have a list of car-dealers and licence plate numbers. f.e. data in tab 1 Supplier-Cars.jpg
In another tab (let's call this tab 2) I have to make drop-down lists, depending on the value in this drop-down the cell next to it must give the list of cars from this supplier. f.e. if I select in the first line Supplier 1, I get a drop-down list where I can select Car 1 if I select in the second line Supplier 4, I get a drop-down list where I can select Car 4, 6 or 7 etc. Because of the number of suppliers, I can not work with predefined lists where I put all the suppliers one next to the other...
have a drop-down in each line to go look for the cars depending on the supplier I selected ?
View 2 Replies
ADVERTISEMENT
Oct 14, 2008
If you choose entry number 1, Lines 10 to 14 are hidden. If you choose entry number 2, Lines 15 to 29 are hidden and lines 10 to 14 are unhidden. If you choose entry number 3, Lines 20 to 25 are hidden and lines 10 to 29 are unhidden
Until now i have the following:
[Private Sub Worksheet_Change(ByVal Target As Range)
Rows("10:25").Select
Selection.EntireRow.Hidden = False
If Range("A1") = "1" Then
Rows("10:14").Select
Selection.EntireRow.Hidden = True
ElseIf Range("A1") = "2" Then
Rows("15:19").Select
Selection.EntireRow.Hidden = True
ElseIf Range("A1") = "3" Then
Rows("20:24").Select
Selection.EntireRow.Hidden = True
End If
End Sub]
View 2 Replies
View Related
Dec 5, 2013
I need to create a spreadsheet that has approximately 1000 rows with the same exact information in each of them prefilled (as I use the "drag-down" method). I use this chart throughout the year to enter various bits of data in each row. I also use conditional formatting in each row. The company I work for wants to have an empty space between each row.
"
You can create the blank rows separately and then interleave them with the existing rows by sorting. To start, insert a new column to the left of the existing column A. Enter 1 in cell A1 and highlight column A all the way to the last row that contains data. From the Edit menu select Fill | Series and click on OK. Column A should now contain numbers from 1 to the total number of rows. Press Ctrl-C to copy these cells to the clipboard, click in the cell just below the last of them, and press Ctrl-V to paste. Now highlight the entire data area, including the new rows with just a number in column A. Select Sort from the Data menu and choose the No header row option in the resulting dialog box. Under Sort by select Column A, under Then by select column B, and click on OK. Finally, delete column A. You now have a blank row after every one of the original 1,000-odd rows."
This works great for the data that I copied (with the drag down method) to all the rows. However, this method does not insert an empty line in between all the conditional formatting I have throughout the spreadsheet. This is my conditional formatting formula I have "manage rules" which is under "conditional formatting" =NOT(ISBLANK($A6)) (*please take note that this is selected for 1000 rows.) Maybe there is some kind of change in the formula that I need in order for the conditional formatting method to be copied onto every 2nd line of the 1000 rows".
View 10 Replies
View Related
Jul 4, 2008
I have only recently started playing around with macros, and am slowly getting into them. I do however still struggle a bit to ‘read’ them. Having played with formulas for years I can generally translate a string of formula text into English, like ‘if this cell value is greater than that cell value, then do this, if it is not then if it is equal to that cell value, do that, if not return 0’. Babbling like a child basically.
With visual basic I have managed to record some handy macros and then tweak them a little manually but I am still struggling to follow it going through it step by step reading it like a formula. So I hope you won’t mind me asking some very stupid questions. I mean well; I’m just a bit slow.
At present I am trying to do two different things on two different sheets, and I was wondering if perhaps one of you could nudge me in the right direction.
1:
I am trying to insert a blank row above every row that has a certain word in column B.
So basically ‘find value “Example” in column B, and when you find it, insert an entirely blank row directly above it’.
2:
Is a bit more challenging. I want to change the colour of a cell if the value in the cell corresponds with the value of another cell in the same column.
For example, I have a long list of surnames in column A. When I add ‘McNeil’ at the bottom, I would like to be able to run a macro that checks if the name McNeil appears anywhere else in column A, and if it does, that it changes the colour of the cell.
Preferably both of the cells that say McNeil, but one would do very nicely indeed.
If that is possible, I wonder if it is possible to do the same with the first name in column B, but only if there was a match for the surname in column A on the same row. So, if McNeil does not appear in column A, don’t bother, but if it does, does the corresponding first name appear in column B?
If both of that is possible, the next step would obviously be if McNeil appears in column A (say twice, once in A123 and once in A678), do cells B123 and B678 match as well?
View 14 Replies
View Related
Jul 28, 2006
change the colours of alternate rows in order to make reading line by line easier. I know that I have used this before as a Conditional action ie:- if(mod...... etc
As always, the program I used it in has 'disappeared' so I can't check back with that.
View 5 Replies
View Related
Jun 9, 2009
I would like to use conditional formatting verify that the lines of data being pulled from my application into Excel, but totaled using "=sum" are equal to totals pulled directly from my application. I believe that I should be able to use conditional formatting but am not sure how to make the formula work correctly.
To illustrate my problem: I have cells A1 (a total of a range), A2 (also a total of a range) and A3 (total of data pulled directly into my Excel report). In cell A3 I would like to verify that lines A1 + A2 - A3 = 0. This I can do without any problem, but there is rounding involved. The total of the above formula should be between -1 and 1 and still show as correct. I want the number in A3 to show up in red/bold when incorrect.
View 6 Replies
View Related
Sep 10, 2012
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing
View 1 Replies
View Related
Mar 24, 2012
I have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line. I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. FYI I did go through many many threads and found some that were close to my problem but not close enough to work.
"
JOES TOWING SERVICE [0123451]
123 STREET
CALEDONIA FL 12345
"
"BUBBA / SHOP
ACCURATE AUTO SUPPLY INC [0234567]
21234 HYDRAULIC DR
LOCKWOOD OR 45678
"
View 7 Replies
View Related
Jul 10, 2014
I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).
So my data is as follows - (I have attached teh sample file)
"Pari bom - GP and CFO 02547895452 pari@gmail.com
papa bom - GP and CFO 23365789541 papa@gmail.com
mama bom - CFO 2587412589 pari@gmail.com
nana bom - CFO 2587412589 nana@gmail.com
masi bom - CFO 2587412589 masi@gmail.com
"
I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.
View 4 Replies
View Related
Jan 23, 2010
share a macro to copy cells from range C20:C300 and paste them in C2 on multiple lines. If possible two lines gap between each cell's value when they are pasted in C2.
View 9 Replies
View Related
Jun 24, 2010
I need a macro that will seperate multiple lines in more than one cell into multple rows.
Cells G-N contain multiple lines of data that I must split out into multiple rows; the multiple lines of data must be parced out into the same row.
View 4 Replies
View Related
Oct 8, 2008
I would like to make a form where the user has to select from a drop down list several options, and that depending on the pick the drop down list below shows different options. example: For the question "what food do you want to eat?" you can select pasta, pizza or dessert.
Now if that person pics dessert, the 2nd drop down menu should shom him again a number of desserts he has to pick. And again below another one depending on what he chose.
View 2 Replies
View Related
Nov 20, 2008
I am having is that I know that other people will be adding more and more suppliers/brands and types as time goes on.
This presents two problems:
1) I am not in a position to keep making new rules every time a new entry is added to each of these categories.
2) I am trying to add functionality that will allow me to sort them alphabetically - so that the drop down lists continue to make sense. However if this were to happen then the calculations created next to the lists would go out of whack whenever I did an alphabetical sort.
View 5 Replies
View Related
Oct 15, 2009
I'm trying to create a drop down control for each cell that will present the user with values Red, Green, Yellow. Once selected the cell will turn that color.
View 9 Replies
View Related
May 8, 2008
I am trying to create 4 pull down menus. Menus 1, 2, 3, 4. Menu 1 is the top menu, menu 2 will only show certain information based on what was chosen on menu 1. Menu 3 will show certain information based on what was chosen in 2 and 4, again, will only show certain information based on what was chosen in 3. Sort of how the menus on autotrader work, if you chose audi it will only show audi vehicles, although not for the same purpose, or even the same industry. I am using Excel 2007, it might matter. I have attached an xls file that shows the information I am using and below that the basic progression, I haven't listed it all, but you should get the general idea.
View 5 Replies
View Related
Oct 27, 2008
Sheet1 is my database and looks like the following:
HTML ABCDEFGHIJK
1RefNoSetSubsetStatus
2101lambsheepINP
3102catpetCOM
4103chickenbirdINP
5104milkcowINP
6105turkeybirdINP
7106honeybeeCAN
8107dogpetINP
9108boybabyINP
Sheet2 is for the user input, in which Columns H and I would have a data validation drop-down list and looks like the following: ...
View 14 Replies
View Related
Dec 2, 2009
I have attached a spreadsheet that I need to automate. Cell C3 is a drop down box of the raw data found on the raw data sheet. I would like B6-8, c6-8 and d6-8 to automatically updated and change depending on what users select from the drop box in C3. (so basically im trying to re create the yellow table with formulae/ vb.)
View 4 Replies
View Related
Feb 17, 2006
Just wondering if a drop down list can be specific to a selection made in a previous cell. For example in column A the user would select either:
Weekly
Monthly
Other
Then in column B, the drop down list would be conditional upon what was selected in A. So if the user selected 'Weekly' - the drop down list in B would be: Monday, Tuesday, Wednesday, etc. Or if the user selected 'Monthly' in column A's list, the list in column B would be: 1, 2, 3, ......31.
View 3 Replies
View Related
Jun 21, 2006
I have to the following lines in the header (left side).
Canadian Mutual Fund Industry
Gross Sales By Fund Company
As at January 31, 2006
(FYTD Tier 1 Firms)
(C$ 000's)
the date is Strored in strDatey. the problem I m having is that I don't know how to make muliple lines in the header through the code.
Private Sub CommandButton1_Click()
Dim strHeader As String
Dim strDatey As String
Sheets("FYTD Gross (Tier 1)").Select
strDatey = "April 20"
MsgBox (strDatey)
strHeader = strHeader & "Canadian Mutual Fund Industry " & strDatey ' text strDatey
ActiveSheet.PageSetup.LeftHeader = strHeader
End Sub
View 6 Replies
View Related
Mar 27, 2014
I have a list of projects and a list of milestones and I want to extract every milestone for a project one by one. I have found a way to do this for one project but as there is not a fixed number of milestones, it's proving tricky to do it for multiple projects as I don't know how to tell the formula to stop looking at the Project 1 and move on to Project 3 for example.
This is a very very simplified version of my table (there are other columns inbetween and many more projects)
Project 1Milestone 1
Project 1Milestone 2
Project 1Milestone 3
Project 1Milestone 4
Project 1Milestone 5
Project 2Milestone 1
Project 2Milestone 2
Project 2Milestone 3
Project 2Milestone 4
Project 2Milestone 5
Project 2Milestone 6
Project 2Milestone 7
Project 3 Milestone 1
Project 3 Milestone 2
View 2 Replies
View Related
Jan 14, 2014
is there possible to freeze multiple line and column in one sheet i.e freeze line 1 and column A1:A10 and free lines 3-10 and then freeze line 11 and free lines 12: 20
View 1 Replies
View Related
Aug 5, 2014
TTHBF251863100
147943F00KNQ TTHBF251863100
REF VINS DU MONDE SA
O/RTT HBF251863100
I need a macro to transform my multiple lines cell as above ( 5 lines ) into a one line cell as below.
REF VINS DU MONDE SA
As the cells needing changing will always be different it would be good if I could have a macro working on the selected cells only, not a specified range.
View 1 Replies
View Related
Oct 7, 2011
how to copy cells from different columns to another workbook and to do this for several lines? The macro stops at the blue line
For i = 8 To WS2.Cells(Rows.Count, 3).End(xlUp).Row
If MySheet.Name = WS2.Cells(i, 3).Value Then
WS2(i, 4).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Destination = WB1.Worksheets(table(z)).Range("G10")
View 1 Replies
View Related
Jun 7, 2012
I have values in a column of cells in the format (All numbers in a single cell)
34567,43510,'1'
44049,99820,'1'
11124,37373,'1'
etc
up to ten such entries may appear in each cell with an arbitrary number in each cell. They were entered using the ALT+Enter method. Is there an easy formula to extract each row (such as 44049,99820,'1') to use in a formula. If there are two entries I would like to write two separate rows like:
LINE 34567,43510,'1'
LINE 44049,99820,'1'
If a macro would be required I understand and could use that also.
View 1 Replies
View Related
Jun 23, 2014
I have a data table consisting of entries; for a simple example,
Column header
Entry A
Entry B
Entry C
Entry D
Entry E
I want to apply an Advanced Filter to Exclude multiple items. Say I want to exclude entries A and B. In my Advanced Filter criteria range I entered:
Column header
A
B
But since Excel reads each line of Advanced Filter criteria as "or" - i.e. does not equal A OR does not equal B - nothing gets filtered. I know the proper way to use AND in Advanced Filter is with multiple columns, such as:
Column Header
Column Header
A
B
But my exclusions will be dynamic so I would prefer to have my criteria listed vertically instead of horizontally.
View 4 Replies
View Related
Nov 20, 2007
I have a large spreadsheet I need to sort into alphabetical manager order.
As there are between 2 and 20 rows per manager I would like to know if I am able to sort this into alphabetical order!
View 9 Replies
View Related
Feb 6, 2008
I send a regular file from Outlook which contains a large amount of data, the intention being that the receiptiant will copy this data into notepad or excel. This works fine if it is going to another Outlook user, however one of the users is running Lotus notes and it causes the text to go onto multiple lines . Unfortunatly i am unable to send the files to the users as attachments so i have to send the text in the body of the email
This is how they see the data
31.01.2008|380059||1013259|800766574|S353|300129441|ZLF|300129441|00000|
2353|11000955&770695&PAL_CHEP_FG|152&143&2|customer name |HARDWICK GRANGE|WARRINGTON|WOOLSTON|WA1 4RJ||20080204|S353||
2.875|M3|40|083000|20080204| 2.000|02143970067|ADVICE IF
PALLET QUANTITY INCORRECT Customer reque|MV1080097/1080105|
31.01.2008|380059||1013259|800766580|S353|300215153|ZLF|300215153|00000|
2353|11000654&PAL_CHEP_FG|78&12|customer name |BLACKHEATH
LANE|RUNCORN|MANOR PARK|WA7 1SE|N/A|20080204|S353||
16.865|M3|40|090000|20080204| 13.000|0262 04020801 LOAD
2|DEL MUST BE UNLOADED BY DRIVER ON A 1ST COME 1ST S|MV1080097/1080105|
31.01.2008|380059||1013259|800766584|S353|300129441|ZLF|300129441|00000|
2353|10000294&748060&725762&PAL_CHEP_FG|180&475&240&3|ICELAND FOODS
LIMITED|HARDWICK GRANGE|WARRINGTON|WOOLSTON|WA1 4RJ||20080204|S353||
5.732|M3|40|103000|20080204| 3.000|02143971494|Customer
requested delivery time: 04/Feb/2008 10:|MV1080097/1080105|
There are always 30 records, some are blank, but are always seperated by a | i.e 1st record ends with MV1080097/1080105
I have tried to create a macro for them to use which will seperate the cells into excel using the delimeter but i am stuck on how to manage the multiple lines which dependant on the amount of text within the cells may vary
View 9 Replies
View Related
Apr 15, 2008
I receive a monthly report containing a list of people, and how much is being paid for certain services. The company that sends me this list is preparing to adjust their rates and it will be retroactive back a few months.
The way they plan on doing it is by means of taking a credit back several months, then "re-paying" the correct rate. The main data will include the month for which the payment (or credit) is being made, the person's unique identifier, as well as the amount.
Here's a sample of what it would look like:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, -20.00
John Doe, 123, 03012008, 25.00
John Doe, 123, 02012008, -20.00
John Doe, 123, 02012008, 25.00
So basically the above shows they paid $25.00 (correct rate) for April 08, then they took back $20.00 the prior two months (the old rate) and paid the correct rate right afterwards.
In what I need to do, this is going to be a lot of work. Is there a way to programatically merge the amounts given the member's unique ID as the "key field" as well as the same month? So it might look like the following:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, 5.00
John Doe, 123, 02012008, 5.00
Just giving the sum of the amounts for a the given people in the same month?
I'm pretty good with VBA but this one is stumping me.
View 9 Replies
View Related
Jun 24, 2008
The sales record I am working on contains many lines per order for the same part number, e.g.:
Order: 121212
Part: 12345, Qty 1
Part: 12345, Qty 1
Part: 12345, Qty 1
Instead of:
Order: 121212
Part: 12345, Qty 3.
I want to consolidate this data. I've tried subtotalling on order, then subtotalling on part number. This works, but the resultant appearance is messy and hard to follow.
View 9 Replies
View Related
Jun 16, 2009
Entering a code in a cell, I'd like the cell next to it to return a list of possibles which the user can then select:
Line Code Material
123456 Apple
Orange
Strawberry
By entering the line code the user get the 3 Materials that has this Line code and they coose the one they want.
The list I was using to look up looks thus:
Line Code Material
123456 Apple
123456 Orange
123456 Strawberry
I've read a bit about combo boxes and lost myself complete, so please use small words for me.
View 9 Replies
View Related
