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 ?
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)
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]
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".
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?
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.
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?
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
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  123 STREET
CALEDONIA FL 12345 " "BUBBA / SHOP ACCURATE AUTO SUPPLY INC  21234 HYDRAULIC DR
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.
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.
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.
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.)
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.
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
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)
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")
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.
I have a data table consisting of entries; for a simple example,
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
But my exclusions will be dynamic so I would prefer to have my criteria listed vertically instead of horizontally.
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
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.