I have a problem in applying Data Validation on cells containing Formula. Eg: cell A1 has a value 10 and cell B1 has a value 15. i have a formula "=A1+B1" in cell C1. If i apply a Data validation to the cell C1 so as to accept values only in the range of 0 to 10, the cell C1 is still accepting values grater than 10 when i change the values in the cells A1 and B1.
View 2 Replies
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.
I have a auto filtered applied to 1000 rows of data and the actual quantity of rows I currently see is 55 rows. When I do a filldown command to apply this formula, "=CONCATENATE(X$1,I51)" the result I get is wrong becuase where the "I51" value is at the formula is picking up cell values that are hidden due to the applied autofilter.
What do I need to add to this formula so that it only picks up cell values that are not suppressed by the autofilter? I want it to disregard the hidden rows when I do a filldown and only apply the formula to the rows that are not hidden due to the applied autofilter.
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
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
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.
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.
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$)
How would I write a formula if I was trying to get the cell to count all expired certifications (based on a range of cells and their expiration dates) as well as all blank cells? This is what I have so far and all it's doing is displaying "FALSE"... =COUNTIF(D2:D95,"
View 5 Replies View RelatedI want to take the value in I and apply a 10% discount to it and place the new value in column M. Would it be possible to have this new value rounded up or down to the nearest 9.
Example:
Column I=7.99
Column M=7.19
Column I=7.59
Column M=6.83 (however I would need it to be rounded to the nearest 9. The price I would need would be 6.79)
Column I=2.19
Column M=1.97 (however I would need it to be rounded to the nearest 9. The price I would need would be 1.99)
I'm trying to apply the code for accumulating value in a single cell to a range of cells.
I want to use the code found in this thread [URL] ..... but, it only lets me edit "B1" to another single cell. I need my cells G4 to G469 to operate the same way.
How to edit the code so I can include a range or show me a version of the code where I can enter my desired range?
The above formula is not working as in A9, I have a formula although there is no physical text in the cell as the criteria applied is false. However, B9 still appears.
=if(istext(a9),b9,"")
on VBA in regards validation rules in Excel?
View 9 Replies View RelatedI need to edit multiple validations in a worksheet. There are over 990 validation for each month. I want to change the word in the sentence. Example: April to May. Right now I have to edit each one seperatly. I have tried a macro but it changes to whole sentence. Find and replace do not allow editing in a validation.
View 9 Replies View RelatedI'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 RelatedI 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...............................
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 RelatedI am trying to paste formats and validations to the current row of my spreadsheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targetcol, ganntfirst, ganntlast As Integer
On Error Resume Next
Application.EnableEvents = False
targetcol = Range("BudgetHrs").Column
ganntfirst = Range("Ganntfirst").Column
[Code] .....
The code in orange is the problem code, and I have a range named format which is actually Row1:1
And I want the formats and data validation pasted into the current row and also the next row.
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.
In MS Excel, I find that when the zoom is dropped to 50% the fonts in the
list become unreadable. Is there anyway that the fonts in the list have the
ability to have their own formats?
I cannot acheive to have a {Sum(if(...)} formula applied to whole columns. See below.
The following formula return a numeric value. So it works.
{=SUM(IF(Query!$L1:$L65535=TEXT($A37,"@"),Query!$AM1:$AM65535,))}
(confirmed with Ctrl+Shift+Enter)
The following formula returns #NUM. So it fails.
{=SUM(IF(Query!L:L=TEXT($A37,"@"),Query!AM:AM,))}
(confirmed with Ctrl+Shift+Enter)
how to use {Sum(if(... )} to whole columns?
On Sheet 1 I have Data Validation Lists set up. On Sheet 2 I have the information populating from what is entered on Sheet 1, only problem is...when I don't have anything entered in the cell(s) on Sheet 1 then the cell(s) on Sheet 2 reflect 0. I would like the cell(s) to remain blank if nothing is entered on Sheet 1.
View 4 Replies View RelatedI have created a Data Validation list in one of my spreadsheets which works fine. The list itself has no duplicates which is fine but when I select my entries in the other spreadsheet, I want to make sure they cannot select the same value twice.
I tried selecting "List" in data validation and using an AND with COUNTIF but it doesn't work.
=AND(Employees, countif($A$1:$A$20,A1)=1)
How to populate data between 2 linked file based on the Dropdown Validations?
View 10 Replies View RelatedI have a very simple workbook in Excel 2000 with one page containing all my data which I update on a daily basis. Some of the data is linked to another workbook so that it updates each time I open it. Then I have 12 tabs, one worksheet for each month of the year with 10 simple graphs on each worksheet.
As you can see, it is a very simple worksheet. Each month, I copy and paste the graphs from the previous month into the current month and simply alter the Source Data in the graph to gather the current month's data. At the start of this month, it allowed me to copy and paste, but when I tried to alter the Source Data to the current month, I got a window on screen with a yellow triangle containing an exclamation mark and the words "No new fonts may be applied to this worksheet". If I click the OK button or the cross, it responds but the whole programme is locked. The only way I can get out is to give the 3 fingered salute and close Excel down.
I have a workbook with 33 worksheets and more than 150 charts. Now, when I right-click on a chart to access "Source Data" for example, I get a message saying "No more new fonts may be applied in this workbook". Then I have to click "OK" about a dozen times before I get access to the dialog box. Curiously, it happens for some charts and not for some others.
View 7 Replies View RelatedIf filtering column C, just showing 'A's, by pressing the add row button, I want to copy the last cell in column C and pasting it in row 73, since it's the next unused cell when the worksheet is unfiltered.
View 2 Replies View Relatedin this example below i need to work out what the divsor would be for values inbetween the data?
View 9 Replies View RelatedI want to have the macro delete the color applied once a new "Row" is selected!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RR As Range
Set RR = Target
Cells(RR.Row, 2).Interior.ColorIndex = 36
Cells(RR.Row, 3).Interior.ColorIndex = 36
Cells(RR.Row, 4).Interior.ColorIndex = 36
End Sub
I have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?
View 9 Replies View Related
