Over 1 million tech questions and answers.

Excel 2013 - Data Validation HELP

Q: Excel 2013 - Data Validation HELP

Hi,

Please see the attached pic.

In the workbook I am working on in the pic there are 3 pages

page 1. is what looks like the main document that is worked in and it has a drop down menu that I would like to add vendors to a preexisting list (but I can not figure out how)

page 2. is a list of venders mot NOT all of which are in the drop down menu already (if I edit the list the changes do not effect the corresponding vendor that are already listed in the dropdown menu on page1)

page 3. named "sheet 1" seems to be blank and have no function

** in the picture you can see that I highlighted the "VENDOR:" drop-down and right bellow that I am showing the data validation for that drop-down.

I would really like to be able to edit this drop-down list.

Can someone please point me in the right direct?

Let me know if you need any further information!

Thanks a million!

Read other answers
RELEVANCY SCORE 200
Preferred Solution: Excel 2013 - Data Validation HELP

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

RELEVANCY SCORE 74.8

Hi everyone,

I need help in excel, your comments will be highly appreciated.

I have 2 sheets in a workbook, one named: _Inventory_ & the other one _Quotation_

I wrote the inventory in Inventory sheet and then I went to Insert/ Name/ Define and created Inv.Quinc. which refers to =Inventory!$A$1:$A$2000

On sheet Quotation, cell H20, I went to Data/ Validation/ Allow: List/ Source:= Inv.Quinc. This is to create a list that I can choose from in H20
It works, but every time I open the list I have to scroll up, because it always start from the last empty field.

I have attached the file for your reference
Thank you in advance
 

A:Data Validation in Excel

Read other 13 answers
RELEVANCY SCORE 74

Hi everyone. I'm creating a spreadsheet using protection and data validation. I will be locking the sheet and allowing users to enter data in specific cells. The data they enter can be numeric decimals only. Protecting the sheet and ensuring the data is only numeric is not a problem. However I was wondering if there is any way to ensure the data is entered in increments of .25, .50, .75, and 1.00? For instance the user needs to enter the number 2.25 but accidentally tries to enter 2.20. Is there any way to ensure 2.20 cannot be entered?

Thanks for any help.
 

A:Data Validation In Excel 2002

Hmmm, well I guess one way I can do it is by using a named list for each cell. Any other way you guys can think of? Something that doesn't involve a drop down list?
 

Read other 2 answers
RELEVANCY SCORE 74

I have a spreadsheet for work that is basically free form. But in one column I have an ending line that reads "vehicle inspection, unloading" and a few more words. My former spreadsheet allowed me to select whatever line I wanted in that column in which to place the test by selecting it from the drop down. For some reason now it will do that but it will not let me type anything in any line. So, I can select that phrase from the drop down but I can't type any other info into any line with out getting an error. Kind of like it is either that phrase or nothing. How do I keep my drop down info on there and use it in any line I want while still being able to type anything into any line not occupied by the drop down text? Does that make sense? I set this up through data - data validation. I suspect there is just a setting in there I am not checking or unchecking properly.
 

A:Solved: Excel Data Validation

on the validation screen - you should see a "Error Alert" tab
on there will be a tick box
"show error alert after invalid data is entered "

untick that
 

Read other 3 answers
RELEVANCY SCORE 74

I am trying to write a macro to run through and change the data validation. I have a column and the data validation for a given cell is in the same column. Data validation does not carry out in autofill like formulas. I can not get the validation formula to work with a variable. ="=$columnvariable$number1:$columbvariable$number2" where column variable is the column letter and number 1 is a row number and number 2 is the other row number. Is there a way to make this work where i can increment the column letter and the numbers?
 

A:Solved: Excel Data Validation

Read other 16 answers
RELEVANCY SCORE 74

I am using Excel to create UPC bar codes for merchandise in my store. I have used the data validation function on the column with the UPC codes. It has been working great all the way down to row 778. Now no matter what number (whether used or not used) I put into the cell, I get the error that says that number has already been used. BTW I have tried numbers that I KNOW have been used. Does anyone know how to fix this?
 

A:Help in Excel with data validation countif

can we see a copy of the spreadsheet with dummy data in - this is a public forum, so any information posted will be in the public domain

