Question about Microsoft Office Professional 2007 Full Version for PC

1 Answer

Validation using Excel VBA code

Hi Expert.. I have a sheet, where user have to fill data. There is a column Department.. user should not enter or paste a value other than defined departments. i have applied validation using data>>validation. but it works only if the user selects department from the validation list. but if he copy data from his source and paste it in my Sheet, then the validation doent work. i have created a button (Validation) on the top of the sheet, to validate the data. it validate for email id, contact numbers, name, etc... when the user clicks on this button it throws errors.. the same way it shold throw the error if the department is not in the validation list... please help me out... its urgent

Thanks in advance
Regards
Seema

Posted by on

1 Answer

  • Level 3:

    An expert who has achieved level 3 by getting 1000 points

    Superstar:

    An expert that got 20 achievements.

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

  • Microsoft Master
  • 2,794 Answers

Thats a lot of form work to be expecting out of Excel. You might be better off using Access, creating a table. If you need to export it back to a spreadsheet you can. The validation controls are better in Access and don't have a lot of limitations to them.

Good luck on that

Posted on Jul 23, 2008

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Excel cell replacement


To have the contents of the cell change from something you input into something else, you would need to put in a macro using VBA. You'll need someone who knows VBA to help with that.

OR... It is a lot easier to get similar results, if you are ok with using a few more cells to do it. The item# will remain where it was entered, and the product name will have to go in a different cell. In that case, you can use a lookup formula.

To do this, lets say your item# is entered in cell 'Sheet1'!C1, and you want the product_name to display in cell 'Sheet1'!D1. On another sheet (lets say sheet2) In cells 'Sheet2'!A1:B5 input the item#'s in the first column (column A1:A5) and input the matching product-names in column B1:B5. This is your lookup data. This sheet can be hidden if you want In cell 'Sheet1'!D1, use a vlookup formula that will look at the item# and find a match in the list, and display the product name for you. =vlookup('Sheet1'!C1,'Sheet2'!$A$1:$B$5,2,false)
This will display N/A# if the number can't be found.

Feb 15, 2013 | Microsoft Excel for PC

1 Answer

Excel vba


For complete guide please refer to this link
http://www.fontstuff.com/VBA/vbatut03.htm

Thanks and regards

Fixya is educating to educate

May 12, 2009 | Microsoft Office Professional Edition 2003...

1 Answer

In Excel trying to compare two ranges


I imagine that the only way is with VBA coding. This is possible if you know programming in Excel (VBA - Visual Basic for Applications).

Apr 17, 2009 | Oracle Database Enterprise Named User Plus

1 Answer

CODES for entering an I.D number using vba


Try the help built into Excel and give yourself time to absorb it; Excel has some of the best help in the business. VBA may be more complex than you need to solve a data entry problem. If you're trying to add data input masks in a database, why don't you try Access (the help for which is admittedly weaker because database programming is among the most complex of all productivity apps)? I learned VBA from 3 books I rented from a good public library over the course of a project that was about a month long, (because) at some point Microsoft abandoned the idea of teaching it via help screens. Still, you might join a MS developer site and those are loaded with detailed explanations and many other experts. I hope this helps... I feel your pain.

Mar 22, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

Copying data from one sheet to another if two fileds match


Nope, sorry, although I am truly an expert at Excel formulas, I do not understand what you are trying to end up with in the final cell. We can compare a specified field with two spreadsheets - use named ranges and index/match lookup formulas. But then where you really lose me is in reading "a generic field" to find a match, and then placing what "data from another field" into what "other sheet" - ? See the confusion?

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):
A1: Part B1: Code C1: Price D1: Find Part E1: Find Code
A2: x B2: 11 C2: 5.00 D2: y E2: 12
A3: x B3: 12 C3: 6.00 D3: y E3: 11
A4: y B4: 11 C4: 7.00 D4: x E4: 12
A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:
=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:
=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Jul 08, 2008 | Microsoft Business & Productivity Software

1 Answer

Sumproduct or conditional sumif with multiple criteria in vba code...


i'll just reply to your conditional sumif query.

yes, you can do multiple criteria query but with a matrix formula. you may look it up. nonetheless, here is how one would look like:

{=sum(if(range1=A x range2=B x range3=C, sum range))} note that x (multiplication) is used to denote intersection of the criteria

initially, you'll enter it as:
=sum(if(range1=A x range2=B x range3=C, sum range))

then, edit the cell (F2) and press ctrl+shift+enter for the brackets { } to appear.

Jun 27, 2008 | Microsoft Excel for PC

1 Answer

Excel Formulas


Hi,
Try using a software called MacroRunner for Excel v1.0.. you can download this from the following link
http://www.filebuzz.com/fileinfo/21205/MacroRunner_for_Excel.html

Good luck.

Feb 05, 2008 | Business & Productivity Software

3 Answers

Excel vba password recovery


You can but you will need software. Search for google for VBA password recovery tools. Search for the appropriate crack you have it all . .

Dec 30, 2007 | Microsoft Excel for PC

2 Answers

Duplicacy in excel sheet


Since you are searching the data by the phone number , first select all the data in the spreadsheet and sort it in ascending order by the phone number.
Then, assuming you have 5 columns of data A through E, and the phone numbers are in column E, with row 1 occupied by column headings, use the following formula in cell F2=IF(E2=E1,"Duplicate",1)

Drag this formula down column F till the end of your data
Select the entire data and do an auto filter
In column F filter the data by Duplicate and delete all these rows
What remains should be unique data

Dec 19, 2007 | Business & Productivity Software

Not finding what you are looking for?
Microsoft Office Professional 2007 Full Version for PC Logo

1,886 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18304 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...