Solved

Excel data verify

Posted on 2011-02-18
18
420 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 41

Expert Comment

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

Dave
Data-Validation-r1.xls
0
 
LVL 41

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:teylyn
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
 

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 41

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:teylyn
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 41

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 41

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:teylyn
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

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:teylyn
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 41

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:teylyn
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 41

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:
teylyn 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 41

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 41

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Office 2013 constant OST corruption 22 72
Outlook 2010 Archive 3 42
TT Column Arrange 10 28
TT Auto DashBoard 4 33
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now