Below is a list of sku's where column C contains values that represent quantities of items received. Now, column D contains 4 different categories of items, BK, BL, CBL and PP, so what I need to do, is add the numbers shown in column C, for each category separately. That is, all values from column C having a "PP" value on column D, should be added separately, and so on with the other categories from column D. The resulting values will be used to create pie charts, but that part I have resolved.
What I'm doing to achieve this, is selecting column D, then, conditional formatting→highlight cell rules→equals to, then typing each category so I can highlight the corresponding cells. Then, adding Filter to column C, then sorting by color, then, selecting the corresponding cells from column C to add their values. The process works, but takes too long, especially when considering my lists usually have a few hundreds of sku's each. The below list is just a shortened version of one of them.
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
I have a name in Column A, and a number that goes with it in Column B. Some of the names on Column A are duplicates. I want to extract a new list that will add the values in Column B of all matching names on Column A, so that my list has only unique names and a total of the numbers in Column B.
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
In sheet1 i have a Listbox(Controlbox), which stores data of 100 names. Now in the D column i need to type the Name. So i would like to have a code which can pull the list box below the Cell I am Typing and it has to match the strings i type to list box(i.e, Pull data from the Listbox as i type the character in a cell)
So here i need 2 Modules 1- Which will pull the data from the listbox 2 - which will bring the list box below any cell which i am typing
I have a global list in B column and I want to create a text about the list. If I put "1" in A column, I want my text to be on B11 for the selected items. So excel should do a loop for the selected range and combine the selected items in a new line and give me as an output.
I want a functionality in a sheet wherein if i select a value from a drop down, i will get specific fields to be entered in the sheet below and when i select a different value from the drop down the fields to be entered should change.
I have a workbook that has three worksheets. The ASU Database sheet contains part numbers and descriptions including specific details that are selected from pull down data validation lists that are named on the third worksheet. I want to have a front end on the first worksheet that has similar data validation lists for the user to select from to create a search criteria that will create a worksheet with only the rows that meet this criteria. I am not sure what method to use to accomplish this.
I'm looking for a macro to jump to and select a cell if it matches the text in another cell based on a drop down list. So I have a drop down list in cell c57 and a form control search button right next to it. I want the user to be able to select from the drop down, click search and then jump to the cell that matches what they selected. I would like the macro to search from B:60 - B629.
I am carrying out some sensitivity testing on a model, and would like to greatly increase the speed of the process by being able to call in from a user defined cell a named range, which is then pasted to the right of the cell.
The attached file steps through the process I have in mind.
I have two problems sort of rolled into one. I have a spreadsheet with a list of skills to perform in cells a2:z2. Under those I put in dates where those skills are performed so a3:a100 , b3:b100 and so on. At the moment I have the these dates populating another sheet with the list of skills going a2:a200 and the dates in b2:b200.
Annoyingly I have been asked set it up so it inputs the other way so my problems are: The skills in the second sheet are in a different order than in the first sheet so its not a straight correlation between the two so I would need to search for the title and match it to the second sheet. Then the second problem is as the will be numerous entries I need to find the next available space in the first sheet. So can you add data at the bottom of a list??
What I am trying to do is cause (where the formula is) to:
If A2 is empty, put nothing, If C2 is greater than today put nothing, otherwise put C2 If E2 is empty then if b2 is "1st" add 4 workdays to A2, otherwise add 5 workdays.
I seem to have a problem with getting the formula to put the value of C2 if it is not greater than today In otherwords if the date in C2 is 6th Jan, i want 6th Jan as the result of the formula.
I have a listbox that has row and columns. The rows are combinations of options and the columns are Additives.
After selecting a row in Listbox1, (first column are names) i would like the value of the second column to goto a specific area.... find that value and in the cell to the right of it place a "Y". The "Y" would indicate that "yes" it was part of the selection row of the listbox. Same for third column, forth, fifth and sixth. The result from the listbox is a number of Y's and N's in the result page. Then making all that were not part of selected line and equal to "N" to hide row (height = to zero).
Listbox and expected result are in attached example worksheet.
The attached does show the need much better then I can explain it.
I have a spreadsheet with two columns and 39900 rows, from that data I want to create two new columns so cell C1 = A143 and D1 = B143, C2=A286, D1=B286, and so on. The row location increases by 143.
The simplest thing I tried to do was to say C1=A1+142, but (knowing it is wrong) I get a #value error.
How can I tell excel to grab these values located every 144 rows below and create two new columns?
i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.
i have an excel file which contains data, I want to be able to search this file and to fill the cell and its next 3 cells to the right with a certain colour. Is this possible as I have wrote some vba to do this but, Im only able to search and not fill the cells with the correct colour.
I have a row of sub-headings at row 12 that require Sort Filters. My problem is that I need the filters on selected columns only. It seems that with the standard filter button I get all or nothing.
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E" Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.
Sub test() Dim SelectDate As Range Set SelectDate = Range("SelectedDate") If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _ "Jan" ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then
I have 2 sheets: "MasterInventory" and "InventoryFeed". "MasterInventory", is my full list of all the item I sell on Amazon.com. "InventoryFeed" is populated when I download an update from amazon and paste the data into it. Each list has about 30 columns and each list uses identical headings in the top row. The third column in each sheet is for the product's SKU.
What I'd like to do is loop through each row in the "inventoryFeed" sheet, assign it's sku (the value in column 3) to a variable, and then copy the entire Row. Then I'd like to switch to the "MasterInventory" sheet, and look in column C for the identical SKU. If it is found, I'd like to overwrite the entire line by pasting in the data that was copied earlier. If the SKU is not discovered, I'd like to paste the information into the next blank row in the "masterInventory" sheet.
After the whole thing is looped through, I'd like to sort the MasterInventory Sheet from A to Z by the product's SKU.
I have a table in which you can select values from a pre-defined list. However on some days I need to be able to select multiple values from the list and not just one value. Is it possible to do this or if not is it possible that on the days I need to select multiple values I can get excel to auotmatically insert a line to enable me to select the second, third etc value without having to physically insert the extra line?
I have a list of all transactions that happened during the day at different locations On another sheet I want to list all the locations and in another column to populate a list of unique transaction codes for each location from the first sheet (all unique values for each location in one cell). I am attaching a spreadsheet in case I am not clear enough (I need to create a Summary). This attachment is really a simplification of a file I work with. The real one has many columns and multiple subtotals, I would not be able to incorporate a method that involve filters
What I would like the formula to do is look at the list in column D. If a particular row shows a match for that text string it will populate that value in column B, but delete everything preceding the text value and after.
I am trying to get a list of values for cells that have been colored.
The following is the macro that I have but I am not an expert on VBA
I think I need two things to achieve this.
1. Would really appreciate if someone would please help me debug this?
2. After I get this list of values I would like to be able to apply this to a worksheet and in one column define the differing row colors by a value. What would be method to do this?
Sub PrintRGBValues()
Dim intNumColor As Integer Dim strHexVal, strRGBVal As String
My requirement is to implement a drop down list having two values "Yes (Recommended), No", I want the value "Yes (Recommended)" to be default value for all cells on which this validation is put. But also need a message box to be displayed if the user go ahead and select "No" the message box should be like "If you are not certain about this feature please click on 'Cancel' and contact you List Provider." and have 'Ok' and 'Cancel' button on it.