have you tried setting up on a different sheet to see if the problem occurs on a new sheet
whats the version of excel you are using
how are you validating the column
 

Read other 3 answers
RELEVANCY SCORE 74

question,

I have a list of 5000 names below a data validation box. I would like excel to autocomplete the names that users are typing. unfortunately it will only autocomplete the first 1000. Is there a limit on the length of the list it will autocomplete or is this something i can change,

thanks for your time,

Dave
 

Read other answers
RELEVANCY SCORE 73.2

I have data validation drop down lists and conditional drop down lists in some of my fields on my Excel 2010 worksheet. When I switch to Data Form for easier data entry, it still restricts the cells correctly, but it does not show the drop down lists.

Is there a way to show the drop down list in the data form?
Can comments to help the data entry person be added to the form?

Thanks

A:Excel 2010 Validation in Data Forms

One thread in Office support should cover this> Excel 2010 Validation in Data Forms

Read other 1 answers
RELEVANCY SCORE 73.2

Currently I have a data validation drop down list in Excel where you can only select one list item. I want users to be able to select multiple items from this list, and have it show in the single cell separated by comma. I've read you can do this with VBA but I'm not familiar with VBA in Excel. If anyone knows another way to do this, or some step by step instructions you could link to about how to do it with VBA, I'm pretty technical so with a little guidance should be able to figure it out.
 

Read other answers
RELEVANCY SCORE 73.2

Hi

I am trying to write a formula in Excel data validation that meets one or the other condition.

I tried the following, but it does not trigger the validation pop-up box.

The formula is as follows:
=OR(SUM($E$11)<=8,SUM($E$11)<4)
i HAVE UNCHECKED "IGNORE BLANKS"

Hope I have been clear.

Jean

Read other answers
RELEVANCY SCORE 72.4

hello everybody. its been a while since i have been on this site..anyway this is the situation:

i would like the number in cell A1 to increase by 1 everytime there is data entered in cell A2 and A3( data must be entered in both, reject and do not increment if data only entered in A2 or A3). any way to do this in excel?

thanks all!
 

A:Solved: Excel 2003 - how to perform some data validation..

Read other 16 answers
RELEVANCY SCORE 72.4

Any suggestions for removing blanks from an Excel 2000 drop-down validation list?
Data validation procedures I found at the MVP site work fine in a new (empty) spreadsheet but cannot get it to work in a spread sheet that already contains lists. Lists are named ranges in several columns but on the same worksheet. The data validation area is on a separate worksheet.
In one column, the drop down works fine but in another column the dropdown starts at the bottom and I must scroll up past blank lines to see the list.

Hope this helps:

Sheet 1 = columns that will contain validation cells
Sheet 2 = Named Range 1 in col. 1, Named Range 2 in col. 2 . . .

On Sheet 1:
col 1 dropdown list =OK
col 2 dropdown list = blanks and must scroll up to see beginning of list (only 3 items in this list)

Thank for any help you can provide.
 

A:Excel Data Validation Blank in Drop Down List

Your named ranges contain blank cells. Anyway you can change the range NOT to include them?
 

Read other 1 answers
RELEVANCY SCORE 72.4

Hi There

I'm not sure if this is possible or not. I checked the internet and coud not find a solution. I have created a very simple dropdown list with only 2 entries. I typed in the entries in the list source box. Is there a way that the user can also type in any other entry of his/her choosing to the cell without the message appearing saying that the entry is not valid?? If it is not possible please let me know.

Thanks
 

A:Solved: Data Validation list (Excel 2000)

Just turn off error alert in Data Validation Option
 

Read other 2 answers
RELEVANCY SCORE 71.6

Hi,
I'm working on an Excel project of mine and wanted to use VBA to create a Data Validation List in a merged cell.
The strange thing is that using VBA for some reason the list does not show as a drop down list but as a long string
The string is the 26 letters of the alphabet
A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z
Click to expand...

Since I was doubting my own VBA code I decided to record a macro and added all the 26 letters of the alphabet
The resulting code:

Code:

Sub DVApply()
' DVApply Macro
Sheets("DataForm").Select
ActiveSheet.Unprotect
Range("B2:C2").Select
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("D5").Select
End Sub

