want to get a formula where I can get the details of City if searched Brand wise and details of brand city wise. I also want option to search say five city names at once and get the brands available in all those cities. I have updated a excel with some dummy data.
View 7 RepliesEssentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.
Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).
Also, the Data Validation dropdown includes blanks which I would like to exclude.
I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.
I have a workbook with two tabs, the first tab "Query" is an Access query that pulls in call data from our phone systems. The second tab "Report" is a userform/report. This will be used by numerous people and has to be as userfriendly as possibly. Because our clients want the data sent this way I don't have a lot of room in changing the report itself.
Let me paint the picture, I have it so the user is able to select the month from a validation list I created in B1, when the month is selected is autopopulates each work day of that month in cells A13:A35. There are several (28 total) reports from different phone lines we have. So I have another validation list in cell B3 that has the name of each report we have. So in theory the user should select the month and report and the information will fill in and they can print that specific report off. The issue is this. I had used the formula =SUMPRODUCT(--(Query!A5:A25004=Report!A13),--(Query!B5:B25004=Aban_08),Query!E5:E25004). I'm trying to find total calls for specific lines for specific dates. The "Report" tab has information from January to current with all 80 different call lines (all named differently such as CDN_5700, CDN_5701). One report such as "Aban_08" actually pulls information from several call lines which is bringing me to my issue, I need one tab for the report, and be able to have the user select the different report, but each report pulls from multiple call line names. Again, I need (for instance) B13 (cell A13 is the date June 2) to pull all calls from the report tab for the date of June 2 with (for example) the names "CDN_5700","CDN_5701","CDN_5702".
iam looking for formula to display the condtion result .
i have spec in col B and tolerance in col C and col D ( +ve & -ve tolerance respectively) from col E to I ,observed reading data & col j for results.
i want to check the value of col E to I for B C D value n if entire 5 values are within spec then display "0" in subsequesnt col J else display "1"
OBSERVEDRESULTS.L.NOSPEC`+TOLERANCE`- TOLERANCE123451250.20.2262525.225.425.22100.20.2350.20.2450.20.25510.20.2
I am trying to make a simple planning sheet.
I have a workbook with 2 sheets.
1.sheet contains Columns : DayDateTimeClassTeacherCovered
2. sheet contains the availability of teachers. so all columns start with a date and the rows contain the name.
A row therefore looks like this.
7/1
Harley
Lidia
Livia
Thais
Salete
Carla
My issue: The 1.sheet has a column Teacher, now in this column I want to have a drop down list. (E2, E3, E4, E5 ... etc..) This list is depended on the date which is in column B.
Essentially I want the list to look up date in its row go to the second sheet and give me all the possible names in it. That is all.
I would like the individual cells of C1 and D1 to allow me to input values up to 100. The tricky part is that if D1 equals 100 I would like C1 to be less than 100 and vice versa, if C1 equals 100 then D1 should equal less than 100.
Ex. C1 = 100 & D1 = 10
This should never happen: C1 = 100 & D1 = 100. I would also like an error box to show when either of the cell values exceed 100.
I have a cell that uses list type data validation. I need to be able to update the list of allowable values via a macro and would greatly prefer not using a range in excel.
I've been able to create the string that I want as the formula but cannot change the formula. I'm not very familiar with this aspect of excel macros (data validation)
How do I hardcode the Offset function? Right now I am placing the offset formula in Validation from the Data menu toolbar.
Also, I have two columns "Expense" and "Income" used for offset. Expense has 11 selections for the listbox, and Income has only 3. When I choose income from the dropdown in column B, I get way too many blank selections in the dropdown just because It doesnt contain as many items as Expense. How can I re-code adn hardcode the formula so I have only few blanks for my selection.
I have attached a copy "data validation test" to help make things more clear.
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies View RelatedI'm trying to set a data validation in B1 from values in A1:A5.
A1: Car
A2: Papers
A3: Pencil
A4: Pants
A5: Shoes
What I want is show in dropdown list the words in singular (without the "s" at the end).
The formula below works if I introduce it in a normal cell,
[Code]....
or as array formula with CRTL+SHIFT+ENTER in this way
[Code] .....
But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.
if it is possible to have data validations on a cell within Excel but allow a user to input a formula? The data validation is a decimal between 0 and 1 and the formula would be a vlookup.
View 4 Replies View RelatedI am trying to use the validation feature to calculate a check digit and the formula is too long to put in validation. I rewrote the formula to be shorter using math features instead of text, but apparently you can't use arrays in validation. Because of certain distribution constraints, using VBA is not possible so a UDF is out. Can either of these be shortened in anyway? I am not sure if it is even technically possible to solve this problem with these constraints.
Here is the "human readable" explanation of formula:
(Accept only 10 digit numeric value.)
Example Number: 123456789-3
Digits 1,3,5,7,9 are multiplied by 2 (weighted 5)
So 1,2,3,4,5,6,7,8,9 becomes 2,2,6,4,10,6,14,8,18Weighted 5's multiplied values then have their individual digits added (ex:12=1+2=3)
So 2,2,6,4,10,6,14,8,18 becomes 2,2,6,4,1,6,5,8,9
Weighted 5's sum is added to the sum of the Nonweighted 4 digits.
So 2,2,6,4,1,6,5,8,9,3 becomes 43
Add 24 to sum.
43+24 = 67
Checksum = (10-(67 mod 10)) mod 10
(Sum round up to 10 minus sum)
Method 1 ...
I have a range named as follows in a formula:
BaseCase!$O:$O
I have a data validation from which the user can choose another tab.
So, how do I change the reference above to:
TabName!$O:$O
so that TabName refers to the text string in cell B2 (data val. box)
I tried to use TEXT to no avail. I imagine there is a simple solution, but I am at a loss.
INDIRECT perhaps?
Is it possible to validate a string with both numbers and text with a formula using data validation? (I don't want to use a User Defined Function)
in Column C, Starting in cell C2 I have a 12 character string of six numbers, four letters, and two numbers. like this:
280838ZNOV11
is it possible to use a Custom Data Validation formula to ensure the user follows this format?
I am trying to get my array formula to recognize the text selected in my data validation cell. The formula worked perfectly until I decided I wanted to try and use a data validation list to eliminate spelling errors. Even though the text I am trying to recognize in the cell is exactly the same, because it is in a data validation format it no longer works. Is there a way I can modify my formula so it works? Example below...
formula: {=SUM((A2:A109>DATE(2013,10,31))*(H2:H108="Joe Blow"))}
A2: 11/2/2013
H2: Joe Blow (chosen from a data validationi list)
The result of the formula should equal 1 in this example, or more if the same criteria occurs more than once within the range of A2:A109, and H2:H109.
Can I limit the text length in a cell that contains a formula? You may say "Limit the text length in the input cell". That can't be done because the formula in the resultant cell takes text from two other cells in addition to the input cell. I need to limit the overall text length to, let say, 50 characters.
View 7 Replies View RelatedUsing frequency in a data validation formula for ensuring unique entries.
Currently I use COUNTIF($B$2:$B$244,B2)
I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:
I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells.
I have create an excel formula that I need to input data daily.
Every day, I need to insert a line and input my new data.
Below is my formula
=IF(V159="Skip","Go",IF(P160>0,IF(COUNTIF(C60:D154,">"&H159+3),"Good"),IF(COUNTIF(C60:D154,"<"&H159-3),"Good")))
The problem is that when I insert a line. Most of my variable will change according except C60:D154
As you can see below
V159 change to V160 = This is good
C60:D154 do not change accordingly. How can I make it change accordingly ?? Why it is not changing when I insert a line ?
=IF(V160="Skip","Go",IF(P161>0,IF(COUNTIF(C60:D154,">"&H160+3),"Good"),IF(COUNTIF(C60:D154,"<"&H160-3),"Good")))
I require a custom data validation formula to limit cell value to maximum of 4 decimal places.
0.0001 ok
0.02 ok
0.3 ok
0.12345 fail
0.123456 fail etc
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies View RelatedI would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
I had some of values in Column A, B & C for ex: column A has brand name, column B has model name and Column C has sub_model name here i have a limited values i need to make it as drop down list but i had a problem with the below formula.
=IF($F$2=Sheet2!$H$2,al_v,IF($F$2=Sheet2!$H$3,am_v,IF($F$2=Sheet2!$H$4,au_v,IF($F$2=Sheet2!$H$5,be_v,
IF($F$2=Sheet2!$H$6,bmw_v,IF($F$2=Sheet2!$H$7,bg_v,IF($F$2=Sheet2!$H$8,cv_v,I
F($F$2=Sheet2!$H$9,ch_v,IF($F$2=Sheet2!$H$9,ch_v,"")))))))))
i need to add some more ifelse but the validation don't allows it.
I have a Yes/No list that I use in two cells for data validation:
Cell A1 has the Yes/No list for data validation.
Cell A10 has the Yes/No list for data validation.
I want A10 to become No if the user selects "No" in the A1 pull-down list.
I am trying to validate a Grid Location, example: 22AX21321232
How do i validate letters in the formula?
I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
I want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
I have a drop down validation list on sheet A that refers to my list on sheet B. I would like to formulate a cell on sheet C to give a value from a cell on sheet D based on which item is chosen from the validation list.
View 4 Replies View Relatedis it possible to first make an IF check on a cell and then as one of the results (true or false) to give the cell a Validation List?
So for instance if the IF will come out true, the Cell would just diplay some text and if the IF will come out false, the user will be able to choose something from a Validation Drop-down List?