Validation And Lookup Function Are Working Weird
Sep 2, 2009
There is a range of quarters and relevant dates like from and till - means if you have 1 Q 2009 => it means from 01.01.09 till 31.03.09. I would like to have following in my excel. There is a validation for selecting Q from list, once you select Q, there is LOOKUP function displaying from and till dates. But however it's not working, in same cases it's working, but in most cases the from/till period is not relevant to selected quarter.
View 2 Replies
ADVERTISEMENT
Jul 23, 2014
I need to compare 2 cell with 1 specification reference.But the function can't give return value as per required.Both 2 input cells using Data Validation List.
Please refer attachment for some examples : matching.xlsx
View 2 Replies
View Related
Feb 19, 2008
I'm trying to sum a certain column. The column is dynamic and depends upon the threshold. So this is what I have
Sheet1
Div | 100+ |130+ |140+ ( might tell me (140+)
View 9 Replies
View Related
Mar 17, 2009
I have some weird problems that I don't know how to fix (or even explain). Mostly, my code is good and works well in all my spreadsheets but I find myself trying to debug stuff that should be working, but isn't working 100% as it should.
For example, I have a cell that counts information using
=NB($A$8:$A$1000)
BTW: NB is COUNT in french, just so you know...
The weird problem is, it seems to skip the first 10-15-20 rows; it doesn't count them. So the total in the cell should be around 150, but it's 129?...
Can someone give me an alternative code to count cells?
View 9 Replies
View Related
Jul 9, 2012
The code below is fired from a Worksheet Change Event on cell c18. The cell has a userform calendar control to select Date of Birth. What I want the code to do is check the age at the current date and if it is below 16 or over 25, then show the appropriate message. I cannot use data validation to not allow values outside this age range because there will be instances where a person's details can be added if their age is under 16 or over 25. I just need to alert the inputter in case they are unaware or have inputted incorrectly.
When the message box pops up, if they click Yes then the code should take them to the next cell for inputting. If they click No then it should stay in cell C18 and hopefully pop the calendar back up (I've not tested this bit yet).
The code as it stands brings up the "This person is under 16 years...." message no matter what date of birth is input.
VB:
Sub AgeValidation()
Dim age As Integer
Dim dob As Range
Dim AgeMsgAnswer16 As String
Dim AgeMsgAnswer25 As String
Set dob = Worksheets("Monitoring Form").Range("c18")
[Code] ....
View 3 Replies
View Related
May 30, 2008
I have a client with a list of books, and they are trying to sort a column in a sheet that has LC classification codes. When just doing a normal A-Z sort, it looks like this:
RJ 41 .D53 F54 2005
RJ 416 .L4 C52 1999
RJ 420 .D5T97 2003
RJ 421 .H385 2006
RJ 426 .H43 P43 2005
RJ 431 .M37 2003
RJ 436 .A8 R33 2006
RJ 436 .A8 T498 2001
RJ 436 .A8 T87 2003
RJ 45 .N4 2000
RJ 45 .N418 2006
RJ 45 .R92 1998
RJ 47 .E39 2001
RJ 48 .H37 2000
RJ 48 .H37 2002
RJ 48.2 .C537 2004
RJ 48.2 .P347 2005
RJ 48.2 .P385 2005
RJ 48.2 .R93 2003
RJ 480 .P34 2005
RJ 482 .R48 L448 2004
RJ 486 .C45 2000
What is weird about it is that I would think that the numbers after the RJ would be in numeric order but it seems to be treating them as codes. So, instead of RJ 41, then RJ 45, it goes on with a series of things that start with RJ 41. Is it normal for this to happen, or is there a way to change it so that it goes 41, 45, 47, 48, then 416, 420, etc.
View 9 Replies
View Related
May 13, 2014
I have a log that I have data validations on.
Column C validates on a list called "Division"
Column D is supposed to validate based on the selection in Column C, and give the list based on Column C.
I had it working awhile ago, then I went and changed the lists. Now, the lists are not validating appropriately (I'm not getting the expected drop down in D based on the selected in C) and I don't know why its not working anymore.
View 1 Replies
View Related
Apr 13, 2011
For some reason my data validation cell is not working when I first open up the spreadsheet.
I have to go to data validation, open it and just hit ok, then I am able to use my drop down list.
My data is this
"=OFFSET(name,MATCH(LEFT(B2,LEN(B2)),LEFT(address,LEN(B2)),0),0,SUMPRODUCT(--(LEFT(address,LEN(B2))=B2)),1)"
View 5 Replies
View Related
Mar 28, 2013
I have a data validation list does nothing upon start up (not working). The formula is:
=OFFSET(E$1:E$5,MATCH(C94,LEFT($B$3:$B$87,LEN(C94)),0)+2,0)
If I go into data validation and just click OK (leave formula as is), it starts working again, but the next time I start up the same thing happens.
View 3 Replies
View Related
Jun 12, 2014
I'm trying to add code to validate the format in which users are entering in a date within my userform, and I am also trying to validate if they enter in a specific value within a combobox, they will be unable to enter data within a textbox further down in the userform. Here is how my code is currently written, but neither my date or data validations are working. I have bolded the new coding I added to an existing code that was created by someone else at my job prior to it being given to me.
[code]
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
[Code].....
View 14 Replies
View Related
Jan 22, 2014
I have a UserForm with Combo Boxes, List Boxes, Text Boxes etc. When the OK button is clicked I want it to ensure certain fields are not left blank and notify the user (see code below). This works great for the combo boxes but not for list boxes. It seems to skip over that code. In other words, in the code below, if a manager is not selected, it doesn't notify the user.
Private Sub OkButton_Click()
'Verify fields are not left blank
If cboAgent.Value = "" Then
[Code]....
View 2 Replies
View Related
Apr 22, 2003
I have set cell validation to use a named list and checked 'In-cell drop-down'. The validation works but the drop-down is not working.
View 9 Replies
View Related
Jan 2, 2009
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
View 7 Replies
View Related
Jun 12, 2009
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies
View Related
Sep 6, 2009
In the following code snippet;
strFill = "Query for" & strCommName
strFill = strFill & vbCrLf & strPitHrs
strFill = strFill & vbCrLf & "This query has a rating of"
The cell output is formatted this way;
"Query for SP500
9:30-4:15 EST
This query has a rating of"
And thats how the text shows if you copy and paste it BUT in the cell there is a square box at the end of each of the first two lines. Is the vbCrLf causing that? If so what should I be using instead so the text in the cell shows exactly like the bolded text above
View 9 Replies
View Related
Jul 10, 2012
I have the following code to check the date of birth entered into cell C18 and to alert the inputter if the age is either under 16 or over 25.
VB:
Sub AgeValidation()
Dim age As Integer
Dim dob As Range
Dim AgeMsgAnswer16 As String
Dim AgeMsgAnswer25 As String
[Code] .....
It works to some extent in that the correct message box pops up if the age is under 16 and similarly if over 25. If answering Yes on either message box then the correct thing happens. Its what happens if the inputter selects No thats not right. If the age is under 16 and the user selects No in answer to "Is this correct?" then the code is clearing the cell contents and showing the calendar again but is also popping up the 'Over 25' validation message box which then won't go away until Yes is selected. Also there are then multiple copies of the calendar open.
What I need the code to do is look at the date selected from a popup calendar in c18 and decide if that age is within the 16-25 year old range. If it is outside that then the inputter needs to be alerted to it. I can't use the inbuilt data validation because there are some scenarios where it would be acceptable to have an age outside of that range but we want to cover inputting errors as well as double checking the age.
When a msgbox pops up to alert the inputter and they choose "Yes" to say the date of birth is correct then I want the focus to go to cell C20 ready to input the next piece of information.
When the inputter selects "No" on the message box, then I want the original date to be deleted and the calendar to reappear so they can select another date. So effectively resetting the field so they can start again choosing a date like when they first entered the cell.
View 4 Replies
View Related
Sep 11, 2013
I have a tracking sheet... in that I got in column C say "priority" there I have a drop down list date say P1, P2, P3
Now I need to restrict that if a user enters a data in the C2 and trying to enter a data in D2 leaving the drop down list data blank in C2. A error message saying "pls select priority forst" etc...
I got the following solution from moderator:
You can apply the Custom Data Validation formula =LEN(C2)>0 to D2, making sure that Ignore Blank is unchecked.
It works but, when I enter something in that cell it gives the error message and when I click OK or Cancel it just ignore the conditions and leave the data what ever I entered and moves further.
View 1 Replies
View Related
Oct 21, 2008
Data > Import External Data > Import Data
and specified that its a delimited text and the delimiter is a comma ",". One of the columns in the row has got a pretty long text and contains certain new line characters as well. After extracting the CSV the data in this particular column is treated as a different data entries.
Somebody please help. I have added the file in xls format since csv upload is not permitted.
View 6 Replies
View Related
Dec 12, 2005
A client of ours has one particular file that has some very strange behavior as described below. This only occurs with the one particular file and the behavior is consistent across different PC's in both their office and ours. All other Excel files in both offices work fine. Here's what happens:
- you can use Windows Explorer to copy the file between drives without
a problem (as expected)
- if you open the file in Excel on one drive then save it back to that
same drive with either the same file name or a different file name, the
file works fine (as expected)
- if you open the file in Excel on one drive then do a "Save As" to
save it to a *different* drive with either the same file name or a
different file name (in default .xls format), the new file causes
errors in Excel every time you open it as per below (weird!)
- even if you do a "Save As" and choose a different format eg Excel
97-2003 or Excel 95, the error behavior is consistent (weird!).............
View 2 Replies
View Related
Jul 1, 2014
I'm using the following code to try to ensure a value is entered into a text box, but when I click over to the next text box skipping the first one entirely, I don't receive any message indicating the previous box is empty. I'm trying to make it so certain fields are required and others are optional. Here's the code I'm currently using:
[Code] .....
View 14 Replies
View Related
Aug 21, 2012
I have the following function in cell (table 2 column C) where I want to find a contact name from my table TMI if 3 criteria are true (client, account, NAM) For each contact I have 1 true combination of client/account/NAM but for each client/account/Nam I can have several contacts.
{=OFFSET(TMI[[#Headers],[Contact]],MATCH(1,(TMI[Client]=$B$1)*(TMI[NAM]=C3)*(TMI[Account]=B3),0),,COUNTIFS(TMI[Client],$B$1,TMI[Account],B3,TMI[NAM],C3),1)}
The function is working well but shows only the first contact name if I have 3 contacts for the same client/account/NAM. (I pressed Ctrl+Shift+Enter)
When I press F9 on the function, it shows all the contact names! so they are somewhere.
Hence, I have copied and pasted this function into data validation / list in the source but it came up with an erreur.
Table TMI:
A B C DClient
Account
NAM
Contact
Comments
[Code]..
Table with offset function:
A B C D
Chi
Account
NAM
Contact name
Comments
[Code]..
All client, NAM and account lists are dynamic !
View 2 Replies
View Related
Jul 29, 2008
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies
View Related
Aug 20, 2014
In my table there is something wrong in array formula so that I'm not getting correct output in the table.
View 14 Replies
View Related
Dec 31, 2008
can anyone tell me why this isnt quite working?
View 5 Replies
View Related
Aug 25, 2013
I am doing the partial lookup to get the total amount based on a certain criteria.
For example in sheet1 there is a table and on sheet2 I have to pull out the data from sheet1.
Column A
Column B
Provision of Maisie System User -
56988.89
Provision of Maisie System User - first 12 free
7451.78
Provision of Maisie System User - first 12 free (rebate 1 May 08)
-487.25
[code]....
The formulas which i m using is =VLOOKUP("*"&$A$2&"*",$A$2:$B$10,2,0). Its giving me the first value as there are duplicates i need the total sum. For example:- Maisie amount which i m getting is 56988.89 but i want total maisie amount which is 56501.64.
View 3 Replies
View Related
Dec 4, 2007
When I use a lookup to look for "*"&A1&"*" where A1 contains "AB" I get a value not available error despite the fact that in the postcode table there are loads of postcodes beginning with "AB...". Is it the multiple entries of "AB" in the lookup table that will be messing this search up?
View 9 Replies
View Related
Dec 3, 2012
I'm trying to validate a cell in Excel 2007 that should contain only two letters.
Formula:
Code:
=IF(AND(LEN(A1)=2,ISTEXT(A1)),TRUE,FALSE)
When the value of A1 is JK, the result is TRUE
When the value of A1 is 12, the result is FALSE
When the value of A1 is 3K, the result is TRUE
When the value of A1 is K3, the result is TRUE
When the value of A1 is 123, the result is FALSE
When the value of A1 is JKL, the result is FALSE
When the value of A1 is 3, the result is FALSE
When the value of A1 is K, the result is FALSE
The LEN function works as expected, but the ISTEXT function does not, whenever the cell contains a number and a letter. I've tested the LEN and ISTEXT functions separately, and get the same results.
Is it me, or is it Excel?
View 7 Replies
View Related
Feb 18, 2009
I have a problem with a very simple Lookup (please see attached). When I copy/drag down the dropdowns in column A and the Lookup formula in column B, the lookup up does not recognise the value in column A till I save the sheet. The same if I select a different value from the dropdown i.e. the lookup only works when I save the sheet. Am using the wrong Function to do this or it something else.
View 3 Replies
View Related
Aug 27, 2009
I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
View 2 Replies
View Related
Mar 6, 2012
I have a workbook with two sheets. In first sheet(Data), there are three columns. In first (E-mail) there are random emails and program must check if email is correct and write it in column 2 or incorrect and write it in column 3.
What I need is to lookup also the domain extension if it is correct. I have domain extension written in sheet2 (DomainExt) and I need program to compare with sheet1(Data).
So: example@gmail.com (correct)
example@gmail.coS (incorrect)
Code:
Sub email()
Dim txtEmail As String
Dim rng As Range
Set rng = Selection
i = 2
For Each cell In rng
txtEmail = cell.Value
[Code] ........
View 6 Replies
View Related