Now here is the thing. The moment I record the macro the list shows up nicely as a dropdown list, but if I run the macro the new list will show up again a a single string and not in a list unless I open the Data Validation Dialog and click Apply and close it.
I&#... Read more

A:Solved: Excel (2010) Data Validation using VBA not working as expected

Whatever the problem is I wrote another routine and it works now.
I've thrown every range at it and it (still) works.
I'ts still strange why even the recorded macro does not work when you run it again afterwards.
Let it be, one more of Microsoft's deep secrets
At least this post had 15 views, but I'm marking it solved.
 

Read other 1 answers
RELEVANCY SCORE 71.6

Hi there
sincerely hoping & wondering whether someone out here could help me by offering me your solution or suggestion ?

My situation is as follows:
In my worksheet there is about 600 cells scattered all over the sheet that are to represent different monetary value figures, that I have used NAME MANAGER to define as MONEDATA1, MONEDATA2, and MONEDATA3 (each holding around 200 cells, which is the approx.limit allowed by NAME MANAGER for Excel 2010). Thing is, my work revolves around dealing with 12 different countries of currencies, and so all these monetary cells, in CURRENCY format, thus need to be 1 out of 12 different currencies with respective currency signs, at any one time I do some calculation. So, they must convert into 1 of those 12 currencies, each time determined by a selection from a single dropdown list of 12 currencies located at the top of the worksheet in one particular cell I data validated, which is $C$6.

Now, I just need the worksheet to convert all the 3 groups MONEDATA1, MONEDATA2 and 3 simultaneously, instantly when 1 of the 12 currencies is chosen in $C$6, by using a VBA code. Is there a best way to resolve this need ?

Could you help me with the above problem ? Thanks in anticipation and advance ! Greatly appreciated any help.
cheers & have a nice day there,
Jason.
 

A:Excel data validation and executing a code based on selected value

 

Read other 1 answers
RELEVANCY SCORE 70

Hi

I use MS 2010, and in my Excel work book I have a list which works fine for me. But when I send it to others, the cell with the list in does work. - By not working I mean that there is no "arrow" to access the different variables that the list should show.

I assume this is a compatibility issue. Was just wondering if there were any known solution.

Thanks
 

A:Solved: List function in data validation not compatible with earlier version of Excel

Read other 14 answers
RELEVANCY SCORE 70

Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?

Thanks,

Mark.
 

A:Solved: Excel 2007 - Dynamic formula based on Data Validation List

Read other 11 answers
RELEVANCY SCORE 69.2

Does anyone know how to do this?
 

A:How to Normalize data in Excel 2013

Define "normalize".
even better. Take a screen shot of what you see and explain what it is you want.
 

Read other 1 answers
RELEVANCY SCORE 68.4

I had deleted one of my excel files accidentally. I recovered it using Tuneup utilitites 15. But it showing a error popup. am using win 8.1 and office 2013. I've tried recovery tools but it is not useful and also tried office - open and repair method.. pls any one help me to recover my corrupt excel file or atleast i need all the datas in it.
 
Whenever I try to open the recovered excel file, it shows popup as in the attachments with this post
 
 

A:how to recover data from the excel 2013 file?

Hello there,Can you try another data recovery tool, i.e. Recuva from Piriform?Alex

Read other 4 answers
RELEVANCY SCORE 67.6

