Allow only values contained in a list to be entered into a cell - Data Validation

Hi all,

I have built a formula which is working fine, but i was wondering if you could provide me with a more elegant one. Here is the idea and the formulas i used:

I have a budget sheet where all employees are stored with some basic data and some salary data.
Basic data are columns A to D (A5= id, B5= Name, C5= Department, D5=Position)
Salary data are columns E to H (E5=Salary, F5=Raise (%), G5= Bonus, H5= Employer Contribution). Columns M5 to X5 are months, Jan to Dec. Column Z5 =Total Cost (summing months)

What i want to do is to calculate the total cost for each employee throughout the year, based on the numbers entered in Salary Data columns. Using the following formula ( E6+($E6*$F6)+$G6+$H6 ) i calculate the cost of one employee . The tricky part is that i wanted somehow to calculate the cost IF midyear the company decides to alter the strategy and for example instead of 2% raise, decrease it to 1,75% starting from March for example.

So i added three helper columns. Column I with a data validation list with months. Column J with the new percentage. Column K where i calculate how many months the cost will be calculated with the original Raise% (F6) and how many with the new one (J6).

Cell K6 = MATCH(I6;$M$5:$X$5;0)-1. If in I6 i choose March from the data validation list, K6 gives 2 which corresponds to January and February where the original Raise% will be applied, whereas the rest 10 months (starting from March) will be using the amended Raise% in cell J6

Cells M6 to X6 holds the following formula which gives me the result i want

=IF(MATCH($I6;$M$5:$X$5;0)=1;$E6+($E6*$F6)+$G6+$H6;IF(MATCH($I6;$M$5:$X$5;0)>1;IF(COLUMNS($M5:M5)

I am using this Code..
but here if ListStr(vaules from table--oracle DB) size is more than 255 char.. but in spread sheet cell list it's showing only values upto 255 chars, so how to use cell range for this. Here i don't want to put values in any cell...but i want all values from ListStr(vaules from table--oracle DB).

Please help for this.
This is the code:

Public Sub AddValidationForRange(Range, ListStr)
ActiveSheet.Range(Range).Validation.Delete
ActiveSheet.Range(Range).Validation.Add Type:=xlValidateList, Formula1:=ListStr , AlertStyle:=xlValidAlertStop
ActiveSheet.Range(Range).Validation.ErrorMessage = "You must select a value from the dropdown. If the value you want to add is not present in the dropdown, click Cancel & select 'Add a value'"
ActiveSheet.Range(Range).Validation.IgnoreBlank = False
End Sub
---------
While vRecordSetWindowTable.EOF = False
ListStr = ListStr & vRecordSetWindowTable.fields("WindowName") & ","
vRecordSetWindowTable.MoveNext
Wend

Thanks,
Manisha.

I need to set data validation from a list and I need the list of values to dynamically display the unique values from a list in a range. So if the list had:
111
222
222
333
333
333
444
444

I need the dropdown to propose:
111
222
333
444

I know how to do a dynamic range name, it is the unique value bit that I cannot do. I would prefer a single formula that can be entered in the data validation dialog as opposed to creating multiple ranges and then pointing the list to the last one.

TIA,
Ken

I have a page within a workbook that contains all of the data "Master" I would like to take specific information (located in a column) and place it on a new sheet. ex on a new sheet I type in the name john doe I want it to look at the master sheet filter it for john doe and copy that information to a new sheet any suggestions

Hello all.
I have a Validation drop down box (column B) and a defined range drop down box (column E linked to the Totals tab with A2-A31 defined as Products) that I need to activate everytime data is added to a new line in column 'A' and cells in column 'B' or 'E' are selected. Additionally, they need to activate if the cells in column 'B' or 'E' are selected for any of the old data so changes can be made. This will help reduce the overall file size and calculation time for a year's worth of data.

There is already some code on the worksheet that looks like it can be adapted if someone knows how to code in the drop down boxes.

Thanks in advance for your assistance!!!

Andrew

Hi Excel People

This is my first time asking a question on here, so please let me know if I should post it elsewhere.

I have a diary of court trials, with a sheet for each month - the example I have attached is for just a few months.

I want to extract a list of all trials that are longer than ten days, the first day of a ten-day-plus trial is currently entered as the file number with a plus symbol (alt-entered for a new line in the cell), e.g.

D098564
+


These file numbers are entered into the range called Trial_Info (it is the same on every sheet).

I want to check the Trial_Info range on every sheet in the workbook, and return a list of all entries that contain the plus symbol.

I want the list to also look at column B to return the trial date.

The list would ultimately look like:

19/10/09 D071392
20/10/09 D07994
20/10/09 S08355

etc.

I'm not too fussed if the plus is there in the list also (e.g. 19/10/09 D071392 +).

I have tried LOOKUP and VLOOKUP functions but have got stuck very early, what is the best way to approach this. A macro that extracts the info into a new workbook or a text file (or even a new worksheet in the workbook) would be ideal, so that the end user can extract the long trial list once a month at the push of a button.

Hopefully someone can shed some light for me.

Thanks in advance
Steve

Hello - hope someone can help me.

I am using data validation to allow the user to select from a drop-down list in a worksheet. I want them to only be able to select from the list and not to input any other values, and I have checked the box in Error message which says 'Show error alert after invalid data is entered' but I am still able to type in an invalid value without the error message appearing.

The named range which informs the list is large and includes lots of blank cells to enable me to add extra values to the list without having to rename the range, could this be the problem?

Does anyone know if it's possible to add multiple values that are contained in a single cell with single values contained in other cells? I'm looking for a formula (not VBA) that i can enter directly in a cell. Unfortunately, that's how the bosses want the information entered. For example:

Cell A1
1, 2

Cell B1
3

Cell C1

1, 3

Cell D1 containing the totals

=10

Thanks in advance for any assistance anyone can provide.

Hi, I created a list with names and after each name a difficulty.
Easy, Normal, Hard and Brutal.
It looks something like this

Name 1 HARD
Name 2 EASY
Name 3 EASY
Name 4 BRUTAL
Name 5 HARD
.. etc


I want to be able to sort the second colum (difficulty), not alphabetically, but in order of their value.
So: Easy, Normal, Hard, Brutal
Or: Brutal, Hard, Normal, Easy

I can only arrange them alphabetically, can anyone help me?

Thanks,
cxn