Solved

Excel data verify

Posted on 2011-02-18
18
423 Views
Last Modified: 2012-05-11
Hi Expert,

Attached  excel file, i will input datas into  Sheet " DEDUCT"column A start from cell A6 .

Prevent  typing mistake.
So  i  want  excel  detect  data  at  Sheet1 Column A exist or not, if not  exist then  not allow to input .


Regards
Stanley 1.xls
0
Comment
Question by:candychan611
  • 10
  • 6
  • 2
18 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 34931316
Try this.  explanation forthcoming...

Dave
Data-Validation-r1.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34931331
See picture.  I defined the name range in column A of Sheet 1 to "Item_List".

In the DEDUCT sheet, I selected the range for data input (colored yellow) and went to the Data, Data Validation (see Ribbon) and selected "List" for the Allow prompt, and "=Item_List" under the source prompt.  There are other prompts under Data Validation for you to handle based on user input, if you desire.

Enjoy!

Dave
DataValidation.png
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34931334
dlmille was faster. I was just about to post the same solution.

Tip: If you list is very long and you don't want to use a drop-down, then you can untick the "In-cell dropdown" in the screenshot above. Users can simply type the value into the cell and won't see the drop-down list.

Also, be aware that if the source for Item_List contains blanks, then ANY value can be entered.

cheers, teylyn
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:candychan611
ID: 34931550
dlmille, thx it works.

Just 1 question : how  to  extend  the range    "ITEM_LIST"are  from A6  to  A65536  

0
 
LVL 42

Expert Comment

by:dlmille
ID: 34931623
Sure.  Go to the Ribbon under the Tab called Formulas at the top.  Look toward the middle of the screen at the top, and find NAME manager.

See the Item_List name?  Click on that.  at the bottom, you'll see SOURCE, just change that.  then hit the check or OK and you're done!

Dave
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34931695
Candy,

Just a word of warning.

I assume that you do NOT have 65536 items in your list. If you extend the range name to include all rows in that column, the data validation list will contain blank cells.

If a data validation list contains blank cells, ANY value can be typed into the cell and will be accepted. The data validation has become useless.

You probably want the Item_list to use all cells with values, and you do not want to change the list definition whenever you add new items to the list. For this, you can set up a dynamic range name. In the Name Manager, select the Item_list name and use this formula to define it:

=Sheet1!$A$3:INDEX(Sheet1!$A$3:$A$65536,COUNTA(Sheet1!$A$3:$A$65536))

Now, if you add items to the list, the range name will automatically include them in the data validation. Just make sure that you don't have any blank cells in your item list.

See attached file. Add a few items in Sheet1 at the bottom of the list in Column A and see that they will be valid entries.

Also, try to use the whole column A as the data validation list and see that you can enter any value. That's probably not what you want.

cheers, teylyn

Copy-of-Data-Validation-r1-1.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34931733
Actually, if you uncheck the "Ignore Blank" item, you can define the entire range, rather than having a volatile name definition as teylyn suggests.

I like her tip :) , but wanted to ensure correct response.

You can define like this and still maintain the entire column as the data validation range.  See attached - it will only accept valid items in the Item_List and will not accept ANY value or BLANKS as input...
Cheers,

Dave data validationData-Validation-r2.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34931734
Kudu's teylyn - either the volatile formula or unchecking the ignore blank is necessary to avoid inadvertent entries.

Dave
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34931772
@Dave,

I am a strong believer in lean worksheets. I'd rather define a range to include just the populated cells than define the range to include the whole column.

I guess it comes down to style, and a preference for fast calculating workbooks.

I avoid the use of whole column or row ranges wherever I can, because inadvertently, it will slow the calculation down.

This may not apply to a data validation range, but if you're in the habit of using whole columns, you will soon be ending up doing Sumproducts and Array formulas with whole columns, too, and find yourself staring at a "Calculating -- 2%" message for minutes.

Be wary of using whole columns or rows. More often than not, there's a better option.

And the "volatility" in that range name can safely be ignored. It's nothing compared to using volatile formulas in a worksheet cell.

Especially when you move on to Excel 2007 and later, with more than a million rows, you will find that your formulas using whole columns slow everything down considerably.

Better nip a bad habit in the bud.

cheers, teylyn
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34931846
Point taken.  I had been warned about volatile formulas in named ranges but seems like many do it.  I don't knock the tip, but wanted to be clear that is you uncheck the ignore blank checkbox, (even with the volatile name) then one needn't worry about blanks in the item list.  Better to be clean and build good habits - agreed!

Dave
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34931874
Dave,

You are absolutely correct with the "ignore blank" check box.

The use of volatile formulas in named ranges is not really worth mentioning.

If you have volatile formulas like Offset() or an Indirect() in worksheet cells, and you only have a couple of dozens of these, it's still OK.

But if the volatile functions are copied down thousands of rows, you will notice that your worksheet calculation slows down and that after hitting Enter on any cell, Excel takes a while to "wake up" and accept your next move.