Hi All, In Excel I have a worksheet (W1) that has a table (call it Table A) from Access in it.
In Excel, another worksheet (W2) has cells which should show the values in the cells in Table A
That works fine also until I use the Excel refresh data option for Table A.
The Excel worksheet W1 has !REF errors, incorrect row heights and does not show the updated Table A .
Does anyone else have this problem or know of a solution ?
(It's the same problem in any other Office version I have tried)
Thanks for your replies
 

A:M$ Office 2013 data refresh chaos in Excel

Well, I solved this after an exhaustive day working with Office Access and Excel.
There are about 7 complicated changes you must make to get the Data links to work.
One big mistake I made was not saving a table after I changed it.
If you don't do that, the Access->Excel exports just return the old data.
 

Read other 1 answers
RELEVANCY SCORE 66.8

This might be some kind of a simple error but I have no clue what to do about it. I'm sorry that the program language is in Finnish but I think you can get the point. I've tried googling several times now and no one hasn't seem to have this problem (or everyone else knows how to fix it..)

Read other answers
RELEVANCY SCORE 66

Hello, I'm trying to import data from an online website that constantly updates its database. However, I can't do it with the Web Query from Excel 2013. After showing the Script Error display, I clicked continue instead of stopping it just to find out that I couldn't scroll down the Web Query page. Furthermore, I couldn't cancel so I had to use Task Manager to end the task of Excel 2013. I'm aware that it is using IE as a browser for the Web Query, maybe changing the browser would help? If it's not possible to do so, how can I fix this script error problem?

--------------------------------------------------------------------------------------------------------------------------
Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 8.1 Single Language, 64 bit
Processor: Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz, Intel64 Family 6 Model 60 Stepping 3
Processor Count: 8
RAM: 8130 Mb
Graphics Card: NVIDIA GeForce GTX 745, -1 Mb
Hard Drives: C: 304 GB (210 GB Free); D: 18 GB (2 GB Free); E: 606 GB (522 GB Free);
Motherboard: Hewlett-Packard, 2B2C
Antivirus: Microsoft Security Essentials, Enabled and Updated
 

Read other answers
RELEVANCY SCORE 66

Running into issue with T470. I have an excel file which when i scroll left or right with touchpad cells with formulas and data disappear. However when i use touchpoint no issue. if i zoom in and out cells show data. Updated all drivers for mouse 19.3.4.99, video 22.20.16.4744, Realtek HD audio, and bios 1.39. Still not working with touchpad. 

A:T470 Horizontal scroll Excel 2013 cells missing data

Have you tried resetting the touchpad settings to its defaults?

Read other 1 answers
RELEVANCY SCORE 60.4

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 60.4

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 55.2

Hi,

I want to know how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.

So that the excel size won't be that big and also it saves processing time.
 

A:Solved: Access data export into Excel as the data linked to excel.

Read other 16 answers
RELEVANCY SCORE 55.2

Hi there!
On sheet B, cell C7, I have a dropdown list (using data validation) of names. The things I want to do with vb codes are as under:-
Let suppose, I select the first name from drop and took the print of page1. Now I want to automatically
Select the next name (i.e Mike) from dropdown list and take the print of page1. Then
Select the next name (i.e Steve) from dropdown list and take the print of page1. Then
Select the next name (i.e Jhon) from dropdown list and take the print of page1.
And so on till last name (i.e Blair).

Please note that I have some blank fields in my dropdown list. I just want to ignore them, mean I did not want to take the print for those blank fields. (sample file is attached)
Any help would be highly appreciated.
 

A:Data Validation with VBA

Read other 7 answers
RELEVANCY SCORE 54.4

I'm setting up a sheet to control the entering of address information. I have a drop-down list set up for the city choice. Then, the Zip Code field looks up the correct Zip Code for the chosen city. Since some cities have more than one Zip Code, I use Data Validation to let the user choose from a list, and this overrides the vlookup default choice, which is good. But it makes it easy to choose the wrong Zip Code, and I wish Data Validation would give a warning when this happens, but it's already busy. Is there any way to offer a drop-down list and check for a city match at the same time? There are even townships that share a Zip Code, though I don't expect that will be a problem.
 

A:Overworked data validation

Try this download:

http://www.ozgrid.com/FreeDownloads/MatchingLists.zip
from www.ozgrid.com
 

Read other 2 answers
RELEVANCY SCORE 54.4

Hi,

I have a couple of questions if you could possibly help me.

I have a cell where I want an employee to input the approximate (or actual) age of a person. To make this easier, I have created the following list - baby, child, 16-20, 20's 30's, 40+, Unknown. The problem is that sometimes the employee will know the exact age of the person - and I could do with capturing this information. Is there any way, other than elongating the list (adding on 0,1,2,3,4 etc), that the cell can be validated in such a way that you either input an actual age (say 00-100) or choose from the drop down list above?

The second question is -

I have 2 questions on an excel spreadsheet: -
1 - What is the number of clowns in the circus? Answer to be put in cell B3 (minimum 0, maximum 9)
2 - How many clowns had red noses? Answer to be placed in cell B4 (minimum 0, maximum 9).

Currently, both cells are validated to produce a drop down menu of 10 options - 0-9. What I want is to ensure the inability to place a higher number in cell B4 than B3. But I want to keep the drop down menu's (using list on validation). Can this be done?

Any help would be greatly appreciated.

Mark
 

A:Cell - Data Validation

It might help us to help you if you told us the exact program you are using.
 

Read other 3 answers
RELEVANCY SCORE 54.4

Hi,

I am facing problems in validation, i have given a validation for a cell as a whole number greater than zero, after that if i give any values(numbers) to that cell, it shows error.

If any one have idea, please let me know. Thanks in advance..
Nagarajan.S
 

A:Validation in Excel

Check that the format of the cell itself isn't "Text" (CTRL+1, "Number" tab).

If that's not the answer, upload your workbook if possible.
 

Read other 2 answers
RELEVANCY SCORE 54.4

Hey,

I want to put in a validation so that if the person who is inputting the data is below 9 years of age, it won't let them input any data.

The cell is E3.

Thanks.
 

A:Validation - Excel

Read other 16 answers
RELEVANCY SCORE 54

Hi everyone,
I’ve created a list (insert\ name\ define) then data, validation…..
the list contains more that 200 entries. now using it to choose one of the entries can take much longer that typing it.
Is there a way to type only the first 2 or 3 letters to get to my choices faster instead of scrolling the whole list?
Thank you
 

A:Data validation- choosing from list

Read other 6 answers
RELEVANCY SCORE 54

Hi all,

I'm trying to create a form with the use of data validation so that staff do not key in wrong values. The condition is:

1) The year of manufacture for a new insured cannot exceed 8 years
2) If the insured is not a renewal (not new insured) than the year of manufacture cannot exceed 11 years
3) The year of manufacture cannot exceed today's year

