Excel 2003 :: Two Date Data Validations For One Cell?
Jun 8, 2013
I'm using Microsoft Office 365 Home Premium. Although this file is saved with Excel 97-2003 - because the computers at work only use 2003.
OK - Here is my dilemma. I've been creating a spread(work)sheet for work. One of the fields that needs to be entered by the manager doing the papwerwork is 'Date'. I currently have the Data-Validation set as follows because the paperwork being done is for the yesterday:
Code:
=TODAY()-1
I have the Error Alert set to Warning prompting the manager to ensure the date is correct (sometimes a mid-shift audit is being done, so the actual current date has to be used). Now, what I am wanting to do is to also set it that if the manager set's the date for a future date, I would like Excel to also either do a Warning or a Critical displaying a message that future dates cannot be used.
I know this may sound trivial, but with this paperwork, dates are extremely critical and vital in the event of an investigation for cash shortages etc.
I have uploaded the excel sheet to my domain, which I can provide a link to - either here or in a private message - but I do not want to do so unless I know that it's ok to do. If so, I will reply with a link to the location of the Excel file.
View 9 Replies
ADVERTISEMENT
Feb 21, 2014
I am using Excel 2003 at work.
New cases are entered on to the spreadsheet. The case remains open until a closure date is entered, at which point the case is closed. What I need is the following:
Cell A3 = should say "Open" if there is no value in cell Y3
Cell A3 = should say "Closed" if a date or any value is entered in cell Y3. Ideally it should only say "Closed" if a date is entered in format xx/xx/xx, but am flexible so that it says "Closed" if any data is input.
At the moment Cell A1 has a drop down list consisting of open and closed. I will remove this if it causes complication.
View 5 Replies
View Related
Aug 20, 2013
I am looking for a formula for a date.
I want the cell and column to highlight when the date has arrived, and I want it to remain highlighted for 7 days.
I have got as far as =F1=TODAY()
View 2 Replies
View Related
Jul 2, 2013
I have a scenario where a cell has to be restricted based on the user input of another cell, also it should allow only Yes or No values.
ex:column A
column B
pending response
submitted
User should be allowed to enter a value in column B as "yes" or "No" only when column A has a value "Submitted"
I referred the thread to restrict entering the data [URL]...
However not able to add list as well.
View 2 Replies
View Related
Apr 10, 2014
I am creating a Spreadsheet to work with a game which I am designing. I want to create a combat calculator which will generate formulas based on which class a person chooses to be, and their choice of weapons, armour and other equipment (makes things a lot easier for me to visualize). I have created the first worksheet which will act as the interface where users can input their character configurations and a second worksheet where all of the data is stored, included items and their values.
Assuming that on the interface I have cell A1 which will let the user select their class (Warrior, Archer, Sorcerer).
I also have cells B1 = Helmet, B2 = Body Armour, B3 = Leg Armour, B4 = Feet Armour, B5 = Hand Armour.
I would like cells B1:B5 to create a Data Validation based on the class which is selected in A1.
E.g.
IF A1="Warrior"; B1 = A list of Melee Helmets, B2 = A list of Melee Body Armour... etc
IF A1="Sorcerer"; B3 = A list of Magic Leg Armour...
I have managed to do this with the following formula, but only if the data is stored in the same sheet.
Cell B1
=IF(A1="Warrior", =meleeArmour1, =IF(A1="Archer", =rangeArmour1, =magicArmour1))
Cell B2
=IF(A1="Warrior", =meleeArmour2, =IF(A1="Archer", =rangeArmour2, =magicArmour2))
[NOTE]
=meleeArmour1 represents the range of cells where the Melee Helmets/Head Armour is stored.
=magicArmour2 represents the range of cells where the Magic Body Armour is stored.
The group cell names do not seem to work if they are on a different sheet, nor does it work if I replace the group cell name (=meleeArmour1) with the full formula (=Sheet2!A$1$:A$10$)
View 5 Replies
View Related
Apr 11, 2013
Data in cell A1 - 1) A Q
Data in cell A2 - 12) V
Data in cell A3 - 131) B H L
How do I throw away the #) and the following space in cells A1, A2, and A3 and save the rest?
Excel 2003. ie. no VBA.
View 4 Replies
View Related
Nov 12, 2009
On a worksheet I want 4 cells to have validation, when a selection in the first cell is made this limits the choice in the second, when the second is selected then the third is limited by 1 and 2 and the forth will be limited by 1,2, and 3.
In the attached example data , if I choose a Region, I have a choice of Countries. When I choose a Country I am limited to valid Cities and when I have chosen a City then a list of applicable Agents will be available. I have used NamedRanges for a two dimensional lookup but for four and my list will be far bigger than the sample, I do not fancy maintaining numerous NamedRanges.
View 5 Replies
View Related
Jan 17, 2013
Cell B1 contains a date, then B2 contains a formula that says:
=IF(A1>TODAY(),A1,A2)
A1 contains a green tick and A2 contains a red cross.
What I am trying to add is that if B1 contains no date then B2 needs to be blank.
I tried using =IF((A1="",0),(A1>TODAY(),A1,A2) to get it to show a 0 if there was no data but this doesn't work.
I am using Excel 2003.
View 4 Replies
View Related
Nov 28, 2013
I've got 2 worksheets in the same workbook.
Sheet 1 contains huge amounts of data - thousands of rows and multiple columns
Sheet 2 - I want to extract the data from sheet 1 column A into sheet 2 column A but only the data from every 21st row.
I want to be able to copy the formula automatically down, otherwise it will take hours to do it manually. So far I can only get the cell reference to increment by 1 each time after copying.
So what I'm trying to achieve is :-
Sheet 2 A1 = Sheet 1 A1
Sheet 2 A2 = Sheet 1 A22
Sheet 2 A3 = Sheet 1 A43
View 3 Replies
View Related
Aug 30, 2013
She has a spreadsheet created in Excel 2003 (she is still using 2003) which contains an image with a textbox in the top left corner then all other cells contain data.
When user prints this spreadsheet all that prints is the image and the textbox.
I have cleared the print area reset the print area removed the picture (prints only the text box)
and none of these changes have changed what prints.
I have checked all printer setting and these appear normal.
previously user could print this spreadsheet fine and all other worksheets in the workbook print fine as well.
other users are still able to print to the same printer correctly from this spreadsheet.
View 2 Replies
View Related
Jun 11, 2013
I have to analyze our abandoned calls for the year. So far there are close to 2000 abandoned calls and I need analyze them according to the month and the time they were received.
I have two worksheets in my workbook the first worksheet is named DROPPED, this is were all the data is. The second worksheet is called DROPPED CALLS SUMMARY, this is where all the analysis happens.
On the DROPPED CALLS SUMMARY Worksheet I need to have the Sum of Column I IF ColumnA is equal to Jan-13(or whatever month) and Column B is equal to 8:00 AM (or whatever time). I'm using Excel 2003 so I cannot use the SUMIFS Formula and I can't find a formula to work with.
A
B
C
D
[Code]....
View 5 Replies
View Related
Dec 29, 2011
Looking for a macro in Excel 2003 that will open a pop up box so that the user can enter a date to search for within the spreadsheet.
View 6 Replies
View Related
Feb 9, 2014
I have a very basic understanding of excel, I mean BASIC, for my work I have multiple dates for vehicles to go in for inspections. I would like the cells to change colours when within 30 days, 10 days and 5 days. I'm on a work excel 2003.
View 6 Replies
View Related
Mar 11, 2012
I'm creating some userforms for a spreadsheet on Excel 2003 and was wondering if it is possible to have the date field and time field as drop down boxes?
I would ideally like the date field to have 3 drop down boxes (date, month and year) and then the time field to have 2 drop down boxes (hours (in 24 hour time) and minutes).
The date and time would then populate one cell each on the spreadsheet.
View 9 Replies
View Related
May 20, 2012
I need a function to work out what the date will be 45 WORKING days after today(), this function needs to exclude Saturdays, Sundays and any Public Holidays i.e. there could be either 10 or 12 weekend days added into the calculation depending on when today() is plus any additional Public Holidays.
I am using Excel 2003 although it will need to work in Excel 2010 shortly.
View 4 Replies
View Related
Sep 10, 2013
I have a cell (A1) which contains the month. A2 contains the year. I need a formula which will return each date of the month.
View 7 Replies
View Related
May 7, 2014
The following formula works perfectly in Excel 2003:
=SUMPRODUCT((A2:A10="A")*(b2:b10="B")*(c2:c10="C"))
However, I've got a fourth column that contains either a date or a blank cells; I want to limit the formula to only include certain dates in the sum. i.e:
=SUMPRODUCT((A2:A1000="A")*(b2:b10="B")*(c2:c10="C")*(d2:d10>="01/04/2014"))
Rather than an error it's just retuning an incorrect number.
View 6 Replies
View Related
Jun 20, 2014
I want to use a formula, in another cell, to convert "Friday, 30 May 2014, 3:47:16 PM" to a value. I am using Excel 2003.
View 2 Replies
View Related
Apr 4, 2014
I am using Excel 2003. I have attached a data file here. getting the values in Q3, R3 and S3.
Scenario:
Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.
Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.
View 10 Replies
View Related
Jan 31, 2012
I've created a worksheet with a number of dropdowns / data validations. Now the thing with these validations is the source range apparently has to be in the same sheet (well it can be another one but much simpler if in the same). I've just realized I created them in the wrong place, so need to erase them. Pressing the delete key isnt working though, they're still there. I could right-click delete but that then requires one to move cells up/right etc and that could mess with my formulas.
View 7 Replies
View Related
Jan 28, 2007
I am using a modified version of Debra Dalgleish's filtercopy macro in which 3 data validations are being used (she used 1 in her downloadable example ProductList. zip) and it works just fine when the validations are used in order as they should be. However, in looking over the data I am trying to present in my report I think it would be very valuable to use the first and third validations at times (skipping the 2nd) to discern information in that way. Long story short, I can't seem to get this to work and don't know if it's even possible because I'm not a VBA expert.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 3 And Target.Column = 4 Then
Worksheets("StoreData").Range("O2").Calculate
Worksheets("StoreData").Range("Database") _
. AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("StoreData").Range("O1:O2"), _
CopyToRange:=Range("A6:K6"), Unique:=False
End If
If Target.Row = 3 And Target.Column = 6 Then...............................
View 4 Replies
View Related
Apr 13, 2009
On a tab, i have one column where information is entered. what should be entered is based on a dropdown list (which i have setup on a validation). the other thing that should be allowed to be entered is a an 8 digit number. when something else is entered, i need to have a popup message appear to tell the user to fix the error.
I have no idea how to effectively enter in the restriction in VBA for this.
Below is the VBA i have started:
1. The problem is that if one of the drop-downs is chosen, then i get this error message (because the dropdown list has text that is more than 8 characters in length).
2. The one other problem is that if the cell is left blank, i still receive the popup message. how do i avoid this from showing up.
3. I am also looking to do something like this for a range of cells (from F5:F100).
Private Sub Worksheet_Change()
Dim stue As String
stue = "f5"
Application.EnableEvents = True
If Len(Range("f5")) 8 Then
MsgBox "there is an error in cell " & stue & " "
End If
End Sub
View 9 Replies
View Related
Jun 25, 2010
I am having difficulty calculating the number of total overlapping days between several date ranges (6 of them to be precise)
I am using Excel 2003 and my data looks like this:
Sent Out (A) - Received (B)
01/06/2010 13:00 - 30/06/2010 13:00
02/06/2010 13:00 - 16/06/2010 13:00
09/06/2010 13:00 - 10/06/2010 13:00
21/06/2010 13:00 - 25/06/2010 9:44
23/06/2010 13:00 - 25/06/2010 10:56
23/06/2010 13:00 - 29/06/2010 13:00
I really can't wrap my head around the idea of calculating overlap (in network days) between six date ranges and was wondering if any of you would have a solution to this problem.
View 10 Replies
View Related
Oct 30, 2011
Operating System: Windows XP, Excel version: 2003
Aim: To create a Macro to hide all rows where the date in column D is before today. Column D has about 600 rows.
Current solution:
Code:
Sub Hide_Old2()
'Worksheet name
With Worksheets("Schedule")
'set start of date range
Set rngStart = .Range("D2")
'find end of date range
[Code] .....
The problem with this solution is its speed, or lack thereof. It causes the screen to hang and flicker while it cylces through. Is there some way to create a range based on the date and hide the range? or another solution?
Note: Autofilter is not an option, as the spreadsheet with the dates needs to be kept simple for other stakholders and the macro is being run from another sheet.
View 3 Replies
View Related
Dec 20, 2012
Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"
View 5 Replies
View Related
Jan 29, 2013
I'm loosing the validations that I set up such as having a dropdown list and so on when I save and reopen the file. First I thought I didn't save it, but notice it happening consistently.
View 3 Replies
View Related
Jan 6, 2009
I have search the site for a possible solution for my problem. And that is: how to create multiple lists using validation depending on each other.
The scenario is. I have a data sheet that contains a list of alien plant species. The species have different methods of treatments (some do have the same). Each treatment might have one or more herbicide products that can be used and with each herbicide a certain application mixture.
I have used validation to create the pick list of the plants, the next pick list should be the treatment for the selected plant species and the 3rd list the product and then last list the mixture.
All of these lists should only list the information that is relevant to the selected plant specie, in the first list.
View 9 Replies
View Related
Mar 6, 2007
I have an excel spread sheet, Col I is a Date, Col K is a Date, Col P is a date and Col T is a text(with list validation) I have validation that if if T = "As is" and P <> I or K (which ever one has a value) then Error. Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=IF(T2= ""Accept as is"",(IF(P2=(IF(K2<>"""",K2,I2)),TRUE, FALSE)), TRUE)"
But if the user changes T to some other value then it is ok to change P, But they could put any value in that field. I Need to validate that P is a DATE also
I was trying to do a validation Greater than > 01/01/2007 but I can not do multiple validations on the same cell. How can I validate P is a date also
View 3 Replies
View Related
Jun 19, 2014
I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.
A B C
7/1/2012 0:00825-CL-A-41091-REG4.00
7/1/2012 0:00825-CL-A-41091-REG6.25
7/1/2013 0:00825-CL-A-41456-REG4.25
7/1/2013 0:00825-CL-A-41456-REG6.50
1/1/2014 0:00825-CL-A-41640-REG4.25
1/1/2014 0:00825-CL-A-41640-REG6.50
3/1/2014 0:00825-CL-E-41699-REG3.00
3/1/2014 0:00825-CL-E-41699-REG4.00
View 14 Replies
View Related
Jan 6, 2014
I am having difficulty calculating the number of total overlapping days between several date ranges for each item in another sheet which has unique items
I am using Excel 2003 and my data looks like this:
Sheet-1 Sheet-2
Item - Sent Out (A) - Received (B) Unique Item Number of days excluding overlap days
1234 01/06/2010 - 30/06/2010 1234 -
4321 02/06/2010 - 16/06/2010 4321 -
1234 09/06/2010 - 10/06/2010
4321 21/06/2010 - 25/06/2010
1234 23/06/2010 - 25/06/2010
4321 23/06/2010 - 29/06/2010
I have used the below formula found from the earlier post but need to add a condition calculating the days for each item.
=SUM(IF(MMULT((NETWORKDAYS(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7)))),2)<6)
*(ROW(INDIRECT(MIN( INT(A2:A7))&":"&MAX(INT(B2:B7))))>=TRANSPOSE(INT(A2:A7)))
*(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7))))<=TRANSPOSE(B2:B7))+0,ROW(A2:A7)^0),1))
View 6 Replies
View Related