Volatile or semi-volatile functions (like Index()) in named ranges do not result in any noticeable change in calculation speed. So far, the "worst" effect I have ever seen with volatile functions in range name definitions is that when you close a workbook that uses them, Excel asks if you want to save the workbook, even if you have not made any changes. Volatile functions in named ranges get recalculated when the workbook is opened, and the workbook is then flagged as "dirty" (not my term = with unsaved changes) and it will be treated as a changed workbook.

Nothing to write home about.

Candy, I hope you don't mind the spin-off discussion, and I hope that you have find a solution for your problem that works for you.

Mind you, both Dlmille's and my suggestions work fine. You can decide which one works best for you and run with that. The most important thing here is that you find a solution that you feel comfortable with and will be able to maintain and adjust to your needs, now and in the future, even if you have not worked with the Excel file for a few months.

cheers, teylyn



0
 
LVL 42

Expert Comment

by:dlmille
ID: 34931878
There is a problem, however, if the data entered in the item_list is numeric.

=Sheet1!$A$3:INDEX(Sheet1!$A$3:$A$65536,COUNTA(Sheet1!$A$3:$A$65536)) will not work correctly.

Better to be safe to set the volatile range to include both numeric and non-numeric.  Agreed?

Candy, please use the volatile name range approach as I think it suits.

Teylyn - can't seem to function - how can we convert the above to check for the last cell either numeric or text?

Dave
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34931913
Dave,

I don't see a problem.

The attached file has a dynamic range called MyRange  with the formula

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,COUNTA(Sheet1!$A$2:$A$65536))

The data in column A below row 1 is numeric. The range adjusts just fine if data is added or deleted.

The only problem occurs if the data is not contiguous, i.e. contains blank cells.

Personally, I prefer the Match() over the Counta() in the index formula, but there are two versions, depending on whether the data is numeric or text.

For numeric data

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(99^99,Sheet1!$A:$A,1))

For text data (applied to column B)

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH("zzzz",Sheet1!$B:$B,1))

With the Match it does not matter if there are blank cells in the range, since Match will find the last cell in the range, as opposed to counting the number of populated cells (which will fail with blank cells in the range)

Since, typically, a column in a data set contains either numbers or text, the Match() variant will work better for dynamic ranges.

cheers, teylyn
Book1.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34931923
Candy,

Here's the formula to use - whether you have numeric or text values in your validation list, this should work properly:

=Sheet1!$A$3:INDEX(Sheet1!$A$3:$A$65536,MAX(MATCH(9.99999999999999E+307,Sheet1!$A$3:$A$65536),MATCH(REPT("z",60),Sheet1!$A$3:$A$65536)))
See attached:

PS - I converted all text that looked like numeric to numeric to test.  and you had a mix as a result.  You may have wanted that to be text, so be advised.  All you need is this revised formula in your name definition.  Also uncheck the ignore blank checkbox on name creation and you're set!

Cheers!

Dave
Data-Validation-r3.xls
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 250 total points
ID: 34931932
Dave,

the 99^99 is probably sufficient and easier to remember than 9.99999999999999E+307
I'd always prefer "zzzz" over REPT("z",60) since it's less to type and equally as unlikely to appear in a list of text values.

I think by now Candy must be thoroughly confused, so I'll bow out of the expert formula show-off.

cheers, teylyn
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 250 total points
ID: 34931936
:)

I think we're good.  Thanks for the input - better spreadsheet will rise from the ashes.

Candy - good luck!  Let me know if you need further assistance.

Dave
0
 

Author Comment

by:candychan611
ID: 34933550
Thx both experts.


I am not experienced excel user , data file  only as much as  millions rows, did't sure which is best , but i will try both of them.

teylyn: Your suggestion so  meaningful :)

Dave:Thanks your detail suggestion , just  I am using Chinese excel 03, so need study the interface to how to redefine the CELL RANGE  as   attached.

Cheers!

1.jpg
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34934533
Here's a macro that will do it for you until you figure out the Chines (though the buttons should be in the same place and the definition would still be the same:

Sub UpdateItemListRange()
    On Error Resume Next
    ActiveWorkbook.Names.Add Name:="Item_List", _
        RefersTo:="=Sheet1!$A$3:INDEX(Sheet1!$A$3:$A$65536,MAX(MATCH(9.99999999999999E+307,Sheet1!$A$3:$A$65536),MATCH(REPT(""z"",60),Sheet1!$A$3:$A$65536)))"
    On Error GoTo 0
    ActiveWorkbook.Names("Item_List").RefersTo = "=Sheet1!$A$3:INDEX(Sheet1!$A$3:$A$65536,MAX(MATCH(9.99999999999999E+307,Sheet1!$A$3:$A$65536),MATCH(REPT(""z"",60),Sheet1!$A$3:$A$65536)))"
End Sub

The first part tries to add the name and if it exists it would get an error, so the second one kicks in after.  Otherwise, the first one creates the name, then falls through the name modify after.


See attached - try to run the macro and see if it works for you!

Dave
Data-Validation-r4.xls
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question