# Excel 2007 :: Data Validation Query - Sum Of Two Cells Less Than A Set Number?

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

## 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?

## Excel 2007 :: Workbook Use One Data Connection With Different SQL Query For Each Worksheet?

Oct 13, 2013

I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.

I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.

## Excel 2007 :: Data Validation With 24:00 Times?

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?

## Excel 2007 :: Using IF / VLOOKUP Formulas INSIDE Data Validation

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:

I do not get an error, yet the drop down list does not allow me to select anything as I thought it would.

## Excel 2007 :: Data Validation Not Stopping Invalid Input?

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

## Excel 2007 :: Data Validation List Crashes VBA Macro

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].....

## Excel 2007 :: Data Validation Based On User Input?

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

## Excel 2007 :: Data Validation List Linked To Different Sheet?

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.

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.

## Excel 2007 :: Dropdown Lists Not Deleting Even After Removing Data Validation?

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.

## Excel 2007 :: Data Validation List To Control External MS Access Table Embedded

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?

## Excel 2007 :: VBA SQL Query Length?

Feb 16, 2012

I've inherited an SQL server database which imports data from a multitude of spreadsheets each night. Unfortunately the previous data is not deleted from the database or spreadsheets, so there are duplicated records throughout the database. The method they have been using to get data out is to "select distinct", which gets around the problem (not my preferred solution).

I now need to produce an invoice from the database, so I select distinct records for the previous month (based on date) from, say, the TRUCKING table into a TEMP table, so I can do my calculations from there.

But there are a couple of columns which prevent me from getting only unique records. They are the unique ID column and the TIMESTAMP column they have added.

If I try to select all the individual columns, except for the ID and TIMESTAMP, the string length goes over 256 chars and the query truncates.

So I tried "SELECT DISTINCT * FROM TRUCKING WHERE etc etc" but I get the ID and TIMESTAMP fields, which defeats the purpose, and I get duplicate records.

Is there a way to say, for instance, SELECT ALL EXCEPT the fields I don't want?

## Excel 2007 :: Count Number Of Cells In A Range That Have Text

Feb 7, 2012

I'm using excel 2007, here's what I need.

I want to count the number of cells in a range that have text in them (any text at all) but not count them if they have numbers in them or are blank. How would this formula be written?

## Excel 2007 :: Inserting Number Of Blank Rows Between Cells

Mar 5, 2013

I have a worksheet with four data columns A,B,C,D starting in row 2. I want to add between every row, exactly 11 blank rows. So if we have:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

the end result should be:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

Do I necessarily need a macro for this? Or is there another quick and creative way to achieve this result?

## Excel 2007 :: Edit Query From Spreadsheet

Apr 11, 2012

I have recently installed excel 2007 how do you edit and ODBC query from the spreadsheet. In 2003 you right clicked on a cell that opened a dropdown where you selected edit query.

## Excel 2007 :: Script Error In WEB QUERY

Nov 7, 2012

I am using Excel 2007. I am having problem with web query. I want to get data from Komatsu parts catalogue, komatsu parts book, komatsu parts search, komatsu parts numbers, komatsu parts database this website. In excel 2007-> get external data tab->from web-> and i typed the website(Komatsu parts catalogue, komatsu parts book, komatsu parts search, komatsu parts numbers, komatsu parts database) and click go then i receieved Script error

Line: 3
Char: 1373
Error: Expected identifier, string or number
Code: 0
[URL] .....

yes no]

like that i received an error

## Excel 2007 :: How To Use Query Tables With Multiple Web Pages

Apr 22, 2014

I'm trying to query a web site and export the data to a single sheet in Excel. I've used the record macro function in Excel (2007) and come up with the following but I want to query over 20 pages from this site and wondered if there was any way in looping this macro to do the hard work for me;

"URL;http://www.futwiz.com/en/players", Destination:=Range("\$A\$1"))
.Name = "players_1"
.FieldNames = True
.RowNumbers = False

[Code] ........

On the second page the URL changes to;

URL;FIFA 14 Ultimate Team Players - FUTWIZ

and the .Name field changes to;

.Name = "players?page=1_1"

and on the 3rd page I get;

URL;FIFA 14 Ultimate Team Players - FUTWIZ

and..

.Name = "players?page=2"

## Data Validation Drop Down Query

Jan 12, 2010

Would anyone know if it is possible to extend the size of the list presented when using the data validation drop down? At present, it seems to default to 8 choices (even though there is a scroll bar) but I was wondering could the list Automatically display, say 20 choices?

## Excel 2007 :: Appending Sheet Number Automatically By Dragging Cells In Formula

Jul 12, 2011

appending excel sheet number in a formula. Here is the example.

In an excel book, I have sheets 1 to 50. In each sheet i have a standard format to enter the details. And in one sheet I want to keep some summary with required cells. I made a table by applying formula, for example in cell A1 i kept formula ='9'!\$D\$9. In this '9' refers sheet9. In the similar way i want to capture the data from all sheets.

A1 cell ='9'!\$D\$9
A2 cell ='10'!\$D\$9
A3 cell ='11'!\$D\$9....etc

Is there any formula to append the Sheet number automatically (like dragging the shell) intead changing sheet number manually. because it is wasting so much time.

## Excel 2007 :: Data Validation List Depend On Another DV List

Apr 29, 2012

I have a List of Different Fruits in Cells A1 to A5

Apple
Banana
Orange
Strawberry
Cherry

And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,

But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....

I Used the formula

=IF(C1=A1,OFFSET(A2,,,COUNTA(\$A\$2:\$A\$5),1),0)

But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)

## Excel 2007 :: ODBC Connection Query Shows No Result

Mar 25, 2013

Why this sql query doesn't return values? My excel version is 2007.

Code:
if object_id( 'TEMPDB..#TMPDOC') is not null
begin
drop table #TMPDOC
end

create table #TMPDOC (Code varchar(5) NOT NULL)

[Code] ...........

## Excel 2007 :: Insert Random Number Preceded By X If Adjacent Cell Has Data

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.

## Excel 2007 :: If Then Else And Validation

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

## Excel 2007 :: How To Add Validation Of Yes And No

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.

## Query That Pulls Data From A 2007 Access Database

Nov 11, 2009

I need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.

## Excel 2007 :: Auto Lock Cells Once Data Is Entered Not Working

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

## Excel 2007 :: Copy Column Of Data To New Location Ignoring Blank Cells

Apr 30, 2010

I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.

## Excel 2007 :: Macro For Finding Out Missing Number And Duplicate Number In A Range

Jan 22, 2013

Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1

## Data 'is Not Number' Character Query

Oct 5, 2009

They have a list of data numbers which are yy/#####/@@@@@ (2digit year/casenumber/alphanumeric code). Where some people have typed in the code wrong (6 or more digits in case number) we want to know is there a way of doing this.

Ideally I want something that says "If 9th character is a slash then yes, if not no" or "if 9th character is a number then yes, if not, no".

Example of data is (where we want third option highlighted as erroneous (6 not 5 numbers in middle)

09/56487/KFJT
09/42105/PLOUR3
09/002145/PLIFD
09/02145/ASDF