Let me try to explain condition 1 and 2 with examples to clarify.

1) The year is 2011. A person wishes to insure his car this year (2011), his vehicle year of manufacture cannot be less than 2004 (2011-8 years). i.e. It can be anywhere in the range of 2004 to 2011, but below 2004 an error will occur prompting the user that we cannot insure this vehicle.
2) The year is 2011. A person has insured his car with us in 2009, his vehicle year of manufacture cannot be less than 2001 (2011-11 years). i.e. It can be anywhere in the range of 2001 to 2011, but below 2001 an error will occur prompting the user that we cannot insure this vehicle.

I've been looking at this for a number of hours and I feel it's only a simple formula to input in the data validation but I just can't figure out.
I've attached Book 1.xlsx for reference
 

A:Solved: Data validation with formula

Read other 7 answers
RELEVANCY SCORE 54

I have been trying to sort this out for days and each time I think I've cracked it I hit on another problem.

I am trying to produce a rota for all the members on our team at work which cuts out the need for annotating printed copies by hand with everything self-generating within the spreadsheet.

So far I have the staff names in a list for the main part of the rota where you select from a dropdown. That works perfectly.

The problem is the other part of the rota. I can select from a dropdown to indicate when a staff member has annual leave or a training day but it is the days off in lieu of weekends that are causing me all the grief.

I am trying to reach a point where when you select a staff member with the day off option (e.g. Joe Bloggs DO), the dependent dropdown after it has the correct weekend days combined with the staff member's name (the weekend preceding the Monday of that week's rota and the weekend following the Friday of that week's rota) i.e. for week beginning Monday 6th December the dropdown would say "Joe Bloggs DO four times respectively followed by the dates 4/12, 5/12, 11/12 and 12/12.

Each week of the rota has the Monday date in the upper left cell with a relative row reference which adds seven each time you copy and paste in the same relative position. The dates for that week simply add 1 to the cell to their left so with each copy of the rota the dates effectively prefill automatically. That works fine.

So far I have got... Read more

A:trouble with data validation formulas

I'm getting closer to a solution - to make it easier, I have named the header row with the staff member's names, and named the entire area where the concatenations of staff name/dates are stored. This will eventually be dynamic.

So that each of the four weekend dates for each Monday are unique, I have added a column to the left which contains the column numbers referencing the table on the other sheet with the weekend dates in it. This makes it much easier both for copying and enables me to concatenate the column number with the Monday date so that each row has a unique reference.

The problem I am left with is how to get the INDEX/MATCH to work to pick out the relevant dates for each week of the rota. I can get it to pick out one but I have four rows with dates in and only one with the staff member's name so it would throw out the relative referencing (I think)
 

