Spreadsheet - Output Choices / Multiple Dropdowns In Sequence
Mar 14, 2014
I am looking to create a spreadsheet wherein the user can choose multiple drop downs in sequence and then have it output each choice to a template or empty space within the spreadsheet.
For example, lets say column 1 said what is your favourite colour and had a dropdown with two choices red and green. Column 2 says "favourite place" and had a drop down with two choices "London and USA". Then once the user chooses an answer in each column it would output these choices to notepad or a large space within the spreedsheet.
In this example it would output:
"Red
London"
if the first two answers were chosen from each column. Obviously it would be a much larger scale than the simple example above.
I have a function which either looks up a value on a spreadsheet or runs a sub. If it runs the sub, it should generate a list of figures which are output to a spreadsheet.
My function keeps crashing but I have found by stepping through it that is correctly decides whether to lookup a value or run the sub. It starts the sub correctly but when it comes to outputting information to the spreadsheet it crashes. I have lx_calc set as an array and I am using the following to output to a spreadsheet (lx_calc(20) is defined as 10000 and ImpFac and qx have already been calculated):
For i = 21 To 120 lx_calc(i) = lx_calc(i - 1) * (1 - (1 - ImpFac(i - 1)) * qx(i - 21)) Workbooks("Macro Ann Addin.xls").Worksheets("Mbr").Cells(i + 1, 1) = lx_calc(i) Next
I can't see why this doesn't work unless it is just that it doesn't like the fact it is looking at an array.
I am trying to calculate phone call rates based on several different conditions. *if my phone call has a "DESCRIPTION" of "Local" then I want to add 2 cents onto the "COST" *if my phone call has a DESCRIPTION of "Mobile" then I want to use the formula = SUM(G7*24*60)*0.4+0.1 to calculate the charges *if my phone call has a "DESCRIPTION" of "13 Number" then I want to add 5 cents onto the "COST" I am able to use a formula that allows 2 condition but I have been unable to work out a formula where I can add more conditions. I have attached a spreadsheet
When I try to insert an 'option' box in which either a check box or radio button are used, only one option can be used. For each option I have to create a new option. But then, each option can be selected, which defeats my purpose. I want to create an option box where there are two option in the box, but the user can only select one of the options, not both, or neither.
I've finally figured out the correct code to allow multiple selections from one of my drop down lists. Now, when I try selecting an item from a separate list (which I only want to be able to choose one thing), it's accepting multiple items. How do I have both working properly?
This is the code I have for my multiple selection list (which is only in column M):
Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count > 1 Then GoTo exitHandler On Error Resume Next
I am working on a grading sheet for our company. I would like users to choose from an option button choice (A, B, C, D, F) for each grading category and I would like to write that to a sheet that corresponds with the graded person. The form will have next and previous buttons to move through the grading process - when previous is chosen I would like the option buttons to populate what was stored in the data sheet. I think I can mottle thru the code individually but with 60 option buttons it may take quite some time.
Can I call an option button choice by "Group"? I started coding individually, but it will take a lot of code - surely there is a more efficient answer. A picture of my form is attached at the bottom of this post.
VB: Private Sub OptionButton1_Click()Dim Grade As String If Me.OptionButton1 = "A" Then Grade = "A" Else Grade = "" End If
I am trying to create a simple order entry worksheet. In cell M1 I need a dropdown box that lets the user select which options go on the product. You can have multiple options. The list of options is contained in column A of the Options worksheet. Depending on which options are selected, I want to put a 1 in column B of the Options worksheet. how to do this or have a sample workbook that I can look at.
I am having a problem with some code I am trying to work with. My problem is that I need to source information which is selected on a userform in 3 list boxes and copy this information into the excel sheet behind on the same row.
Sheet 1 has the following columns on it, with the following possible values. People (Dropdown with value between 1 and 8) Modifier 1 (Dropdown with Text 1, Text 2, Text 3) Modifier 2 (Dropdown with Text 1, Text 2, Text 3) Modifier 3 (Dropdown with Text 1, Text 2, Text 3) Modifier 4 (Dropdown with Text 1, Text 2, Text 3) Result
Sheet 2 Contains a set of values (Distance) that correspond to the value of the People dropdown (ie. People 1 = Distance 5, People 2 = Distance 6 and a table that looks like the following:
Mod 1 Mod 2 Mod 3 Mod 4
Text 1 0 0 0 0
Text 2 2 0.5 0.25 0.25
Text 3 7 1.75 0.875 0.875
What I want to happen is that the Result field value on table one is the result of:
On sheet on I select People = 2, Modifier 1 = Text 3, Modifier 2 = Text 1, Modifier 3 = Text 1, Modifier 4 = Text 1 which I want to result in the following formula:
6*(7 + 0 + 0 + 0)
This might be exceedingly simple, but I just cannot wrap my head around how to do it.
I've attached my .xlsx file for easier understanding.
What I want to do is segment out a contact list by "market area" and "department" and pull the corresponding data with those labels from the Contact List tab into the main tab when validated by the dropdowns.
1.) The Market Area dropdown represents different offices. 2.) The Department dropdown represents departments within each office.
What I want the user to be able to do is to select the 2 dropdowns at the top and view an entire listing of the roster of those departments based on how they are labeled on the Contact List tab. I'm still a relative newbie to vlookup/hlookup so I've tried using them and encountered issues with it returning more than one value or being difficult to fill down the next series of values, etc. I simply want it to return the entire set of employees that fit the identifying dropdowns.
I need to establish a method of interleating multiple dropdowns or Form Control boxes. The purpose is to select one item and have a selection of multiple items associated with the selected item. Example:
Computer training dropdown box 1 offers selections of word, excel, powerpoint, ... If selection is Word, then dropdown box#2 offers advanced in room #15, Intermediate in room #16, Beginning in room #17. If Box 1 selection is excel box 2 selections would be different.
I have here an example of how I might extract data from a source workbook and paste it onto a template workbook, using autofilter to find the rows that are needed. It is currently set-up to take rows that match "AREA 1" or "AREA 2" in column A, along with a number of other criteria in other columns.
What I want to do is add into this macro, a way for the user to be able to choose the filter options will be used for column A of the source workbook. So if they want only "AREA 1", they can choose just that, or if they want "AREA 2", or perhaps both "AREA 1" & "AREA 2", or further additional options. They need to be able to specify just one, multiple, or all of the filter options with regards to column A of the source data.
In effect what I need is a user input box or form with a dropdown menu that will give the filter choices based on column A of the source data, and then for the code that copies the data across, to do so based on which choices the user makes in the filter menu of the input box.
I have uploaded examples of the source data workbook and the target template workbook. The macro exists within the template workbook.
[Code] .....
How it may be possible for the user to be able to specify, one, multiple, or all available filter options for column A of the source data, via a user input box or form.
I am trying to create a simple user interface type thing so that someone is able to select from drop down lists someones information, such as whether they are male or female, aged between 19-35 or 35-67, whether they are studying in a business area, legal or construction etc (there are 6 variables in total), This will then give the probability of success of the person passing this course based on probabilities which I have already worked out. I have worked out how to do the first stage of creating a drop down list showing alternative choices with Sex, Age etc in the data validation options, however:
There are 517 possible combinations, as in Male aged 19 to 35 studying Business (with other variables) or Male aged 19 to 35 studying Law (+ other variables) etc etc etc each with their own probability of success. Due to the long nature of writing out Male1935BusinessNorthWestWhiteBritishCollegeBrown I have rewritten it so it appears in the excel file as M1935BNWWBCB, which obviously wouldn't make any sense to someone if they had to select M 1935 B NW WB CB from drop down lists.
Along side the M1935BNWWBCB there is the probability of success specific to that type of person. So for example I could would have: M 60% M1935 64% M1935B 35%....
I'd like Excel to run a check to see if a row of numbers runs in sequence.
*As in row 4, the numbers dont have to incriment each time *As in row 2, there may be gaps in the data *As in row 6, the data cant increase and then decrease. Each subsequent number must be the same or high than the previous *There could be varying amounts of numbers per row, hence the results being in column R
As an added bonus it'd be nice to see where an error occurs, but just knowing theres an error (column R) is the primary goal.
What I am attempting to do is have my spreadsheet automatically detect and highlight personnel working seven days in a row. There are 63 total columns, the first one being for the person's name. The next 31 columns are a full month's dates in which their day shifts are recorded, while the final 31 are the full month's dates for night shifts worked. Days worked are listed as a "1" in the cell beneath the respective shift's date, while days off go down as "3". Currently I am using conditional formatting with the following formula, which highlights cells in red:
I created the conditional formatting formula to apply to the row for the first person listed, and then copied and pasted the formatting only for the rest of the personnel rows. The issue I'm having is that while it will highlight the seventh work days, it will often highlights all cells prior to the grouping which has seven straight days of work. It also tends to highlight single or multiple cells in red which do not fit the pattern, and whose rows may not even have seven straight days of work.
I have the following data: (The Dept can occur multiple times in Col B.)
Col A Col B Row 1
[Code]....
What I want to do I want to insert a formula in each row of Col A that returns a number that indicates if the Dept in Col B is the first occurrence of the Dept, the second occurrence of the Dept, etc.
I'm trying to create an If then statement that will will allow multiple criteria and output the corresponding data if possible.
I've attached a sample data file to provide insight. I've also used a random number generator formula to create the numbers for the output. When I press F9 I'd like the data to appear in the dream house lottery section with the information of the house that corresponds the Neighborhood and street. What I have in mind below.
If C21=B2,B8,B13 and C22=B3,B4,B5,B9,B10,B11,B14,B15,B16, Then values of E,F,G,H,I appear in E,E,G,H,I appear in the lottery dream house section.
So =IF(C21=B8),IF(C22=B11), ???? IF(C21=2) and (C22=3), Then This is where my problem occurs. I'd like my output to be that of E through I11 to appear in E through I21.
Is that possible and does that make sense. I have a long winded version but it requires the formula to be in each and every cell and it will only allow for one neighborhood at a time.
Writing a formule to lookup for a value and return more than one result,
Example
Col A has Names, Col B has ID number, in Col C I need the output to look for names in col A and return the ID from Col B, I can use a VLOOKUP for this but the problem when col A has a name repeating more that once, In this serinario the lookup picks the first name and gives the first ID
Here is how I want the out put to be as:
Names ID Output John 1 1,3,6 Mathew 2 2 John 3 1,3,6 Mike 4 4 Bob 5 5 John 6 1,3,6
Im working a spredsheet for my work, it is a database of qualifications for certain employees. There are about a hundred different qualifications and about 20 different employees. And some of the qualifications overlap. I would like to input a certain date in one of the cells, and have it copy into some of the other cells where the quals overlap. But i would like to input that date into any of the cells that overlap, not just one.
I have been working on a little vba project and have almost got there with help from others, but the final part needs to have a tally a number of columns and and produce the tally results to a new worksheets - the code that need proof reading /reviewing is below - it is not working correctly as it is not tallying correctly and at times seems to miss counting the last row - so could you review and suggest or make required changes so that it will work on the attached test file.
WHAT IS NEEDED IS;
1. Column D contains names -strings which are repeated such Fred Flintstone so for Fred and others would like to have a summary tally of how many times each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U.
With the output summary worksheet called "WORKER TALLY" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.
2. Then do same again for Column E - Names so for example the name Peter Pan and others would like to have a summary tally of how many each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U. With the output summary worksheet called "Names" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.
Sub workertally() Dim b() As Variant Dim NewWs As Worksheet Dim j As Integer, i As Integer Dim a As Range, v As Range, r As Range, c As Range j = 1 Set a = Range("E6", Range("E" & Rows.Count).End(xlUp)) Set c = Range("E5", Cells(5, Columns.Count).End(xlToLeft)) With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare
in the above table I need to the sum of WC for each Language code & batch no. eg. for Language "id" & batch 1 I need to get the SUM of WC corresponding to the criteria.
What I need is a script or formula with returns multiple results and puts them into one cell.
The Excel is used as a Project Managment Work Sheet for Resources, working on different Projects and their dedicated hours
The raw data table looks like this: Column A: Project Name (=AllProjectsLists) Column E: Resource Name (=MasterDataResources) Column P: values from 0 to 8 (hours)
First I need to check per row the Resource Name. If the Resource Name is a match, than I need to check if in column P the value is bigger than 0. The result goes into a different sheet per row, one result per Resource, but the multiple results should be shown in one cell.
So the result should be something like:
Resource name "Thomas" .... Projects working on: "Project 1, Project 5, Project 13, ..." (in one cell) Resource name "Mary" .... Projects working on: "Project 3, Project 9, Project 13, ..." (in one cell)
I tried with this one, but it only returns one vale per cell:
in column A i have fruit words (e.g. apple, banana, orange...)
in column B i have cities (e.g. london, paris, rome...)
i would like a formula in column C that gives "british apples" when "apple" and "london" are on the same row whilst also giving "french bananas" when "paris" and "banana" are on the same row.
I need a 'simple' vba that outputs all the dates in a range that correspond to a particular ID in a column as well as that same ID in a column beside the dates.
So basically using the data in columns A to B as input, to get columns E and F as output.
This is a very SMALL sample of the data I need to sort through. We have been using filters but the data is growning and becoming far too time consuming using the filter method.
The output I am looking to achieve is something like this, (any format is fine; whether in rows or columns)
SVR1 1.99.99.254 (2) CAT I (display how many CAT I for SVR1) CAT I: 2011-B-44 CAT I: ST-5546 (2) CAT II (display how many CAT II for SVR1) CAT II: 2011-B-52 CAT II: 2011-A-21
SVR2 2.5.2.333 (1) CAT I CAT I: 2012-E-55 (1) CAT II CAT II: 2011-A-21
COLUMN A, varies from SVR1-SVR1400 (cointains duplicates) COLUMN B, IP matches SVR name (contains duplicates) COLUMN C, will be either CAT I, CAT II, CATIII COLUMN D, will contain duplicates
I have some history of using formulas and nested formulas but this one is really throwing me for a loop. It feels like Inception to me and I'm not really sure where to start!
get the code for sending multiple emails as per the spreadsheet list. Assume the spreadsheet has 100 line items and each columns specifies the name of the person, value, recipient email address ("To" and "CC") and sender name.
And the Body of text is :
Hi "Name of the person" Please find the value of "Value" to be paid for the moth of xxx and kindly let me know for further clarification.
