Title should read "Data Validation Lists"...oops
I'm trying to format a spreadsheet so that based on the value in Column A, Coulmn B displays one of two spearate dropdown menus.
I know the following formula will not work, but that is the effect I want in Column B.
Just curious if there is a way to do some sort of Data Validation form that will check the previous selection and remove it from a list for the next selection? I have several machines that my company sells and I am working on an advanced invoice. I am trying to set it up so that if one machine is selected, it will populate the data validation boxes with one set of "options" for that particular machine. Also, when the user clicks on one option, no matter which one, it will remove that option from the list below it. I have posted up somewhat of a screenshot/crop/paste example of what I am attempting to do.
View 1 Replies View RelatedI have a number of different formulae I want to invoke according to a choice. I am able to request the formula I want by entering them ‘in line’ as value1, value2 etc into the CHOOSE formula … e.g. =CHOOSE($H$1,IF(N3>O3,1,""),IF(N3<O3,1,""),IF(N3=O 3,1,””))
but this is cumbersome and confusing since I have a growing number of choices and some of the formulae are quite long. I want to enter them in a list and extract the formula which corresponds with the Index_num in the CHOOSE formula. I also need the cells in the formulae to be relative so this rules out using NAMES to refer to the formulae.
I have this issue where I get an #N/A error when I i run a VLookup in cell H7. I have posted the worbook.
I want to find the value in column D for the value you choose in cell G2
Excelforum.xlsx
I am creating a box CBM optimizing tool at work for shipping our products internationally so that the sales department and logistics can estimate shipping costs and weights with out needing me to physically pack everything in advance. I have broken every product we sell into individual CBM's as well as the boxes that we use to ship. I have also set it up to give a break down of "boxes needed" to accommodate the approximated CBM by box size.
How to create a function that will take that info and calculate which combination of boxes will utilize the available space... In other words, how do i take my break down of the 6 box sizes available, with the "# of boxes needed" data i already have, and have it tell me which combination of boxes will utilize the maximum percentage of space, or leave the least amount of unused space based off of the cumulative CBM being shipped.
Snippet attached:
Capture.PNG
I have a workbook with the twelve months of the year as seperat worksheets. a thirteenth worksheet is a "total" page. I have three different names to choose from a drop down list in collumn E. I need a formula that will say something like, "if JanuaryE4 = John Doe, total page A1= January A1" Is this possible?
View 5 Replies View RelatedI've got an IF statement that's based around what's chosen from a drop-down list. The problem is I'm using "" to stand for nothing being chosen in the cell in question, and this is returning an error message and highlighting the '""' (that looks confusing).
I've tried checking and unchecking 'ignore blanks' in data validation but nothing much has happened.
Here's an example of the formula I'm using:
=IF((AND(C40=A,D40="")),C9,(IF((OR(C40=A,D40=DA)),C9/2,"")))
The problematic "" is the first one.
my spreadsheet attached, when you open it up you will see the tabs on the bottom, each of those tabs are departments.
This spreadsheet track all the jobs coming through my department, placed by other departments.
On the right hand side of each sheet are print numbers columns, and total hour columns.
at the end of a job I record how many hours the job took or how many dollars it cost. Then the job is close out and that line of information is sent to the archive page.
The archive page is where all the information is stored, print numbers hours and dollar amounts and so forth.
what I would like to do is, set up another sheet within the workbook that would sort all of the part numbers and associate them with hours or dollar cost.
I'd like to have it start the search when a new job is entered and I put the print number into a cell, in any of the department pages.
1. I would like to be able to play with it by choosing different categories, sub-categories etc (adding different ones etc.). I tried pivot table but to get this data in pivot table format is a very time consuming task to start with.
2. The sample file has only one month. I get this type of data every month. So have to annualize using other months etc. I could get different months in a column also. So for example column d could have Jan, Feb, March etc. for row of data. But that would mean I will have 1922 * 10 = 19220 rows. And when I update this every month row reference from formulae (If I had formulae) will need to be updated.
I have the flexibility of getting this source data rearranged little bit but not too much flexibility there.
In the following code snippet I have worked out a way to verify that the user inputs an Integer and then prompts downstream if an improper value was entered. The user is prompted for a corrected reponse and loops until they get it right. The initial Cancel at the first prompt was causing issues until i saw poster: titarelli use StrPtr(). Except for subsequent cancels, the code works but it is clunky at best. How to tighten this up?
VB:
Dim Quantity As Variant
Dim CorrectedQuantity As Variant
Dim j As Integer
j = 194
Quantity = 0
CorrectedQuantity = 0
[Code] ......
I have a VBA userform with various textbox fields like 'Name' and 'Address' and 'Amount'
I would like an input box to pop up that asks the user to 're-enter for verification'
So that they have to type the same thing twice, to protect against typos
How do I code it so the program compares the inputbox to the textbox and passes only if they are identical?
(or even, using two inputboxes instead if that would be easier)
how do I code in vba for it to wait until the user has checked the file that has been made make a few amendments and then when done one would click OK or resume for the program to carry on with the rest of the code.
View 2 Replies View Relatedi have followed the following criteria correctly
Create a function GetProducerRow() which takes in one String input (give it any name following the conventions) and returns an Integer.
Write a Select Case statement inside the function.It should compare the input string with the various companies listed in the Voting tables in cells F5:F9.
The Case should not use hard coded values for the names of the companies but something like Range("F5").Value.
In each case, set the return value by assigning the appropriate row number. For example, the value assigned would be 5 for "The Hershey Company" store in F5.
Remember to write Case statement for each of the 5 producers.
Add the Case Else in-case none of the producers match the input and have the return value set to 0.
Function GetProducerRow(strProducer As String) As Integer
strProducer = Range("F5")
Select Case strProducer
I am trying to check what was selected from a validation list range. I need to put up a message box if a certain selection is made. I will try to give some more explaination and detail.
I have a 'Labor Type' cell that has a validation list based on the 'LaborType' range that contains things like T&M, PM, Claim*, Quote, etc. When Claim* is selected, I want to put up the message box reminding the user to enter some additional information.
I have tried a few things like Worksheet_SelectionChange that I can't seem to get to work. I admit it has been a while since I have used a function like this (and never with a list).
I've have a spreadsheet where 4 cells are linked to another workbook via a vlookup.
the problem i have is that a lot of users can update this external book, or it can be saved as a seperate spreadsheet somewhere else on the network. If it was up2 me i would have them only update the one sheet, but as it stands its not. So what i want to be able to do is put some code onto a button on the sheet, from here i want the: Application. GetOpenFileName
method to open....but from here i want them to be able to pick the cells where the data is situated. Any clues..... i can get as far as them selecting a workbook. Do you think i will need to create another userform?? Maybe RefEdit? I'm not sure.
I started a new collection of cards (soccer cards actually) and since I trade with some of my friends via mail, I needed something to keep track of the ones I have and the ones I don't. I made an excel spreadsheet with the numbers of the cards.
Here's my question (I don't know if its possible or not):
I there any way I can create a way to make the verification easier by, for example:
I just got card A20. I write A20 on a specific cell and, in the main table, that number changes color to indicate that I already have that card.
And in the end, a way to reverse the process so I can have the table brand new for the next use.
when you choose Data Validation for a specific cell, you can choose list. Once you have chosen list for "Allow", you can select the source cells for what will be in the drop-down menu. I want to be able to use VBA (or just use Excel) to modify those source cells.
For example:
There will be 3 lists with drop-down menus using Data Validation.
List 1:
Choose first option in list -> List 2 uses source A
Choose second option in list -> List 2 uses Source B
etc...
List 2:
Choose first option in list -> List 3 uses source C
Choose second option in list -> List 3 uses source D
I am trying to create a worksheet which can calculate the Outstanding Work Estimation based on available information.
The information available with me is presented in Rows 3, 4, 5, and 6. For these types of work orders, I have information regarding their Previous Outstanding Status, New Orders, Completed Orders, and at presented Unworked Orders (or Outstanding orders at the end of Jan 2008).
Second set of information is available in rows 9 to 23. This information provides for which type of orders were completed by Staff 1 and Staff 2.
Based on these two sets of information, I am trying to get to the point where I can calculate the following:
a) Applying the weight of orders (namely 1A, 1B, 1C and Blend) by using the drop down list function in column B, I should be able to get the results in Column C (C25 onwards till C40), Column D (D25 onwards till D40), and Column E (E25 onwards till E40). Simlarly for all other months...for February, March, and so on...till December 08.
I'm trying to write what is supposed to be a simple script that looks at a column of usernames (ColumnA) and passwords (ColumnB). I start off by prompting the user to enter their username in an input box and then I want a loop to check this value against the values in Column A. If the username exists, I want to prompt an input box for the password. If the username does not exist I need some kind of limit on the loop to prompt an input box for the user to enter a new username and password that I'll add to the last row of the data table. The loop part is kicking my butt, this is what I have so far...
Sub Main()
Set StartRange = Worksheets("16.10"). Range("A1")
username = InputBox("Please enter your username.", "Enter username", "Jdoe")
i = 1
Do While StartRange.Offset(i, 0).Value <> "username"
i = i + 1
Loop
If StartRange.Offset(i, 0).Value = "username" Then
password = InputBox("Please enter your password.", "Enter password", "password")
End If
End Sub
I am trying to save some time converting a cut up inventory catalog number. Specifically focusing on the color code. I have the list of colors associated with their specific part number. The order cannot change or my sheet will mismatch.
What I want to do is convert the # in column A to the color code text in column b so that it matches the translated code in column C? For example
1 I want to tell 1 to = Polished Chrome anytime #1 appears in my column.
What formula should I choose to achieve that goal. As well, how do I know what data to imput into the formula wizard?
Sheet is attached.
I have Cut a few thousand items off the bottom of the sheet. I can apply to additional data left off.
I have a spreadsheet listing various pieces of computer hardware, software and mobile accessories, etc with costs attached.
I have created a dropdown list of staff profiles eg a sales representative, finance assistant, IT administrator.
What I want to do is get Excel to pull the data through from the data sheet, to a printable summary output form (something you could also cut and paste from). For example, a sale rep would need to be set up with a laptop, laptop case, mobile phone, etc, so when you select Sales rep from droplist, I want that action to pull all the associated data through to the summary form. Similarly, a Finance assistant would require different kit , perhaps a desktop PC , monitor , etc.
Next to my data I have created columns with "Y" for yes in cells containing kit that match the staff role requirements, but I am not sure of the best way to pull this together.
I am trying to have a column that returns either "secure" or "re-assignable" depending on whether the employees title matches the appropriate office/ space allocation.
e.g. an employee who's title is "CEO" and office type is "Large Exterior Office" would return a secure. However an employee with a title of "Admin Assistant", who's office type is "Large Exterior Office" would return "re-assignable"
I have a condition setup (under "This Workbook") upon opening a spreadsheet to verify a computer's predetermined motherboard serial number that will shut the Excel workbook down if the numbers do not match. This works fine. What I'd like to do is to add a second condition that will allow a password to be entered in a textbox if the numbers do not match so that the serial number condition could be manually overridden, if necessary. If the serial number and the password are incorrect the workbook automatically closes.
Here is the code that works:
Private Sub Workbook_Open()
If CreateObject("Scripting.FileSystemObject").GetDrive("C:").SerialNumber "-XXXXXXX" Then ActiveWorkbook.Close False
End Sub
Here is what I am attempting to do that is not functioning properly - need to add a textbox to enter a password to override an incorrect serial number
Private Sub Workbook_Open()
If CreateObject("Scripting.FileSystemObject").GetDrive("C:").SerialNumber "-XXXXXXX" Then
Dim Rng
Rng = InputBox("aaaaaa")
If Rng "aaaaaa" Then ActiveWorkbook.Close False
End Sub
I would like to add a piece of code into the user form that will check and verify if a part has already been added so as to avoid multiples in a user-driven/created database.
here is a repost of the current code i am using for the user form (I have posted it before in another thread .. Blane245 helped me out with a different question I had)
My problem in the given excel file
1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list
2. Sheet2; how to remove "B000" or "B00" from the list
3. Sheet3; how to highlight difference or find out the difference between to lists
Update_List.xlsx
Is there a way to make a drop-down list optional? When I create drop-down lists the user has to make a choice from that list.
Example: a list of doctors. I need the user to be able to select from that list if the patient saw one of those physicians, but if the patient saw another doctor not on the list, I need the user to be able to enter the name of that other doctor. Currently the user has to select from the list or not enter a doctor. I am using Excel 2003.
I would like to have a list that when one of the items is chosen, it brings up a second list and so on.
Therefore, list 4 will depend on what was chosen from List 3 which will depend on List 2 which will depend on List 1.
I'm not sure if macro's are required.
I have attached a file that lists out a Decision Tree (Decision Tree Tab).
I also have a Form tab which is where I have the decisions that need to be made.
So, in the Gender Row I can choose Male or Female. If I choose Male, then the Interests list should contain Cars, Sports or Colors.
Type will depend on which I chose and finally, Detail will depend on which Type I chose.
We work in a Work book with Various sheet.. in this workbook A master sheet (masterdatasheet) & many other datasheet (Each sheet with diff data)
We want a VBA for my master sheet with a Combo box or list type data validation.. where we choose Sheets Name ......... after choosing datasheet... those sheet data Dispaly in My Master data sheet....
with MasterdataSheet we are caclucating some calculation which is which is sown in bottom line in this sheet
My Sample file is attached with 2 datasheet.. (in my original sheet more then 50 sheets so we enclosed only 2 sheet for trail baisis)
(My Hiden sheets protection password is san19241 or san if u want to open those files).
1) Imagine i have a row 1 with names, column 1 with bank account numbers, and under each name corresponding to each bank account it says "yes" or "no" depending on whether or not they are authorized on the bank account. How do i make 2 drop down lists, 1 with bank account numbers that produces the names which are authorized on the account and visa versa (list with names showing bank a/c numbers). It needs to be able to be updated easily.
2) Suppose I have the table as described above and another excel file with 1 column as bank a/c's and another column is authorized bank users. In this column however each cell just has a bunch of names for example one cell may contain (james, john, jack, jennifer, bill). Is there anyway to check these names against the ones in the table from the other spread sheet?
I have two lists of serial numbers which I want to compare to find those number which are not in both lists. I have used the following formula: =IF(A2=$G$2:$G$283,"",A2) where A2 equals the first cell in list 1, G2:G283 the range of the second list, true value "" false value display the contents of the original cell.
This works so far; where the contents in the A and G cells are the same on the row eg A2 and G2, but where they are different the contents of the A cell are being returned even if they are elsewhere in the range.