Using A Cell On Another Worksheet As Input For Data Validation

Oct 13, 2008

Situation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).

Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.

In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.

View 4 Replies


ADVERTISEMENT

VBA Code For Data Validation Input Message By INDEX MATCH When Cell Clicked

May 30, 2014

code that will be able to lookup a cell in Column J of Sheet1 by using a lookup value on Sheet2. The lookup value can be any cell in columns B, D, F, H, J, K, or N on Sheet2. The tricky part is, I want the result of this lookup (the result comes from Column H of Sheet1) to be placed as a data validation input message for the cell directly to the right of the lookup value when this cell is clicked.

For Example: Lookup Value is "416991" which lies in cell N8 on Sheet2. Look it up in Column J of Sheet1 and return the appropriate value "X" from Column H of Sheet1. "X" will then appear as an input message when cell O8 of Sheet2 is clicked.

View 1 Replies View Related

Data Validation (restrict A Cell To Only Be Able To Input The Letter "i" Multiple Times)

Nov 5, 2008

I am trying to restrict a cell to only be able to input the letter "i" multiple times, almost like a tally sheet, in other words I want the cell to be restriced to one letter, but allow that letter to be entered multiple times.

View 4 Replies View Related

VBA Code To Copy Data From Text Box And Input Into A Cell In Another Worksheet

Mar 24, 2009

This is probably far easier then I am making it but I need to take the information that is in a textbox created through the control toolbox and copy and paste that information into a cell on different tab in the workbook. I have tried the infamous google and haven't been able to find much. The excel books that I have don't really touch on the control toolbox functions.

View 2 Replies View Related

Return Cell Data From List When Variable Input In Second Worksheet

Nov 25, 2013

I want to return the data from a list in a cell on worksheet1 if I input a variable on worksheet2. I need to avoid VBA and pivot tables.

Example, if I put "Expense" in the input cell on worksheet2, I want to return a list of each row that has "Expense" in it on worksheet1:

Worksheet 1 (ColumnA/ColumnB):

Revenue/45,000
Expense/20,000
Asset/43,000
Liability/21,000
Revenue/6,000
Expense/9,000
Expense/11,000
Liability/13,000

Worksheet 2 required output (no row gaps or spaces):

Input cell "Expense" - in A1

List required (A3:B5):

Expense/20,000
Expense/9,000
Expense/11,000

View 9 Replies View Related

Sum By Data Validation Input Message

Mar 1, 2014

I have , within a workbook multiple worksheets which have multiple cells with a certain DATA VALIDATION MESSAGE. I am trying to write a macro to sum up all those cells which have a common DATA VALIDATION MESSAGE.

View 2 Replies View Related

Input Message On The Data Validation Box

Sep 2, 2008

I currently have 6 cells that Have a Validation Criteria of a "List". I'm using the Input Message on the Data Validation box. I need to have this Input Message Pinned to a specific area on my worksheet. Each time that I exit and reopen the file the Input Message returns to the Area around the dropdown box. The input message is different for each of the 6 cells.

View 9 Replies View Related

How To Add A Data Validation Input Message In VBA

Sep 13, 2009

Cells A1 to A10 contain names of different cars, what i would like to do in VBA is when i select any individual cell to the right of these the name of the car appears as a message (as if i had gone DATA -> VALIDATION -> INPUT MESSAGE -> CARS NAME).

Tried positioning a Userform next to the active cell but when i scroll down the page and select a cell the userform is to far down the screen to be seen.

View 9 Replies View Related

Data Validation Thru Double Input Into Field

Jan 8, 2009

I have to input data into "ANYDATA" field. Since I need this input to be 100% accurate, I would like my operator to input it TWICE. E.g. 1st input - Obama 2nd input- Obama. Only if BOTH the entries match will the data move into ANYDATA field, else the field becomes blank once again, and the operator has to re-key twice again. In example above, if he had input 2nd time as Obame, then he will have to repeat the process. Can anyone pls help me create suitable formula/ field settings...Thx! Preferably, I would NOT like to create two dummy fields "ANYDATA1" and "ANYDATA2" with corresponding entry in "ANYDATA" if both match.

p.s. Need this to be done using MS-Excel only.

View 10 Replies View Related

Data Validation - Input Message From Other Sheet

May 5, 2014

