I have a form in which a user pastes test names into a certain column of a spreadsheet. I have a listbox (not an ActiveX control) where I want the tests to be displayed. I want to resize the listbox depending on the number of tests pasted in-depending on where the last row is in this column of test names. Is there a way to do this automatically when the user pastes a new test set in the spreadsheet?
View 2 RepliesI have a listbox filled automatically from some cells. I have to choose an item from this listbox, click a button, and then my macro will summarize some data. All it´s running, but a little problem, the size of the listbox is changing itself after some actions. For example, when i change the value of the cells from where i fill this listbox.
View 2 Replies View RelatedFormula that counts the number of people who are on my course and are taking their tests.
I have 5 classes, Class1, Class2, Class3, Class4 & Class5.
Column A details which class the pupil is in.
Column B details the pupils name.
Column C details the state of the course. This is either a DATE which they started, or "Paid" (they have paid for the course but not started), "Not Paid" (havent paid for course and havent started) or "In Progress" (Payment is in the process of being arranged).
Column D details the state of their final exam. This is either a DATE as to which they have finished and passed the exam, "Not Taken Yet" (Havent taken the exam yet) or "Fail" (They failed the exam).
This is where I am having problems, at the top of my sheet I want to summarise each classes statistics, but I am having problems with the COUNTIF. For example:
What formula can I use to add up the number of people in each class who have started the course?
E.g.
=countif(C10:C100," Is a Date ? ") AND is in Class1 ?
What forumla can I use to add up the number of people in each class who haven't passed the test ?
E.g.
=Countif(D10:D100,"Not Taken Yet" & "Failed") AND is in Class2 ?
I know I need an array to work this out.
And finally to really complicate things how about :
People in each CLASS who have STARTED the course (Date in C) AND haven't passed their test (Col D). I understand this is relying on a three part array whereas the others are 2 parts ?
I have a multicolumn (3 columns) listbox that is filled from a sheet (ProductList). Col1=product code (column A); Col2=description (column B); Col3=prices (columnC). I fill it when the form loads like this:
VB:
Dim rng As Range
With Worksheets("ProductList")
Set rng = .Range(.Range("A2"), _
[Code]....
I am working on a homework assignment, the books method was crap so I did my own method. I am almost done except for 1 last thing. I cannot find any function that will allow for 3 logical tests. I have a screen shot here explaining what I need to happen.
View 2 Replies View RelatedI inserted the following logical tests for cells E5 and E6. I saved the workbook with SE(B) in cell G3.
View 3 Replies View RelatedI have problems with my userform's listboxes. I have two listboxes, and I want second listbox's values to be dependent on first listbox's values.
And even more complicated, I need second listbox's values to be dependent on values on certain matrix.
In that matrix, row headings are listbox1's values and column headings are listbox2's values. How ever there are blanc cells on that matrix aswell. So if there is a blanc cell(s) on a row which (heading) is selected at listbox1, then I don't want that column (heading) which intersects with the blanc cell to be included to my listbox2 values.
Finally I want to insert the selected values from listboxes and the value from the intersection of those listbox values (headings) on that matrix to worksheet.
I included an attachment, where you can see my point better. However, as you can see, now the listbox values are not dependent on that matrix. Otherwise it is working like I want it to work.
I'm trying to format a worksheet to show a one year due date with an alert 30 days out so that I can complete the task before the date due. I am using this for a preventative maintenance sheet for respiratory protection equipment that needs to be serviced annually. For instance; if I service a mask today the next service will be in one year. I'd like it to alert me 30 days out. If I could get that alert through outlook that would be fantastic.
View 5 Replies View RelatedHow do you do two "if" statements?
I need to have two tests before i get a final result so i need to know the protocal to get two if statements.
I have a rather frustrating issue that dates a few months back, so I hope to solve it today. The following code tests a value in Column A. If the value is between 1-5 or equal to 11, column B will return the string A. It works.
View 3 Replies View RelatedBasically, I'm trying to calculate a pooled z-score from z-scores already stored in different cells.
Each individual z-score are calculated like this :
D1=IF(A1=999,999,((A1-10)/3)).
They are derived from standard scores with a mean of 10 and a SD of 3. I use 999 to mark missing values and this work flawlessly for single z-score cells.That way, if A1 is a missing value (999), Excel returns a value of 999.
Now here's my problem. How do I tell Excel to ignore 999 values if I am pooling multiple z-scores together?
Let's say A1, B1 and C1 are single z-scores. I want D1 to be the pooled z-score result, but I want it to ignore A1,B1 and/or C1 if their value is 999. For instance, if A1=+2.0 , B1 = 999 and C1= -1.50, I want my D1 cell to ignore B1 in the calculation.
I've tried : D1=IF(A1,B1,C1=999,999,(LC5+LI5+IK5)/3)) but it sure doesn't work...
I have a set of data in two columns, i.e. 1) dates and 2) names. Now I would like to post in the third column the name "x" if there is more than 2 years (or 730 days) between the current and the previus observation for name "x".
More specifically:
I would like the code/ function to always include the first "new" name then: 1) check whether subsequent names are equal, if equal; 2) check whether the difference between each obervation and the first "same" name is more than 730 days. The code should also always consider every observation with more than a 730 days difference as a "new beginning" for that specific name.
I have tried to use IF and OR functions but the function becomes too long and it does not quite solve the whole problem. Problems with my function occurs when the names change in column two. That is, my function is not able to distinguish between dates that belong to the same name and dates that belong to different names, when performing the second logical test.
I have attached a short example of my data. The problem is solved manually in the example file, just to give a better picture of what I am actually trying to do (I have a few thousand rows in my real file).
I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.
For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.
If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.
If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.
I have an if statement which shows either GP% or average revenue per product. The problem is that GP% needs to be in a percentage format and revenue per product needs to be in number format. 500 is now shown as 50000% or 50% will be shown as 0.5 for example. Is there anyway on changing the format of a cell based on the number?
View 1 Replies View RelatedI have a Master workbook with 4 sheets named WEEK 1 to WEEK 4. At work we work around 13 4 week periods and need this workbook to be dated accordingly, that is Period 1 Week 1 and date of first day (Sunday) of that period. The ideal solution would be for the user to enter 1 to 13 into a cell and via a button all dates to be populated and a new workbook created for that period. I have attached a sample workbook with the 4 weekly sheets and a date range on another sheet to show the range I need the dates from. I hope the workbook explains what I need better than my explanation here!
View 2 Replies View RelatedI have a spreadsheet where I want to record the VO2max value (a number indicating the maximum volume of oxygen a person can utilise) of a person.
The VO2max is just a number, normally between 30-60.
I then need excel to look up the VO2max value (i.e. “35”) and give that number a predetermined inputted value name in a different cell (i.e. “low” or “fair”, “good”, “very good” etc etc).
However, as VO2max is also affected by age and *** I also have a column for the participants age and *** and I want excel to use that to make the value name.
E.g. in a 24 year old male a VO2max of 32 is “low”
But, in a 42 year old female a VO2max of 32 would need to be classed “”moderate”
Any ideas how this can be done? can it be done?
I could post the table containing the age range, values and names if I knew how to post an excel sheet on here.
I want to introduce a number in the textbox1, and that number sets which entire row to select for making a chart. i thought of something like this, but i cant put the variable x just like that.
Private Sub CommandButton1_Click()
Dim x As String
If TextBox1.Text <> "" Then
x = TextBox1.Text
Range("Ax:Mx").EntireRow.Select
[Code] .....
I have a workbook, with a worksheet "Summary". In this worksheet "Summary" I would like to delete all columns that have less than 100 rows.
Please see the attached file : Summary.xlsx‎
I have a workbook has many worksheets, I would like to be able to delete worksheets if, for example, the number of rows in the worksheet is less than 100 rows.
View 3 Replies View Relatedi want to sort a column in such a way that it starts with those cells having the highest number of letter. For example:
before:
AA
AAA
A
AAAAAA
AAAA
AAAAAAAAAAA
I want it to look like:
AAAAAAAAAAA
AAAAAA
AAAA
AAA
AA
A
Ofcourse the real list doesnt contain only "A"s. It contains of words and sentences.
How can i sort columns A as mentioned? The order of column A with other columns should not be destroyed be the sorting process.
The number in cell M21 falls into one of the ranges from C4 to C15. A matrix number needs to be printed in each cell from N21 to N30. The selection of the number to be printed in column N (under Matrix Assignment) depends on the range it is next to in array A4:C15. For example (as shown), the correct number for cell N21 is 122 because 0.2626 falls between 0.24 - 0.2699, and Matrix 122 corresponds to that range.
View 3 Replies View RelatedI am looking for a formula which will insert a word into cell A3 depending on the number in cell A2. For example A2 is between:
0 - 10 then insert the word Div 1
11 -17 then insert the word Div 2
18 - 23 then insert the word Div 3
24 28 then insert the word Div 4
Example below of the data I have. I need to convert dates into DD.MM.YY (8 chars) for SAP. I receive spreadsheets where the date is dd/mm/yy and I use the replace to convert the "/" to ".", then format this column as TEXT. My problem is some of these sheets have hundreds of records and rather than individually scroll through and look for data that requires the "0", is there a way to insert this. I can custom format that it looks as it has the zero, but it is not in the true data.
1.10.12 data should be 01.10.12
11.10.12 this is correct
3.11.12 data should be 03.11.12
3.06.12 data should be 03.06.12 etc
I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........
View 4 Replies View RelatedI have listbox on a userform with number and the way it should work is that when I click on a number in listbox the data correspond to this number should be copied from one sheet to another sheet and gets plotted on graph. The problem is when I click on a number in the list box the data moves from one sheet to another and get plotted but only after I exit the userform sheet.
View 14 Replies View Relatedhow I would be able to change the formatting in a userform listbox to Number format (i.e #,###.0). I am currently pulling data from an access database to populate an excel Userform listbox but the numbers come in as Text. I would like to change this to a Number formate.
View 9 Replies View RelatedI have a text box that I would like to populate with the total number of lines used by a listbox.
I tried:
Trying to have a formula assign a number to a cell depending on a number in that cell.
Example
In all cells in a range from C8 to G13, if there is a 1, i want the number in C20 to show, if there is a 2, whats in
D20, 3/E20, 4/F20, 5/G20, 6/H20
I have a form with a list box on it, I want the listbox to display a number (1 through 20), the agents name, and a count of how many observations they have had. Unfortunately I have been unable to find an example of how to add multiple columns to a list box. I found the usual listbox1.list(x,x)= "whatever" type stuff but that doesn't seem to work right.
how to populate multiple columns including the headers, or point me to a site that explains it or has code examples?
Here is the data I want to put in the multicolumn listbox (the number and the agent name are from a worksheet, the deskside is a calculation sumproduct based on the 3 sheets that make up the quarter....
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.