Excel 2007 :: Validation With ISTEXT Not Working When Number In Cell?
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
ADVERTISEMENT
Jan 6, 2014
I am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear
So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column
View 4 Replies
View Related
Jan 25, 2010
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
View 9 Replies
View Related
Nov 2, 2009
I am trying to use a simple formula: COUNTIF($CT$2:$CT:$430749,CT2)
I am trying to fill this down all the rows (430,749 rows). The formula behaves as its supposed to up to around row 650. After this point, all resulting values are all the same, which happens to be the same value that was returned from the original formula in C2. However, this is not correct. It's like the formula just stops working after row ~650. I tried F9 without success.
View 9 Replies
View Related
May 12, 2010
I need to convert numbers to text in excel 2007. I have done it before in 2003, but can't seem to remember exactly what I used. I may have used some kind of conditional formatting, but not sure.
Here is an example of what I need.
If I type "7203" in a cell I want "Home Repairs" to appear. I have a list of words associated with a list of numbers and want to be able to type the number in a cell and have the word appear.
View 8 Replies
View Related
Feb 14, 2012
I am failing on the logic and syntax to achieve the following. Basically it has to take the sum of 1, or 2 fields and validate it against the value of a 3rd field. I can get it to work for 1 field if DefPercent = 100 but not when looking at the total of 2 fields if DefPercent < 100.
Fields:
- DefPercent (if = 100 LiabilityAmt2.Enable = False) (this works fine already)
- TotalLiability
- LiabilityAmt1
- LiabilityAmt2
Output Required: logic only
If DefPercent = 100 And TotalLiability LiabilityAmt1 Then
MsgBox "error....."
Exit Sub
Else continue
If DefPercent < 100 And TotalLiability LiabilityAmt1 + LiabilityAmt2 Then
MsgBox "error...."
Exit Sub
Else continue
View 9 Replies
View Related
Oct 11, 2012
I am facing issue in Data Validation,
Currently i am using Excel 2007, in that i need to add a validation of Yes and No.
After entering the Validation in the drop down if any one copy any data and paste in the cell(Validtion) the validation vanishes.
View 3 Replies
View Related
Jan 6, 2010
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.
View 14 Replies
View Related
Dec 20, 2009
I wrote a macro to list all the excel files from a directory and its sub-directories to an excel sheet. It is working in excel 2003 but shows error (object does not support this action) in 2007. Actually I have copied almost this entire macro from excel 2007 help only. Can somebody modify this to use in both the versions of excel?
View 6 Replies
View Related
Dec 13, 2011
Arrow keys do not move from one cell to the next. They advance the page or panel view. No spreadsheet works correctly, (new or existing) and scroll lock is not set.
View 3 Replies
View Related
Sep 6, 2013
I recently had a virus on my computer and had to replace it. I had a ton of macros saved in my Personal.xlsb workbook, so I exported those modules and have now imported them to the new Personal.xlsb file. Now for some reason those macros no longer work in other files. Most of them are basic, like I have a macro to paste values or paste formats, etc. but I have some others that are very complex. The macros work fine as long as I am in Personal.xlsb, but if I try to use them in another open workbook I get the "all macros may be disabled or the macro may not be available in this workbook" message. I am using Excel 07 and have verified that macros are enabled.
View 3 Replies
View Related
Nov 24, 2011
i have a problem in that i need to generate a random 6 digit number preceeded by an X if the data in column B is either blank or 0. This needs to be filled in if the adjacent cell in column C has data in. i am a moderate macro writer and am finding this part quite challenging.
i am ussing excel 2007.
View 3 Replies
View Related
Jan 29, 2014
I have a spreadsheet which monitors the hours employees work - one of the rules is that an employee must have 12 hours rest between shifts...
I am using the below Data Validation formula in column I to highlight when an employee has less than 12 hours rest and an alert to appear
=OR(AND(I4-B4=0.5),AND(I4-B4>=0,I4-B4>=0.5))
Now this works perfectly with the exception of when an employee finishes at say, 18:00hrs on a Friday, and starts at 22:00hrs on a Saturday - Excel Calculates the Rest hours as 04:00, when it should be 28:00
I've had a suggestion to use the following:
=I4+I2-B4-A2>=0.5
(I2 being the saturday date, A2 being the Friday date) - this does work but only for Cell I4, If I copy the Data Validation down the column all cell references change - is there a way to keep the I2 and A2 in the formula without having to retype in every row?
View 1 Replies
View Related
Aug 14, 2014
I have searched and found many examples of code that will lock cells once data has been entered. But for some reason, I cannot get it to work in my workbook. The weird thing is, I have successfully got them to work on blank workbooks. I unlock a range I want people to be able to edit, I protect the workbook, I enter the code, and it works. But I do the same thing on my workbook, nothing locks.
I've tried locking all the cells on a sheet, and only unlocking a small range, as in the examples (A1:A10), and I cant get it to work. I've tried not pre-protecting the sheet, I've tried 4 or 5 different examples of code. If it wasn't for the fact I've gotten it to work on blank workbooks, I'd think VBA was disabled or something. I can't post a copy of my workbook for you all to look at because its on a stand-alone computer at work (and the USB is disabled for info-sec).
Excel Version 2007
View 8 Replies
View Related
Mar 23, 2012
Excel 2007, Windows XP Pro
Dim strLoan As String
Dim longCat As Long
Dim rHere As Range
Range("A1").Select
[Code] .....
I can see the cursor move to all the desired cells when this macro executes; it just isn't dropping any data where it should be. I have been working at this stage for the last five hours with no success. I don't know whether my copy-paste methodology is broken or if it is my selection criteria
View 1 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
Sep 6, 2011
What I have attached is spreadsheet I've built to quickly generate an army list for a tabletop game. Anyone can click on the Force Organization Slot (FOS) and choose the type of unit and from there, select the specific unit name attached to that FOS. Each individual unit has a list of options/upgrades currently attached to the sheet from cells AD through AX and the points cost listed next to each upgrade.
For example, when selecting the Rhino unit from the Dedicated_Transport FOS, there should be 4 drop down lists of selectable upgrades (Storm Bolter, Hunter-Killer Missile, Dozer Blade, and Extra Armor). Currently, I have only named the ranges for the Rhino and Razorback units in order to get this working properly without having to go through the entire list of units and change names/create ranges/ect.
What I'm aiming to do is to create an individual drop down list that is directly related to the Unit Name cell in order to populate a complete and selectable upgrade list for each unit. I have played around with some functions that work in the spreadsheet (see cells X10:X12) but do not work properly with Data Validation.
Oddly enough, when I enter this formula under Data Validation:
=IF($A$3<>$AD$1:$AD$182,VLOOKUP(A3,Options,2,FALSE),"")
I do not get an error, yet the drop down list does not allow me to select anything as I thought it would.
View 14 Replies
View Related
Dec 1, 2011
I just have a basic data validation list. I used "List" and checked the right boxes and the file has been working before. Now the same file does not stop the user from typing in anything.
I re-did the same data validation in a new file and it works. Was there something in the file that prevents it from working?
I have Excel 2007. I saved in both xlsx and xlsm formats
View 5 Replies
View Related
Feb 28, 2012
I have a VBA macro for Excel 2007 below that loops through a workbook and deletes a picture (shape) in a range at the top of each worksheet.
The macro works fine until a cell which contains a seemingly unrelated data validation list on Sheets(1) is changed. The macro then repeatedly trips up with a 'Run-time error 1004 - Application defined or object defined error'.
The cell with the data validation is outside of the range in which the shapes are deleted and does not set any of the variables in the macro.
Sub DeleteLogos()
Dim Count As Integer
Dim NumberOfWorksheets As Integer
Dim Logo As Shape
Dim LogoZone As Range
NumberOfWorksheets = Worksheets.Count
For Count = 1 To NumberOfWorksheets
With Sheets(Count)
[code].....
View 4 Replies
View Related
May 24, 2012
In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6
View 9 Replies
View Related
Dec 11, 2012
I am using excel 2007
I am attempting to put a drop down list using "Data Validation". I can get it working when my list is on the same sheet (sheet 1) and the column of cells I want the drop down list to show up in....(you know..when the drop down list shows up in each individual cell)......BUT...when I put the list on another sheet (sheet 2) and try to do the "Data Validation" back on sheet 1, excel won't let me go highlight the list on sheet 2.
I even tried writing sheet2 and the range and that still doesnt work.
View 1 Replies
View Related
Aug 22, 2013
I have a worksheet with several data validation dropdowns that each link to a series of hyperlinks. When you click the dropdown and select the hyperlink, it opens up that particular file. All of these work, except for one of the dropdowns. Even though I can see and select the names, the link is NOT activated.
Is there some obscure excel feature that I am not aware of that is preventing this one from working? Why would this be?
Excel 2007.
View 1 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
Jun 20, 2014
I am having trouble delete a drop down list inspite of selecting clear all from the data validation tab in excel 2007. When I hit Alt + Down Arrow, I still see picklist options. I didnt set up the spreadsheet, hence I am not sure how to begin troubleshooting.
View 1 Replies
View Related
Jan 22, 2013
Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
View 7 Replies
View Related
Jul 11, 2013
I am familiar how to perform the task of bringing in an access table into excel, then using a data validation filter to control the data set in Excel 2003.
How to replicate this procedure in Excel 2007?
View 1 Replies
View Related
Mar 20, 2014
I'd like to search a cell A1 containing for example 'hello_L765'. If the 7th character is L and the 8th character is text(ie not a number) than display yes, otherwise display the 7th character.
View 3 Replies
View Related
Jan 28, 2014
Excel 2013
Our company has a shipping report spreadsheet that has the job number in column A and the ship date in column F. I want to track other things on a separate spreadsheet. So far, we are only down to row 1440 so in order to account for future jobs, my code on the other spreadsheet is:
=VLOOKUP(A2, '[Shipping Report.xlsx]Monthly shipping schedule'!$A$1:$F$5000, 6, FALSE)
Also, the jobs are arranged by ship date, not by number order.
This has worked perfectly for months but for some reason, it will no longer recognize job numbers greater than 1331. The format is the same between the working and non-working cell. Is there a max limit for vlookups that I am not aware of? If this was a sudden failure, I could nail it down but there has to be an underlying reason I'm not seeing.
Here is a sample of my spreadsheet:
Ship Date
1325 12/27/13
1326 3/10/14
1327 1/4/14
1328 1/31/14
1329 3/3/14
1330 1/22/14
1331 1/15/14
1332 #N/A
1333 #N/A
1334 #N/A
1335 #N/A
1336 #N/A
1337 #N/A
1338 #N/A
1339 #N/A
1340 #N/A
View 9 Replies
View Related
Mar 24, 2009
I have 4 cols, x rows. I need Column C to check Column B for a numerical value, and if true, return the value in B, and if not true, then return value from A. Column B are functions formatted as general. The reference made by Sheet2!B is numbers formatted as text as to retain leading zeros. Here is what I have so far
=IF(ISBLANK($B10),$A10,$B10)
View 5 Replies
View Related