What would be the correct syntax for pulling information to the input message box from another sheet?

Code:
Sub test_data_validation()
'
' test_data_validation Macro
'
Dim nrows As Integer
Dim i As Integer

Sheets("sheet1").Activate

[Code] .........

View 2 Replies View Related

Data Validation Input Message - Always In The Same Area Of The Page

Apr 21, 2009

I have multiple data validations with input messages. For the new user, this is handy, but for the person that does this a lot, they get in the way. When I open the sheet, I can move one message to the side of the page and the rest of them come up at the same spot but when I save, exit, and reopen, the message comes up beside the cell and I would like it to always stay to the side of the page.

View 9 Replies View Related

Data Validation :: Cells Should Contain Specific Word On Input

Jan 13, 2010

I'm trying to make the included spreadsheet as idiot proof as possible as a lot is a stake. I have most functionality working well; however, not I would like to add a little more data validation that does not exist in the normal cell validation rule set.

I need all the input cells to validate that the cell contains the word "BOX" upon entry, otherwise throw a warning. This is because the data entry will be done by barcode and there are a couple barcode labels on each box. The one I want will include the word "BOX" somewhere in the code sequence.

Since a previous function in the Code locks the cell, they wont be able to fix the error. but it will alert them so they can make sure to scan in the proper barcode and not miss the box. Also, once I get something that works in the code, where would I put it? In the same code box as the previous script, underneath it, or somewhere else? So, bottom line, I need a script that check the cell for Text of "Box" otherwise throw a message box.

View 2 Replies View Related

Excel 2007 :: Data Validation Not Stopping Invalid Input?

Dec 1, 2011

I just have a basic data validation list. I used "List" and checked the right boxes and the file has been working before. Now the same file does not stop the user from typing in anything.

I re-did the same data validation in a new file and it works. Was there something in the file that prevents it from working?

I have Excel 2007. I saved in both xlsx and xlsm formats

View 5 Replies View Related

Excel 2007 :: Data Validation Based On User Input?

May 24, 2012

In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.

Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6

View 9 Replies View Related

Delete Column - Macro Using Data Validation User Input Value?

Nov 7, 2012

I would like to delete a column using data validation list. for example :

i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.

And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.

View 1 Replies View Related

Code To Auto Input Worksheet Name From Data Copied From That Worksheet

Feb 23, 2014

I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,

How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".

View 6 Replies View Related

Nested Data Validation: Figure A To Enforce Dual Data Validation On A Single Cell?

Aug 19, 2009

I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.

View 2 Replies View Related

Send Data From Input Box To Another Worksheet

Aug 31, 2008

I have created a form for my work. And I have input boxes for users to add data that I would like to get stored in another worksheet. What is the vba code to send data for an input box to another worksheet at the bottommost empty row; first column.

Private Sub cmdAddData_Click()

' Collect user name, find the space between first and last names, and separate the names.
UserName = InputBox("Enter the Program Name.", "Program Name")
spaceLoc = InStr(1, UserName, " ")
firstName = Left(UserName, spaceLoc - 1)

View 9 Replies View Related

Data Validation From Another Worksheet

Oct 8, 2008

I would like to impose data validation on Sheet1 row A. I have a list of values in Sheet2 row A. Is their an easy way to do this?

View 3 Replies View Related

Data Validation From Another Worksheet

Sep 27, 2012

How I can put a List on a separate workSHEET and refer to this worksheet when inserting a data validation list on my main worksheet?

View 4 Replies View Related

Indirect Validation - Using Data In Second Worksheet

Jan 16, 2014

I have a competition entry form for an association I belong to that I'd like to add two drop down menus to so that entrants can select their club name and associated team names for that club.

In a worksheet behind the form I have a list of clubs in column "B" ("A" has the ID number in it as the table is from Access - don't ask!!) and the names of teams associated with that club going across the sheet beside the club name in individual Name Ranges. (Up to 30 Team names in Dynamic lists

=OFFSET(TeamsTest!$C$2,0,0,COUNTA(TeamsTest!$C$3+TeamsTest!$C$3:$AZ$3),1) )

On the form I have managed to make the first drop down so the club can select its name from a list by using Data Validation and referring to the Range Name (ClubLink - =OFFSET(TeamsTest!$B$2,0,0,COUNTA(TeamsTest!$B$2:$B$200),1)).

