In cell I2 I have a data validation list. If the selection is "2" then I want to copy and paste a value from another cell to another sheet.
I did a recorded macro but the code does not recognize the selection from the data validation celll, I2.
Is there some code I can add to get the macro to recognize what the user selected?
I've got a spreadsheet and I've used data validation to look at a list on another tab.
I need to be able to multiply values out based on what i choose on the drop down menu.
I've attached a sample spreadsheet to explain : tasks.xlsx
I'm working on a simple worksheet, it is a tracking sheet for programs taught. What I'm trying to find is a code to insert a row based on a Data Validation List.
Exampl of data:
Column A is for " Name" then futher down Column K is "Program Taught" and Column L is " Date Taught".
Column K is where the Data Validation List is located with a in cell drop down.
What I would like to do is when a user selects a program from Column K a row is inserted with all of the formulas and formats from the previous row, and upon insert would like it to copy Column A into the new row.
This would be so I can keep a historical of programs and dates taught.
And to really spice things up I want to lock Columns K & L of the previous row after the new row is inserted.
I am limiting choices in a certain cell to a list via data validation (using data validation, allow - list and pointing to the source). I want to limit the next set of choices in the next column (in this example in Column C) for the user based on the choice made in the adjoining cell. For example to keep it simple I will use the following: if the user selected "Fruit" in B1, then the options in C1 would only show "Oranges","Apples", "Pears". If however the user selected "Veg" it would only show "Cucumber", "Lettuce", "Spinach", "Radishes" in the drop down in column C. I can put a prefix in front of the column C data validation list if that is needed to narrow down e.g. FR-oranges, FR-apples etc.
A B C
1 Fruit Oranges
2 Veg Lettuce
3
Data Validation source for Column B (H2:H4)
Fruit
Veg
Nuts
Data Validation for Column C: (I2:I10)
Oranges
Apples
Pears
Cucumber
Lettuce
Spinach
Radishes
Almonds
Peanuts
I currently have a drop down menu in one of my worksheets, in which I have several different text values entered. What I would like to do is link each of those text values to a numerical value, which would be entered in to another cell. So if I select "Option A" from my drop down list, and Option A is equal to 200, I want "200" to show up in another cell. If I select "Option B" from my drop down list, and Option B is equal to 400, I want "400 to show up in that same other cell.
View 4 Replies View Relatedis there a way to force so that a cell value can be only of what a validation droplist offers, hence users can not enter their own values and have to use the droplist for cell content selection.
View 2 Replies View RelatedI have a Range on one sheet formatted like this and sorted by Sup then Rep:
Rep Sup
John Sally
Mark Sally
Joseph Sally
George Betty
James Betty
etc..
I have a Validation Drop Down list where a Supervisor is chosen. There's a specific range that I would like to automatically fill in with the reps assigned to the Sup. This is driving me crazy! I already know how to obtain that list as another Validation Drop Down, but not quite sure how to make those values appear in a range instead of a Validation List.
I need to get data copied from sheet1 to sheet2
depending on selection in a validation list.
The data is on the same row and all the same range.
So it would look something like this.
A B C
Fruit Orange Apple Pear
Veg Potato Carrot Onion
Animal Bear Cow Dog
I have the data in A in a dynamic range and validation list.
I need all the data copied from the specific rows ie
choose Animal from the validation list in Sheet2!A1 and
the entire row Bear Cow Dog get copied to Sheet2!B1, Sheet2!C1, Sheet2!D1.
The second problem I think is a lot more complex.
So the same scenario above but this time its a multi select on Sheet3.
I need to be able to select two (or more depending on Fruit and Animals to display in A1, A2 and then their valid options to appear in B, C and D
I have created a cascading data validation list, and I would like to have an adjacent cell auto populate according to the dropdown selection.
Data Eg:
Oranges 1100
Apples 1101
Pears 1102
Grapes 1103
If the user selects "Pears" from the dropdown list, I would like the adjacent cell to populate with the numerical code for "pears", in this case 1102. I am designing a new expense report form at the office, and I am at a standstill with this problem.
I'm looking for a method, vba macro or Excel code, to preform the following task:
In the C column the user chooses one of several options from a menu, let's call these options 1 - 10 (this part is already done). Depending on the choise, different things are supposed to happen:
For 1-9, the formulas for cell A - Q on the current row should be copied to the next row (but with +1 in row number in formulas of course).
For 10, 3 blank rows should be created (well they already are blank..), on the forth row down, A3 - Q3 should be copied exactly. On the fifth row, the same formulas thing that was created for choise 1-9 should be created.
Since the real xls-file is too big already and it contains some sensitive information, I can't attach it here, but I made a small and similar xls-file, that better explain what I want to do.
In all the cells in column F I have a validation list with several predefined choices, but at the bottom of this list I want an "Other..." option, which when chosen, enables the user to enter a new string in that cell if none of the predefined did not match.
View 7 Replies View Relatedvery complex spreadsheet for weight & balance calculations. It's to the point where everything works perfectly in Excel 2007, but it must be used primarily with Excel 2003. Discovered that a crashing problem had to do with condtional formatting, that's all been cleared and will soon be fixed, but there's one that I just can't quite figure out.
I'm using the worksheet change event to trigger the update of charts... In this case, it's looking at a particular cell that has data validation on a dropdown as the trigger. Works perfectly in 2007, and if I put a msgbox prompt in to be launched by a change of that cell, it launches. I can put the chart update code in a separate sub and launch it manually every time, but I cannot call it from the worksheet change.
I've included attachments showing what I believe are the relevant bits of code -
Is there a way to trigger a warning box (ie - must be divisible by 50) when a specific entry is selected from a pull down validation list? The triggering factor is a correct answer to the validation so I do not think the error alert will work in this instance.
View 2 Replies View RelatedI'm trying to continue on this post: Populate Cells After Choice From Drop-Down
I'm trying to expand on CNL's idea. What I need to do is create a spreadsheet that autopopulates two blocks based on the dropdown box that preceedes it. I've got it to partially work, but can't figure out the next step. The differnce between my sheet and CNL's sheet is my drop-down is an indirect data validation.
The easiest would be to have my data in one long list, but my goal is to make it easier on the user to find the specific procedure (I'm trying to build a budgeting spreadsheet for a medical office). So in box A15 there is a list of the different service area, which gives a list of choices in C15 based on the choice selected in A15. My goal is to have G15 and G16 list the cost and charge respectively.
M1 - O3 is the data for Radiology
Q1 - S4 is the data for Labs
U1 - W4 is the data for Office
I am trying to find a formula that calculates the longest period from data in an adjacent cell.
The attached spreadsheet might better explain this
I copy/paste the data from this website [URL] ........
How I can sort all the data based upon the "Date"? The date data is not recognized when I copy/paste.
Whenever I copy and paste dates from the internet I can't get Excel to recognize the info as dates, thus making it impossible to sort by date. It doesn't matter which site I use, I have the problem with all of them.
[URL]
Using any of the links above, how I can paste those dates and get Excel to sort the date?
I want to make a lesson table which distribute the names to lesson choice priority.
You can see detail and explanation at attached file. LessonChoice.xlsx
I have a worksheet that has 8 activex listboxes. Each listbox is tied to the sames list of values (identified as a named range). The named range is a list of countries. Each country should only be selected once, therefore, I would like the selected country(ies) to not show up as a choice when the user makes a selection from another listbox. If this is too hard, maybe we can get a msgbx to appear anytime the users tries to select a country that has already been selected.
View 3 Replies View RelatedI'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 RelatedI have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
I have a combo box on my userform. The selection made in the combobox will eventually control a calculation. I would like to generate some code so that when the user makes a selection in the combo box, and fills in all the requied info on the form, they can click a button on the userform which will generate a new combobox on the worksheet which would contain the user selection, along with the array that populates the combobox on the userform.
View 3 Replies View RelatedMy Main data in my workbook is copied from another workbook. From there it is sorted into separate worksheets for each customer via a macro. I want to be able to set something up so that I can list the customers and then list job descriptions, products and pricing from the correct sheet and return the selected data back to its original form in sheet1.
I am also trying to run the macro that sorts the data in this workbook from another workbook.
I have attached the file so you can see what i am trying to explain.
Charting Data Based On Drop Down List Choice
I am trying to build a report where I can chart data base on a dropdown selection.I did build the dropdown lists, however I don't have any expertise on execel functions in order to make it work.I am attaching my draft report.
I'm trying to do is build a form that will allow me to select from a list of options, that links back to a catologue of data so that when i click on the generate button it will pull the data associated to the item selected from the list into a text box in excel. I have attached the form that I have created.
View 4 Replies View RelatedI have a football pool worksheet. My goal is to have a button that displays a message box with the players in order of how many points they have. So far I have been able to display this message box with one problem...
The problem occurs when there is a tie between two players. If they have they same score it will only display the first score in the row. In my attachments when you press the macro button "score" it displays a message box saying:
"Sue is in first place with 12 points.
Bob is in second place with 9 points.
Bob is in third place with 9 points.
Larry is in forth place with 3 points."
I want it to say:
"Sue is in first place with 12 points.
Bob and Dave are tied for second with 9 points.
Larry is in (third or forth, doesn't matter) place with 3 points."
Even if it needs to say
"Bob is in second place with 9 points.
Dave is in third place with 9 points."
would be an improvement.
Here is my code so far and i've attached the file.
I have a spreadsheet which will be used to measure raw material consumption by day.
I wish to list all days of a year down a column - that I can do.
However, I would like to conditionally format any cell (preferably the row) which contains either a Saturday or a Sunday.
I am tryig to create a macro that looks up a value between two workbooks. In the workbook the value may be on the 1st, 2nd, or 3rd worksheet. So far all that is returned after the macro is run is a cell with the formula in it with no values. This is the macro formula:
Do While rcnt <= lrow
Cells(rcnt, 3) = "=if(iserror(vlookup(cells(rcnt,2),rng1,12,0))=false,vlookup(cells(rcnt,2),rng1,12,0),if(iserror(vlookup(cells(rcnt,2),rng2,12,0))=false,vlookup(cells(rcnt,2),rng2,12,0),if(iserror(vlookup(cells(rcnt,2),rng3,12,0))=false,vlookup(cells(rcnt,2),rng3,12,0),"""")))"
rcnt = rcnt + 1
Loop
I have three lists in the first sheet 'Food', 'Quantity', 'Points Value'
Each food (i.e. tuna, pasta, anchovies) has a quantity (i.e. 40g) and a points value (i.e. '50'). The first list ('Food') has been copied as a drop down list in the second sheet. When selecting a food, I would like the cells 'quantity' and 'points value' to pre-fill - taking the data from the first sheet.
i.e. if say Anchovies has the quantity value '30g' and Points Value '2' on the first sheet, then in the second sheet if anchovies was selected from the drop-down menu, these values would add themselves. how to do this? I have attached a sample file shwoing what I want to do.
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