The codes in E column are what is entered in a worksheet range (user cannot see the list). Needed is to show both the two-character code in column E and the text definitions in column F in the dropdown. As data validation now works, users only see the two-character code and are not remembering what the code stands for - they need to see the definition.
Is there a way to show both the two-character code from column E along with the text in column F? When user selects, only the code would be entered in the target cell. (Is data validation the correct technique?)
I have set this problem up in closely located cells to construct the issue. In reality, the validation list is well off-screen from where the user is actually inserting the codes and there are many more codes/definitions.
I've written a procedure that loads a list of country selections in a dropdown (see attached/vb editor/ThisWorkbook).
The problem is the valid values for this column need to be just the two digit country codes, not the country code +dash+country name. The reason the dropdown selections include both the two digit country code and the country name is because most users will not know the two digit codes. Presenting the selections in this fashion will make t much easier for the user to make the proper selection.
So here is what I need: When the user makes a selection from the dropdown list, the resulting value needs to be the two digit country code only. Is there a way to incorporate this formula .... =MID(B2|1|2) ... in the vba procedure which runs after the user makes a selection? Or, is there another way to accomplish this?
Let's say I have a summary table on Sheet 1 6000 rows of data on sheet 2.
In Sheet 2 I have 3 columns, Name, Month and Score. Name Column (A) has names of students Month Column (B) has months January, February and so on. Score Column (C) has data from Green, Yellow and Red. Red is if they failed.
In the summary table on Sheet 1, I need to show all the names that got a "Red" Score. And their corresponding months.
It should look like this.
Month l Name l Score ----------------------------------------- January l Mike J l Red ----------------------------------------- March l Joe M l Red
I found this code and am trying to use it to update the filter in my pivot table (sheet 6), based on the data validation selection in sheet 1, but when I make my selection on sheet 1, nothing happens.
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim pt As PivotTable Dim pi As PivotItem Dim strField As String strField = "Region"
I am trying to do in the title so here it goes. I want a user to be able (on form 1) to enter a list of codes that refer to specific requirements (e.g. code PB114 may mean the user needs to perform a pressure test) and then excel will automatically display the requirement somewhere below. It is going to be somewhat of a database. In otherwords lets say in cell A1 you type in a code, e.g. PB114. In a range of merged cells or somewhere below that cell I want the information "Perform a pressure test" to appear.
I need someone to go through them assigning a building number, level and location to each of them. I am using drop down menus, the first choice is building and has the following options:
B1 B2 B3 B4A B4B B5 B7
Depending on the option selected there maybe different locations, i.e. B1 has the following available locations:
1-4 4-16 MER ALL KIT
Whereas B7 only has: ALL. The third column, level, operates in a similar way.
I have a VBA-routine that updates a pivot-table like this:
ActiveSheet.PivotTables("Pivottabell1").PivotCache.Refresh With ActiveSheet.PivotTables("Pivottabell1").PivotFields("WorkCode") .PivotItems("(blank)").Visible = False End With
This routine doesnt work when a new "WorkCodes" are added to the DataBase (theese are hidden in the uppdated pivot-table)
Basically I have a form where the list can expand and contract so there will always be varying row lengths.
What I would like to see is a drop down list that shows a) the actual populated field and b) a description of the field when the drop down list is present.
I believe this can be done with a combo box however that would be a lot of work.
I have a data validation list where more than one value should show the same list. Instead of make multiple range, I want to make it so if any of the values are in a cell the data validation list will show the list that goes with those values.
I want a macro to fire when the workbook opens. I want it to look at what day it is and then show the sheet for the next day and hide all the rest.
Example, with today being Tueday, if someone opened the workbook, I want only the Wednesday delivery sheet to be visible and all the others to be hidden, well very hidden.
Then tomorrow it will show Thursday delivery and so on. If opened on a Friday, it should show the Monday delivery sheet.
I have two sheets in my workbook: One containing names with French and other European characters, the other containing a list of ISO entities for these characters, like these:
à#224 á#225 â#226 ã#227 ä#228 å#229 æ#230
I need to replace these characters in Sheet 1 with the equivalent ISO entity from Sheet2 and print the value into the same cell in Sheet1.
I want to have a four sheet database for a client list. Sheet one would be the "master sheet" and will have a column that has a validation drop down with the values "current client", äctive prospect",""dead deal", etc. I want the information from the master list to automatically move to the corrisponding sheet(s) when the validation is chosen.
Their is sheet in which some online data is comeing.
#1 I have to copy that data in another worksheet and its name shud be last 3 characters of the sheet in which data is comeing. Say for example data sheet is abcd_2781 so new sheet name shud be 781
#2 now i have to copy the data based on certain validation the sheet has 14 colums
the data keep coming in....every time it comes with a unque ID. but when it comes there is a colum which tell us three operations 0=NEW 1=change 2=DELETE
And so when ever we copy data it shud only copy 0,1,2 it shud not copy new, change or delete.
Now there is one more colum in which their is entry ID, it carried diff ID for NEW transactions. But when ever their is change or delete it will be same what ever was generated when it was new. so the validation shud be
when its 1= change it should match its Entry ID and delete the complete ROW with all previous same ID keep this only
When its 2= Delete it should match its Entry ID and Delet the complete row with all same including itself.
i am attaching the input and out put data Input data Output data MDUpdateAction MDEntryID MDUpdateAction MDEntryID 0=NEW 100302 0 100302 0=NEW 100303 0 100305 0=NEW 100304 0 100306 0=NEW 100305 0 100314 0=NEW 100306 0 100313 0=NEW 100314 0 100315 0=NEW 100313 0 100316 0=NEW 100315 0 100293 0=NEW 100316 1 100291 0=NEW 100291 0=NEW 100292 0=NEW 100293 2=DELETE 100303 2=DELETE 100292 2=DELETE 100304 1=CHANGE 100291
Every day for each date of month I have to copy data from mails to my workbook and a last sheet named report in which I need last row value from 7 or 8 sheets for this I have developed following codes which seems to me very long procduer.
I m using Data/Validation function in Excel2003? Is it possible to have reference list of drop down input from another sheet? I recorded the macro for this function and had the following result
Sub Macro1() Range("A1").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=$C$2:$C$6" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub
How can I change reference List from the same sheet $C$2:$C$6 to "sheet2!$C$2:$C$6" or it is a limitation for this function?
I have already set up a data validation, if if you pick a region, you only get that regions items in the next column.
A B C D E
[Code]...
But what I would like to do is in Column E(for this example) have 3 extra lists for Location in each region. So if I pick S in A, then in E I only want the Southern options.
I have checked out this website, but I cant seem to work out how to add the third dependence [URL]....
I need a data validation to prevent entries when they are > 50, but only if the value of another cell in col A is "Payment". The value of 50 is an example, I have another formula to get this number using VLookup.
The idea is that if the value of the cell in col A is "Income", I don't want the entry to be rejected. But if the value in col A is "Payment" or "Transfer" and > 50, I want the entry to be rejected (if I only have $50, then the payment cannot be > 50).
I have a sheet that the is preset with the autofiter on. The sheet is protective. I have the following code as a marco and all works fine but if alldata is not already showing. If all of the data is showing and I run the marco, I get and error. What do I need so if all the data is already showing, one will not get an error?
Also from my code the marco is set to only run in one sheet or if the user is another sheet will the marco run?? I am guessing that the marco will run in any active sheet. If that is so what would I need to change to have the macro run in only one name sheet, for example sheet named "Frog"?
Sub AllData() ' ' AllData Macro ' Shows All Data ' ' Keyboard Shortcut: Ctrl+a ' ActiveSheet.Unprotect Password:="pword" Worksheets("Diag List").ShowAllData ActiveSheet.Protect Password:="pword"
I want to show/hide multiple rows based on the value of a cell on another sheet.
For example,
If Sheet1!A1="A" I want to show hide rows on sheet 2 as follows:
2 Show 3 Show 4 Hide 5 Hide 6 Hide 7 Hide
If Sheet1!A1="B" I want to show hide rows on sheet 2 as follows:
2 Hide 3 Hide 4 Show 5 Show 6 Hide 7 Hide
If Sheet1!A1="C" I want to show hide rows on sheet 2 as follows:
2 Hide 3 Hide 4 Hide 5 Hide 6 Show 7 Show
In my actual example the number of rows to show hide is greater (27 rows per selection) so a range, rather than specifying individual rows would be best.
I have a large workbook that contains markbook info of all my classes that I teach. I don't want them to see all my workbook as it would be too confusing. I would like to hide all the data and have a simple INPUT box on a separate page that would ask for their name. The name would then be used to find the row number, add this to a specific column number (always the same) which hold their marks and then return the mark in a Message box.
Call centre data for financial years located in separate workbooks (1 for each year)
Here's what I want to create.
One main file to consolidate/view all data
I've tried to make this as user friendly as possible by having a validated list, whereby the user can select the year they want to view, and hey presto, the data changes to reflect the year displayed in the validated cell.
I am currently working in an excel file for which i want to create a macro. I am using colomn F as the base from which it should be determined whether a row should be copied. In colomn F the user can select three options through data validation. If 'completed' is selected, the whole row should be copied to the second sheet, and each copied row should be pasted to the next empty row. The data on the source sheet starts at row 11 and should be copied to sheet 2, starting from row 11 as well.