Read other 1 answers
RELEVANCY SCORE 54

Is it possible to have a user choose a value from a dropdown box outside a pivot table (which matches an existing pivot table field) which places that column into the pivot table rows area?

I have a matrix table with 4 main column headings (ABCD) that i can place into my pivot table rows area - that works well.

However, I have 40 other headings across the top of my data table (EFGHIJK.....) which the Pivot Table shows each as its own field in the field list box - they are all the same type of heading (role positions for example, manager, director, admin etc) underneath each heading in the matrix it displays a Yes or No value in rows that relate to that row topic/name.

I need to return all rows where under a given selected column (position) there is a Yes present in the cell.

My table that I am running a pivot table from basically has both row headings and column headings and I cant figure out how to generate a simple dropdown box outside the pivot table (at the top for example) that would put that field into the pivot table.

Note: a user could just scroll down the long field list window and tick their position name and then filter for Yes - but I'm trying to make it simpler.

Any advice is appreciated, can what I'm asking actually be done?
 

Read other answers
RELEVANCY SCORE 54

I am working with Office 2007
From the oracle table I wrote a query to get the Region and also the corresponding areas.
This is loaded as a master sheet in excel sheet 1
Then with the other query I got all the values that are to be displayed ( This is Child sheet)
Now in the child work sheet the user is allowed to change the Region. for this I used the data validation list to display the values as a list.
=Sheet2!A:A (Assuming sheet2 as the master work sheet and column a has the values of all the areas)
but Now I heed to filter the list in such a way that only the values corresponding to Region need to be displayed as areas.

Master Table

Region Area
--------------
SIN area1
SIN area2
SIN area3
NYC area4
NYC area5
IND area6
IND area7

Child Table

Position Region Area

NE SIN area1
NW NYC area4

As shown above the area needs to be based on the region in child table, in the same wise if the region is changed the list items need to also be listed in accordance with it.
Please Note :Macros are not allowed in the excel

Regards

Raghul
 

A:Data Validation List with Filter

why not attach the excel workbook with the 2 sheets, it's easier to visualize
 

Read other 1 answers
RELEVANCY SCORE 54

Please disregard... I figured out that I was just being a bone head...
 

Read other answers
RELEVANCY SCORE 54

Hi,

I am trying to pull a dashboard view from a "Tracker". Given below is the formula that I used:

=COUNTIFS('KT Tracker'!$T$2:$T$138,$C$2,'Tracker'!$U$2:$U$138,"<="&TODAY()+7,'Tracker'!$L$2:$L$138,'Monthly Dashboard'!$B17,'Tracker'!$AF$2:$AF$138,'Monthly Dashboard'!$A$13,'Tracker'!$AD$2:$AD$138,"<>Cancelled")

This perfectly works fine in my laptop that has excel 2013 but when I email this to my team who have Excel 2010, they only see zeros. We have checked all options like enabling Automatic Calulcation of Formula, removing all named ranges in the sheet etc., but it is just not working.

Can you please help ? This is a bit urgent and I need to present this for a meeting tomorrow.
Appreciate your help and thanks in advance !!!
 

A:COUNTIFS working in Excel 2013 but not in Excel 2010

It might be something to do with "<>cancelled"
 

Read other 1 answers
RELEVANCY SCORE 54

I cannot search for data in excel 2013. This is new issue. I do notice that in the find window there is an entry for Preview* in the box next to format in the options window that I have never seen before. I get the message cannot find what you are looking for but I know it is there because I am looking at it.

This is new issue. Worked until last night when I was doing a sort and had to make all merged cells uniform in size. Now I can sort but cannot find.

Have closed and reopened excel, but it did not correct problem.
 

A:Solved: Excel search does not work Excel 2013

Read other 7 answers
RELEVANCY SCORE 53.6

i currently have a spreadsheet where a user enters data from a list into cell column A, and data from a list into cell column D. the list for column A allows the user to select from "zone A", "zone B", "zone C", and "zone D". the list for column D allows the user to select from "Yes" or "No".

How can i make it so that an alert message appears when the user selects both Zone D and No in the same row? i want this to be allowed but it should alert the user that it is not reccommended. is this possible to make a popup when both conditions are true? thanks in advanced.
 

