Formatting Cells Based On Data Validation
Jun 10, 2006
I have tried to modify the examples here on the site, but can't seem to get it. Cell G1 has list data validation of 5 numbers,6 digits long. In the cells below G10:G500, these numbers are listed. However, in the G10:G500 range the numbers have 8 digits. The first 6 digits remain the same and only the last 2 change. Here is what I would like to see happen: When I chose a number from G1, I would like to highlight all the cells with the first 6 digits matching G1 in the range G10:G500.
View 2 Replies
ADVERTISEMENT
Jul 24, 2009
I have column of cells, both of with have drop down menus to select information from. I have an adjacent cell to display an error based on conditions. Is there a way to have conditional formatting to apply to the cells with Data Validation to change the color, based on the error. I have tried using the Conditional Formatting options but it still does not change the color of them. I believe i have to use the formula option, but not exactly sure what to put, i know a if statement could work but unsure on how, there are only two conditions that i would need for it to apply, if the adjacent cell has an error, and when it does not. Is this possible?
View 9 Replies
View Related
May 14, 2009
What I am trying to do would appear to be very simple. I am trying to build a quotation form for our sales guys to use. I would like to use a drop down list with the list of products in, which I have created using a list in a second sheet and then using a data validation drop down list.
What I would like to achieve is the sales person selects the product (of a list of only 14) from the list and excel to automatically fill in the two columns to the right with the model no. and also the price.
Would it be possible to use an IF statement as there are only 14 products to choose from? Is there a better way of doing this? I have created the lists of model numbers and prices in the second sheet alongside the product name which the drop down list sources from.
View 10 Replies
View Related
Apr 13, 2012
I am trying to find something that would allow me to have a spreadsheet clear the contents of a certain cell based on the selection made from a data validation list which resides on the same row. This can best be described with an example.
The user selects anything but " " or "none" from the validation list, which for this example resides in C5, would trigger code to clear the contents, if any, in AA5. If " " or "none" is chosen the value remains.
This capability should be available for each row through 100.
View 3 Replies
View Related
Dec 13, 2009
I'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.
i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab.
Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.
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
Jan 14, 2010
I am using Data Validation for my drop down list, using “List” and “source” =$A$132:$A$159 along with Conditional Formatting that changes different fill colors, depending on selection. Is there a way to use VBA with a command button that when selected would give the user a dialog box so that they could enter additional comment that would be added to existing list and would also be able to select a fill color for that new selection.
Below I have a recorded Marco to give an idea of what I would like to achieve, but of course using a input box of some sort to make selections of text and color
Also I'm using Excel 2007
View 14 Replies
View Related
May 21, 2009
I have conditional formatting for a range of cells eg. =COUNTA(AC3,AD3,AE3,AH3)
It is currently set so that cell A3 changes to Green if AC3 is populated, Red if AD is populated, Orange if AE3 populated & white if AH is populated.
AE3 has data validation (Yes or No).
I need AE3 to change to White if no is selected and Orange if Yes is selected.
View 9 Replies
View Related
May 19, 2008
On sheet 1 I enter data such as:
Visit Date | Customer Name | Customer Number | Job Number | Product
On Sheet 2 i have a list of our 130 products which would be similar to '1130Tots Standard 24" ' I have made these as a list so we can choose from the list using data validation.
Now, out of the 130 products we have about 4 main different Catagories - Tots Direct, Tots RTS, Direct, RTS. - I could put what category they are in in a column next to the list on Sheet 2.
What i would like to happen is if e.g. a product is chosen from the drop down list, it could look in the cell in the column next to it and see what Catagory it is in and format the entire row on Sheet 1.
E.g. Direct changes colour to grey, Tots RTS to Pink, Tots Direct to Grey/Pink stripes, RTS stays standard no colour.
A bit of a development on a question i asked last week to cut out another stage. Currently people have to enter the product, then choose in the next column whether is is Direct, RTS, Tots Direct, Tots RTS then conditional formating will colour the rows. - Want to know if it is possible to do this all in 1 go by selecting the product from the list.
View 10 Replies
View Related
Jun 4, 2013
I have a pretty simple spreadsheet (because I'm not an expert on excel) to track temp staffing requirements each week over a year. It only has about 150 rows and a bunch of columns but when I try to make it bigger (add more rows so I can put about 70 staffing positions instead of 19) it will not save (cannot save all of the data and formatting). I have read on the microsoft site that there are limits to data formatting, but it describes over 2000 rows?
View 3 Replies
View Related
Dec 12, 2012
I have a table that is a list for validating values in a column .what I want to do is instead of getting an error message anf stopping if the entered value is not valid the cells become red.
View 6 Replies
View Related
Jun 8, 2007
I need to lock certain cells on a worksheet when a value is selected from a validation list. For example:
user goes to select an option from a validation list, that cell is now locked.
User decides to change the value with a blank option, available on the same list. the cell becomes unlocked ready for new input from the list.
the cell can only allow input when the value in the list matches that of another cell. if another cell already has different data in, it wont allow you to change the value.
View 3 Replies
View Related
Nov 17, 2011
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
View 5 Replies
View Related
May 3, 2013
What I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
View 5 Replies
View Related
Apr 24, 2008
I'm looking for a solution to a problem and from what I've read in the forums so far, this is the place to come. I first want to say that I have tried to solve the problem by using guidance from other similar threads and adapt those answers to my needs, but as my understanding of VBA is non-existent this was somewhat difficult. I don't want to post a repeat question and I hate wasting people's time if unnecessary, but I can't do this on my own.
That being said, here is the problem;
(I have attached a simplified copy of the worksheet I am working on for reference)
I would like to lock a particular cell in my spreadsheet based upon the selections made from two drop down validation lists (Cells “C4” & “D4”)
Essentially, if a correct choice is made in both, then the cell “E4” becomes unlocked for editing. If however, the correct selection is made in one list, but not the other, I need the cell “E4” to remain locked and a message to appear to prompt the user to correct their mistake.
Furthermore, both the validation lists have a whole range of selections that are very similar but have subtle differences;
E.g.
"Soft Eye by Talurit"
"Soft Eye by Splice"
Is it possible to write a VBA code that performs a similar function to the =IF(ISNUMBER(SEARCH("**",))) formula, so that if any choice that contains the word "Soft" is selected from either or both lists then the cell “E4” becomes unlocked, but if the selection does not contain that word then the cell remains locked?
If anyone could provide a code to make this happen, I would be very grateful! But please explain how to do this, as the word “Soft” won’t be the only parameter and I may need to alter the code so I need to understand the principle behind it; For example, on the spreadsheet, if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Soft Eye’ is selected then cell E4 is unlocked for editing. If in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Fused and Tapered’ is selected then cell E4 is unlocked for editing, but if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Standard Eye’ is selected then cell E4 remains locked and an error message appears to alert the user that his/her choices are incorrect. If however, "Soft" is not selected in either list then cell E4 remains locked (it has to be selected in at least one of the two lists to allow editing).
This is a work in progress (not all the formulas that will eventually be used have been entered yet, so please excuse the untidiness of it all) and the choices are likely to change based on input from my colleagues, so I need to understand how the VBA works in order to alter it accordingly, but I don’t want to put any more work into this (it’s still in the early stages) if it isn’t possible to achieve these needs.
View 9 Replies
View Related
Aug 20, 2014
I have already set up a data validation, if if you pick a region, you only get that regions items in the next column.
A
B
C
D
E
[Code]...
But what I would like to do is in Column E(for this example) have 3 extra lists for Location in each region. So if I pick S in A, then in E I only want the Southern options.
I have checked out this website, but I cant seem to work out how to add the third dependence [URL]....
View 5 Replies
View Related
Jul 14, 2009
I am trying to set the data validation on a cell based on another cell value
So basically if the User enters say CAN in cell A2, cell B2 should use "listCAN" range as the data validation list
and if i enter US in A2, B2 should use "listUS" as the data validation list
View 6 Replies
View Related
Feb 16, 2009
I need a data validation to prevent entries when they are > 50, but only if the value of another cell in col A is "Payment". The value of 50 is an example, I have another formula to get this number using VLookup.
The idea is that if the value of the cell in col A is "Income", I don't want the entry to be rejected. But if the value in col A is "Payment" or "Transfer" and > 50, I want the entry to be rejected (if I only have $50, then the payment cannot be > 50).
View 7 Replies
View Related
Apr 25, 2014
I'm trying to have a Data Validation list issue where I want to populate a second list based upon the value of the first list.
In this instance I have a Department in the drop down and Employee Name in the second drop down and I want people to only see Employee names if they're allocated to a particular Department. I've been able to do the list using the INDIRECT function, however that only works when there's only a small sample of both names & depts, however I'm looking at populating this document with around 3000 people and over 250 departments, so I would like to be able to use Column J on the Source Tab to populate the details on the Manager Entry tab.
I have attached a sample spreadsheet if I've not explained very clearly.
TIMESHEET sample.xlsx
View 9 Replies
View Related
Apr 18, 2009
I want to populate a Data Validation based on values in another cell on another worksheet--but, I want to populate it with the values stored in the cell right next to the cell. http://i32.photobucket.com/albums/d3...n/untitled.jpg. would be an example....
What I want my Data Validation List to do is to look at column B and wherever it sees a certain color--for example, red, I want it to put the corresponding value in column A in the list.
View 5 Replies
View Related
Feb 17, 2010
I'm working on a few dynamic KPI charts and could use a little help with figuring out data valuation for the End Date value. In the attached workbook I am using data validation to pick a start date from a dynamic list (Dates) in E2:Exxx. Cell B2 has 2/8/2010 picked. I would like to have a drop down in B2 show the listing of all dates in E2:Exxx which occur after 2/8/2010, so the filtered list would be in the range E3:E49.
View 2 Replies
View Related
Sep 26, 2007
What I would like to do is hide rows 27:30 if the data validation list is equal to "Select Product...". The list is in B27:B30. I've search the forum but couldn't get the codes to work...
View 9 Replies
View Related
Nov 6, 2003
I've 12 numbers for 12 months in Cells. 13th Cell is the sum of all these 12 Cells. What I want is - Can I have a Data Validation on the Sum that it should not be more than 100. So, the users can enter the values in 12 Cells to make it equal to sum of 100 only. I know, I can do in VB Code but if it is possible to do as a function or Data Validation..
View 6 Replies
View Related
Mar 11, 2014
I've got a spreadsheet and I've used data validation to look at a list on another tab.
I need to be able to multiply values out based on what i choose on the drop down menu.
I've attached a sample spreadsheet to explain : tasks.xlsx‎
View 3 Replies
View Related
Mar 31, 2014
Is there any vba codes that can show tab/sheet that is based on the filter selection.
For example:
Cell A1 have a data validation of Apple, orange and Mango
I have 3 tabs named Apple, orange and Mango.
So every time I select Apple on the main in page filter. Apple tab will show. and if orange then orange tab will show.
View 3 Replies
View Related
Jan 11, 2013
I am building an Excel file that will be used to track information and at the core of it all is a list of people from different offices where the number of people per office can change and/or a person leaves the company and is replaced by another. I'll simply:
Column A Column B
Office Employee
Hamilton Emp 1
Hamilton Emp 2
Hamilton Emp 3
Toronto Emp 4
Toronto Emp 5
Toronto Emp 6
Toronto Emp 7
Toronto Emp 8
Waterloo Emp 9
Waterloo Emp 10
This will be all on Worksheet 'Info'. I have a Worksheet for each Office and named them accordingly. On each worksheet I want to use Data Validation on a column, we will call it 'ChosenOne', set it as 'List' and have the Source pull all the employee names that belong to that office and use them as a selection
ex: Hamilton Worksheet, 'ChosenOne' would show Emp 1, Emp 2, and Emp 3 in the list.
If Emp 3 changed offices to Waterloo 6 months from now I would like to change A4 from Hamilton to Waterloo and the formula would not have to be changed and the next time someone selects 'ChosenOne' it would only show Emp 1 and Emp 2.
Of course this means on the Waterloo Worksheet, 'ChosenOne' would show Emp 3, Emp 9, Emp 10 now.
So basically I am trying to not specify a specific named range for each office and am hoping there is a way to poll information from a Table (or any other tool that can simplify this).
I would be ok with something like:
Column A Column B
Office Employee
Hamilton Emp 1, Emp2, Emp 3
Toronto Emp 4, Emp 5, Emp 6, Emp 7, Emp 8
Waterloo Emp 9, Emp 10
and just move Emp 3 from B2 to B4 but I don't know if a list can be created from multiple items in a single cell seperated by a , or ; or :.
View 4 Replies
View Related
Oct 8, 2009
I am creating a disclaimer in one of my sheets.
Cell I15 contains 'Are you a resident for tax purposes?' Cell N15 has the data validation list of Yes or No.
Then I have a button below which covers rows18, 19 and 20.
Is there a way I can have this button hidden until Cell N15 = Yes?
View 10 Replies
View Related
Dec 19, 2012
I am trying to see if it is even possible to have data validation applied to a specified range of cells, but if the value of cell 'B3' is '1' then the data validation will not run on the other cells (H4:G7)
View 2 Replies
View Related
Feb 6, 2008
I want to select a Hyperlinked file.These files are named using Data in ColumnA,B & C. The realtionship between filename and data in columns is; A-B-C.ext. After selecting data A from the dropdown list,i should be able to select corresponding data B and then again another dropdown list for corresponding data C.This should show the desired already hyperlinked file. I want to select the hyperlinked file name by simply selecting all the three data independently.
View 3 Replies
View Related
Aug 10, 2008
I'm working on a simple worksheet, it is a tracking sheet for programs taught. What I'm trying to find is a code to insert a row based on a Data Validation List.
Exampl of data:
Column A is for " Name" then futher down Column K is "Program Taught" and Column L is " Date Taught".
Column K is where the Data Validation List is located with a in cell drop down.
What I would like to do is when a user selects a program from Column K a row is inserted with all of the formulas and formats from the previous row, and upon insert would like it to copy Column A into the new row.
This would be so I can keep a historical of programs and dates taught.
And to really spice things up I want to lock Columns K & L of the previous row after the new row is inserted.
View 4 Replies
View Related
