Fill List Based On Conditional Criteria
Apr 18, 2007
I have an Excel sheet with a column (column O) containing expiring dates. The dates represent the day a certain product expires, and for each date, a name is attached (in column A). So there are 500 dates in the sheet, each with a different corresponding name. How would you have Excel create a list, in a new sheet, of the names that are expiring in less than 90 days from today? That is to say, if the date in a cell in column O is less than 90 days from today, then the information in the aforementioned cell and the information in the cell in column A (in the same row) is COPIED (not moved) to a list in a new sheet?
View 2 Replies
ADVERTISEMENT
Apr 4, 2009
I just stumbled onto this forum while Googling around, and it looks great. I'm pretty new to VBA coding so please bear with me. Effectively what I'm looking to do in this mock-up file, is anywhere the currency is "EUR" in column A, I'd like the relative cell in Column G to change to "INSERT TEXT", else I want it to untouched (i.e. to keep the comment it currently has).
View 6 Replies
View Related
May 23, 2013
The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.
View 2 Replies
View Related
Jul 11, 2007
I would like to fill in a table based on a small database
this table then creates a graph.
But I need to look at 3 criteria before I can fill in the table
namely: customer - weeknumber - weekday
based on these items the time should be filled in accordingly.
Can this be done with regular formula or should I try VBA ?
please have a look at my file the data should go from sheet input to sheet ACL
based on the above mentioned criteria
View 4 Replies
View Related
Dec 15, 2009
I have a set of data in column R, with an unknown number of rows that looks like this
Days Late
-28
150
3
16
41
.
.
.
and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:
View 4 Replies
View Related
Jul 16, 2007
I have a column of information. The first cell has the record number. Then there are criteria the record must meet. If any of the cells have N for nonCompliant, i would like for the record number to change colors. If it were only one citeria, i think i know how to do that, but because there are as many as 10 I'm not sure how to make it change colors for all ten unless i do it one cell at a time.
Also there are several thousand columns on different worksheets, how do i apply the conditions to each column without doing it manually?
View 10 Replies
View Related
Jan 14, 2008
I am trying to use combo box to display my data, here are the details. I am having 20 Departments, and have Budget and Actual values for those Departments for each month for the year 2008, tell me the logic to use combo box as dropdown list for Dept. and how can i get values in first 5 to 10 rows after changing the Department in Dropdown list(Combo Box)
View 5 Replies
View Related
May 28, 2014
The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?
Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
[Code]....
View 2 Replies
View Related
Apr 23, 2014
I have a worksheet that when a row changes based on the value of column B, I want to remove all of the formulas found in the row but keep the existing values, and then change the color of the row.
In the sample file attached, when the value is "Closed", that row will keep the existing values and then it gets grayed out. Rows that are still marked "Open" need to retain the formulas in case other information changes.
I have tried copy/paste special using autofiltering but that doesn't work because of the hidden lines. This file changes on a daily basis and I need a quick way to update the file.
SampleFile.xlsx
View 1 Replies
View Related
Apr 7, 2014
how to create VBA code for conditionally formatting a column of dates that have expired. My unique challenge is that I first need to associate the item attached to the date with the expiration date of a particular group. Therefore, I have to first match the Item label with the appropriate group, and then I need to evaluate the expiration dates of both the Group and the Item. If the Item expires before the Group expires, then I need the Item date to be formatted in bold and with a red font.
Attached is a sample spreadsheet.
Thoughts about how to accomplish this with VBA? By the way, VBA is the necessary approach due to the fact that the data is updated several times per day, and all existing data is erased prior to uploading new data.
View 2 Replies
View Related
Aug 10, 2006
I am trying to figure out how to set up a spreadsheet, that comprises a list of open-entries, wherein the user will insert their information and a % out of 100 assigned to them; the entire form, in the end, cannot go beyond 100%, so each entry has to be done correctly...
BASICALLY my problem is that I am trying to set up the " Total %" cell, which is already assigned the formula "=SUM(D5:D16)" (D5 through D16 being the cells in which %'s are assigned) so that it will auto-populate with the appropriate color/error if the %'s are either insufficient (short of 100%) or exceeding (above 100%). What I had been trying to set up (and have successfully set up in other spreadsheets, however under slightly different circumstances) is a Conditional Format in which the first (with the color Red for "exceeding") was " =If(SUM(D5:D16)>100,SUM(D5:D16),SUM(D5:D16))
" So regardless of exceeding, exactly met, or insufficient totals, it would post the total entered, BUT assigning the color red, so that IF it did, in fact, total higher than 100, it would appear red. A similar formula would be applied to Green (reflecting "on target") in which the formula would be " =If(SUM(D5:D16)=100,SUM(D5:D16),SUM(D5:D16))"
Now the problem is: this is not working for the SUM of the cells. In my previous experience with Conditional Formatting and color assigning results, it had been with the AVERAGE of two columns of cells.(worked without a hitch.)
View 4 Replies
View Related
Dec 26, 2012
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
View 6 Replies
View Related
Jun 23, 2009
I am drawing a blank. I want to use conditional formatting in cell b2 that will outline the cell(I know how to do that) based on a formula. The formula I need would determine if cell a2 is 26,27, or 28. Or function just returns true/false. I would rather not use 2 formulas.
View 2 Replies
View Related
Feb 16, 2010
I have a spreadsheet that requires me to manually hunt down records that match based on several different criteria. this is very time consuming and inefficient. i would like to find a way to highlight certain records that match based on several different fields. i have posted an example spreadsheet that also has a more detailed description of what i am trying to do; which is located in the second sheet.
View 9 Replies
View Related
Oct 9, 2012
I need contents in cell A5 to turn red based on 2 criteria occurring.
IFCell F5 is greater than 0 (zero) AND IF date in H5 is equal to or less than 30 from today's date. PS: if needed I have today's date in A1 for reference.
Can this be done and how?
View 2 Replies
View Related
Mar 1, 2014
I'm trying to set up conditional formatting in a spreadsheet where there are 3 possible correct letters that can be entered into a range of cells and if one of these letters is not entered then I would like the cell to be highlighted.
To be more precise, it's an accommodation spreadsheet and in each cell if someone needs a room for that particular night (each night is represented by a separate column) then a letter is used to indicate what type of room they require i.e. "k" is a king room, "s" is a Suite and "t" is a twin. I don't want to force people to have to select from a list which would be the obvious solution to this as it is simply too cumbersome for the amount of data entry we do.
However i want to ensure that if someone accidentally enters anything other than t,k or s then the cell is highlighted. We work in large spreadsheets covering months of dates so to manually have to find any errors is hugely time consuming.
View 10 Replies
View Related
May 19, 2013
I need a macro that will highlight a row based on a cell criteria. I have used conditional formatting but there is a lot to format and has dramatically slowed the sheet down. However, I know nothing about macros/vbas.
E.g. If it states JI sent then I want the whole row to be highlighted light green.
App form needed = peach
Waiting list = light blue
Cancelled = red font strikethrough
DNA = purple font strikethrough.
Also - it could well be that in the row it has App form received, JI Sent and DNA all in 1 go. So ideally I would want that row to be highlighted light green with purple font strikethrough.
Booking Status
JI Status
Date JI Sent
Attendance
Cancellation date
Chargable / non chargable Cancellations
Chargeable / non chargeable DNA
Date taken
Time taken
Event Title
Start Date
Time
App form received
JI Sent
[Code] ......
View 3 Replies
View Related
Jul 19, 2012
I have a gantt chart that was built in excel. I need to shade cells in the timeline based on persons title under each project.
I can provide a snip of what the layout looks like for a better visual. I want the PM's Green, Supers Yellow and Persons Reporting Orange.
To get the green bar shown is using formula: =SEARCH("Project Management",$E17) , The issue with this is it fails to consider the start finish dates. For this particular project it works but when the formatting is "dragged down" the issue arises.
View 9 Replies
View Related
Apr 25, 2014
I would like to shade my cell yellow if the priority equals A or A-NEW and ACV=o.
In the data below, items 4 and 13 would be shaded yellow.
I am using Excel 2010.
Priority A, B, C
Consumer UPC
Item Description
ACV Weighted Distribution
[Code] .......
View 8 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
Apr 3, 2014
Code is trying to
1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3
[code].....
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
View 4 Replies
View Related
May 8, 2009
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
View 9 Replies
View Related
Jul 20, 2012
I have a sheet that has a button on it that I would like to print a list based on a set criteria. My criteria is "if the value in Range D4:D100 is positive". I think a messagebox would be okay but although it is not needed yet, I would like to have the ability to print up to 100 rows on a single sheet of paper. The print should include Columns A:H of any row where respective D value is positive. If possible could Column D when used as a reference be made relative so that I can add or take away without losing functionality?
[URL]
View 7 Replies
View Related
Jul 9, 2011
I am trying to create a drop down list that is dependent on two criteria. To depict the issue, I've created a mock-example about sports.
On my 'data' sheet:
Column 1: Name of sport
Column 2: City
Column 3: Name of sporting team
On my 'form' sheet:
Cell 1: Dropdown list to select name of sport
Cell 2: Dropdown list to select city
Cell 3: I want this cell to be a dropdown list of names of sporting teams, based on the conditions set by Cell 1 & 2.
Example is attached.
View 14 Replies
View Related
Dec 7, 2011
I have a table with the following headers: Customers, Location, bill number, date of bill, number of days you have untill you must pay the bill, the rest are not important
I need to return a list of the bills from the last six months, in which the customer has been granted with days until he must pay the bill(there are some with no granted days).
The table headers are translated, they are not so long.
I need to do this until friday.
View 1 Replies
View Related
Jul 10, 2012
Suppose I have a table of data such as the one below.
ABC
CarolineRed Team90
NatalieBlue Team13
AmyWhite Team42
AlexaBlue Team58
LaurenBlue Team75
CaitlinRed Team66
ChristyWhite Team42
AnnieWhite Team36
NicoleRed Team90
I am looking to create a sorted Top 10 list based on certain criteria with ties. For example, I would like to take the top values for White Team with both people with "42" at the top, since they are the top values.
I realize that there are not 10 values in the list above but I truncated the list for space purposes.
View 1 Replies
View Related
Nov 27, 2009
customerdb.xls
Column A Column B
Customer ID Suppliers
0023 ABC Co.
0046 XYZ Co.
0023 qqq Co.
0034 MNO Co.
0023 ppp Co.
in another sheet in cell B2 user enters customer code(say 0023).
in cell C2 user is required to enter a single supplier of that customer(0023) from a dropdown list(source customerdb.xls). usually the dropdown list of C2 will show all the suppliers name where i need to show only 0023's suppliers in the dropdown.
so what i need is to filter C2's dropdown list based on customer id entered in cell B2.
View 9 Replies
View Related
Jan 11, 2010
ColA has a range of names.
ColB allocates ColA entries to either of two categories by entering 'cash' or 'credit'.
I need help writing a formula to make a unique list of the names in ColA that have 'cash' in ColB. I'd prefer not to use autofilter.
View 9 Replies
View Related
Jul 1, 2008
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
View 8 Replies
View Related
Sep 21, 2012
Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)
I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.
Obviously to size of the list will vary depending on how many open orders there are.
View 1 Replies
View Related