A:excel 2007 validation help.

You can make a pop-up with code. Easier to use a formula in a free column, such as:

=IF(AND(A2="Zone D",D2="No"),"Zone D + 'No' not recommended","")
 

Read other 1 answers
RELEVANCY SCORE 53.6

I am using Excel 2002.
I know this can be done; I am just drawing a blank.
I have a template that is locked down and the users start a new spreadsheet based on the template. The fields start out as blank and they will tab through each open cell and enter information. There are 4 cells that we want to make required that would have to have a number in them and it can be 0 or any other whole number. However, it must have a number in it.
I have been working with the data validation and cannot seem to get it to work.
Here are some of the things that I have tried:
Data validation for whole number with the data greater than or equal to 0 and unchecking the Ignore blank.

Custom with the formula =ISTEXT(J22) J22 being the cell I am in. Unchecking the Ignore blank.

Custom with the formula =ISNUMBER(J22). Unchecking the Ignore blank.

After I make a setting change to try something different, I lock down the template. Save it and then start a new spreadsheet based on the template.

With all of the things I have tried I can always tab past the boxes with the validation on them.

I have set the Input Message and Error Alert message and when I am in the cell the Input message works just fine, but then I can tab right past the cell and never get the error message.

I have searched the 3 Excel books I have and tried all their solutions and even searched the MS Excel Help online and off.

HELP!! I am just drawing a blank. I know this can be done and is easy.
I have to be doing just... Read more

A:Solved: Excel Validation

Read other 10 answers
RELEVANCY SCORE 53.6

Trying to validate a cell as Decimal between 0 and 99999999999999.99. What I get is a rounded value like 12,345,678,901,234.1 instead of 12,345,678,901,234.12. Changing the edit to (13, 2) saves the decimal place but I loose the 10 trillion. Is this an Excel limitation or is there something else I can try. Using built in cell validation (Data --> Validation). What is the largest number Excel can handle? Thanks........
 

A:Excel Cell Validation

Excel's numbers are precise up to 15 digits, any other digits become 0's. This includes the decimal places.
 

Read other 1 answers
RELEVANCY SCORE 53.6

Hi, I have a fixed list of 10 fields in a worksheet and users enter the inputs in these 10 fields, however the sum total of all the 10 fields should not exceed 100. I am unable to figure this out, may someone kindly help me on this. Thanking you in advance
 

A:Excel Input Validation

Read other 7 answers
RELEVANCY SCORE 53.2

So far i have read you can't do this. So i'm wondering how you achieve this the correct way. I work for a gas field company and the database i am redesigning has a lot of fields that need validation, like well site names, company names etc. The person who built the original database had no validation at all, and you could find the word Production spelled 12 different way sometimes. Made it very hard to keep data readable.

So the part im working on now is the well sites list. I have a table called Well Sites. I have a main work orders table where they must choose a well site which is a lookup of the well sites table. This is fine and dandy, however. If they choose not to use a name in the drop down list, they can simply type anything they want, which is what i want to avoid. I want them to ONLY be able to type names that exist in the well sites table.

I was hoping it would be as easy as setting up a validation rule but from what google is telling me that is not even possible. So how do i control what user's enter. I do NOT want to type in a list of 600 well site names using OR for a validation rule. That is unmanagable, but i need to restrict what they can type so we don't get mis-spellings and mis-labeled wells.

Now i understand some things but some things i've just never done before prior to now. If i setup a one to many relationship between two tables then it seems to work like i want. Only values that exist in the other table can be entered into... Read more

A:Field Validation based on another tables data

Read other 6 answers
RELEVANCY SCORE 53.2

HI,

I required vba codings for pivot table but the value should be choose from the data validation i have created.

For example : I have two pivot tables in sheet1 and these two pivot table have a common column filed available so i have created the data validation in cell D6. Now i need to give reference to the cell D6 for both the pivot table on the column labels.

Based on the value selected on the drop down the pivot table need to be changed on the both pivot tables.

Regards,

VInwin
 

A:Solved: Pivot table with data validation

Read other 16 answers
RELEVANCY SCORE 53.2

Hello,

Is it possible to run a simple macro from a user changing the data validated drop down selection?

LDP Analyst
 

A:Solved: Macro from data validation change

Read other 6 answers