I have tried using an INDIRECT command to create a second drop down which will display only the team names associated with the Club selected in the first drop down, but can't get it to do anything - no drop down appears when you click on the drop down arrow at the side of the cell

The data Validation for the Team Name drop down is =INDIRECT(SUBSTITUTE($C$12," ","")) where C12 is the Club Name cell.

Why I can't get the Team Name cell to show the names listed beside the Club selected in C12?

View 14 Replies View Related

Protect Worksheet And Data Validation

Oct 27, 2009

I have my spreadsheet protected and all the neccessary fields locked/unlocked, but one of the columns contains data validation. How can I prevent a user from copying and pasting a value into the data validation cell versus of course the intended purpose of picking from the list?

View 9 Replies View Related

Protect Worksheet And Data Validation

Oct 28, 2009

I have my spreadsheet protected and all the neccessary fields locked/unlocked, but one of the columns contains data validation. How can I prevent a user from copying and pasting a value into the data validation cell versus of course the intended purpose of picking from the list?

View 9 Replies View Related

Data Validation List From Another Worksheet

Jul 25, 2006

I'm using data validation to create a drop down list ina spreadsheet so that users can select from a pre-defined list. I want to be able to maintain this list in a separate worksheet, but when I try to create the list it tells me that "You may not use references to other worksheets or workbooks for Data Validation Criteria". I don't want to maintain the list in the same worksheet as there's a risk of deletion. Is there a way to create a dropdown list in a second worksheet?

View 5 Replies View Related

Convert The Data By Input And Categorize Into Month In Another Worksheet

Nov 16, 2008

am currently a QE using the following data for my report generation:
Date - Week - #Inspected - #Failed
Want to pull data into ANOTHER WORKSHEET from this one wherein the format will be :

Month - #Inspected - #Failed

Indirectly want Excel to directly convert the data by input and categorize into Month in another worksheet (will be linked and would be great if according to the date it could calculate the week as well)...so if somebody inputs defects by date it should automatically cumulate into respective month.

View 7 Replies View Related

Toggling Data Validation Cells From Another Worksheet

Feb 3, 2014

Is there a way to have a user on sheet1 pick from a pull down of a data validation on sheet2? I have important information that is linked to only sheet2 within a data validation. I want to be able to reference it in sheet1 to have the user pick, then it toggles them on sheet2. Is this possible?

View 2 Replies View Related

Reference Data Validation List On Another Worksheet

Apr 22, 2008

I have a spreadsheet in Excel 2003 with many sheets all of which need to have for some columns the data entry restricted to a specific list.

To do this we have created lists defined and held in a separate sheet and then attempted to reference these from all the other sheets using Data -> Validation and then entering the relevant list in the Validation criteria.

The 1st sheet we set the Data Validation up in all is fine. The 2nd sheet however I get an error pop-up stating 'You may not use references to other worksheets or workbooks for Data Validation criteria'.

View 4 Replies View Related

Data Validation: Can I Use Data Validation In A Cell That Has Formulas In It?

Apr 3, 2009

I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.

View 2 Replies View Related

Data Validation Using List In Another Worksheet - Works For All But One Sheet!

Dec 30, 2008

I am running into an odd issue and hope someone might be able to shed some light.
I have created several lists on one sheet in a workbook and have assigned named ranges to each individual list. So far, so good.

On various other worksheets, I have created Data Validation rules to allow users to select the relevant data from drop-down lists. (using formulas such as =List1, =List2, etc. in the Source box to capture the named ranges I had created).

Everything works perfectly...EXCEPT for one worksheet, which continues to give me the error message, "You may not use references to other worksheets or workbooks for Data Validation criteria." Can anyone explain why this error would only occur on one worksheet and work fine for all the other sheets? I'm perplexed!

View 3 Replies View Related

Data Validation List To Select Worksheet In Macro

Apr 8, 2007

I have a Data Validation list in cell A1 of a worksheet called LIST. I would like to modify the macro that I have recorded, the macro uses data from various worksheets. I would like to sort out some code so that at certain points in the macro it will select the worksheet that is shown in cell A1 of the LIST sheet and then carry out the code already recorded. THis will allow me to quickly change a sheet name that is refererred to in the macro numerous times without actually manually editing the sheet name in the macro code.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved