I would like to make an IF-formula in excel. The problem is that i would like to to it for 12 different commands. i.e "IF(A1=14;243;"") but for 12 commands I saw that the in-built IF function in excel only manage 7 commands, is this true?
And how do I write the formula,
Assuming I’ve the following sample data and wishing if the output data could be formatted using some excel formulas, refer to below sample.
Sample data (Sheet1):
StudentA | Lesson1
StudentA | Lesson21
StudentA | Lesson10
StudentA | Lesson5
StudentB | Lesson5
StudentB | Lesson6
StudentC | Lesson9
StudentC | Lesson8
StudentC | Lesson9
StudentC | Lesson15
StudentC | Lesson11
StudentC | Lesson30
StudentC | Lesson13
StudentD | Lesson1
StudentD | Lesson2
StudentD | Lesson3
StudentD | Lesson4
StudentD | Lesson5
StudentD | Lesson6
StudentD | Lesson7
StudentD | Lesson8
Sample output (Sheet2):
StudentA | StudentB | StudentC | StudentD
Lesson1 | Lesson5 | Lesson9 | Lesson1
Lesson21 | Lesson6 | Lesson8 | Lesson2
Lesson10..................Lesson9 | Lesson3
Lesson5....................Lesson15 | Lesson4
.................................Lesson11 | Lesson5
.................................Lesson30 | Lesson6
.................................Lesson13 | Lesson7
..................................................Lesson8
The sample data could be long and a lot, so it's best if the formula can be made flexible to cater for this requirement.
Please refer to the attached file for some sample data.
I want to accomplish a few things in one cell. The basics are this:
I want to pull the lowest numbers from a given sample. The sample needs to have a time component (10 lowest out of the most recent 20 results). Those numbers are to be averaged. That average is to be multiplied by .96, then that final yield needs to be truncated to 1 decimal.
Now, I'm not sure this is possible or not. But, if it is, I want to also select the amount of numbers I pull from the sample to average to be based on the sample size itself. For example, if there are 10 results in the sample size, I only want to average the lowest 3 of the 10.
Is it possible to formulate all of these rules into one cell?
Is there a code that manage Add-ins?
View 5 Replies View RelatedI'm having problem in managing 4000 checkboxes in a sheet. My sheet containt 2000 rows with 2 coloum of checkboxes. The question is, how to know when checkbox tick in one coloum then it will automatically tick the other one. I know how to do it with small quantity of checkboxes (i.e.: 10 checkboxes). But 4000 checkboxes make me headache.
View 4 Replies View RelatedI'm trying to use a Select Case to log production time-tables.
I've tried a couple angles - one being Select Case.
Every time I run the Macro I get the default ERROR "Wrong Model Entered...."
[Code] .....
I'm thinking the problem lies within
[Code] .....
but I'm not certain.
I just inherited an excel spreadsheet that has 10 tabs per every month in the year. Right now it is not that bad as there are only two months in the spreadsheet but I can see this being a headache to tab through later on in the year.
My initial thought is to make a table of contents worksheet that has a bunch of buttons (with macros) to hide/show tabs based on month. (Note: It would not make sense to consolidate the month worksheets into one as each worksheet already has a lot of data in it to begin with.
Is it possible to make a setting in the registry, so next time excel opens
the addin is not "marked" in the addins-list. I can't do it manually because, because the close_event in the addin will be executed and that will screw things up.
Currently I'm looking at the regkeys: addin-manager and options, I see a change in the registry if I close XL when I unchecked an addin from the addin-list. It looks promising, but Iám not familiar with it
I know how to insert, read and manage comments fields in Excel, however I want to be able to refer to a comment in another spreadsheet. For example
Spreadheet One contains a series of figures, against one of which a comment has been attached.
Spreadsheet Two has links pulling information from cells in various spreadsheets including the cell in Spreadsheet One which has the comment.
I have been working on a spreadsheet to manage my players in a virtual sports game. I have worked out that the optimum skill scores for all the positions and want a way to compare a player to the optimum.
For example:
1st2nd3rd4th5th6th7th
Position 147.1737.6742.0533.8313.6711.3317.33
These are the averages for Position 1
How do I find the player who closest matches this optimum. Players have scores in the same 7 skill areas.
I have a set up a workbook which is linked to numerous other workbooks to provide a summary of information in one location.
However, when we reach April the names of all the linked worksheets will change from 'name 09 - 10' to 'name 10 - 11'.
What is the best way of handling this transition, will I have to manually change all of the links?
I have attached a much simplified example, unfortunately I cannot post the original due to sensitive data.
Apologies if the explanation isnt clear enough, let me know and I'll give further detail.
How I use the Private Subs for Workbook_AddInInstall and Workbook_AddInUninstall with my addins and confirm it is nececessary to use them?
For 2 weeks, I have not been able to get my addins to activate and load into the AddIns tab. I think these missing Subs are why. Ironically, Every example on how to create addins never mention this step or it being necessary. I've even looked in Macros book and it does not mention anything about needing these subs in the Creating AddIns section so they will show up in the AddIns Tab.
How I can get my Addins in the tab and not the Quick Launch menu
I lost my command bar and tool bars (excel 97) all I have is the blue bar on top then the column letters. How do I restore them?
View 9 Replies View RelatedI would like someone to explain the reason why when the program is run, the maximum value in column S successfully is selected by being colored in pink, but the minimum in column R between those pink max's (indicated by the entire row outlined in red) is not the minimum.
In other words, look at the numbers in column R. Only the 14.08 rows are supposed to be boxed in red. I can't understand why the 30.08 and the 46.08 are boxed in red??? And puzzling to me is why the 46.08 near the top are boxed, yet the 46.08 near the bottom are not boxed.
I just want to have a column sorted by Z-A order and then A-Z by the same button, I recorded 2 macros on 2 buttons and tried to join codes with "if, elseif".
View 6 Replies View RelatedIs there any way that i could open and enter commands in ms dos
controlling from excel vba code?, like, say i wanted to change the current
directory in msdos from d: to c:, is there anyway that i could do it from
excel vba?
I have a template sheet where the only thing the user need to do is fill in all the appropriate cells and the click the button that says SAVE to save the file to the right folder and file name.
I want to Grey out the ribbon completely so that user can even change the font or any thing else for that matter. The sheet and workbook is protected. Need a way to stop any unwanted tinkering.
I have run into a problem with the Insert menu.
In the submenu
Cells
Rows
Columns
Worksheet
are "Greyed out" and therefore not available.
This happens when the application is first opened - no code is running it seems to be associated with the Personal workbook?
I have not encountered this previously.
IT help desk was its usual high standard - ZIP
I have searched this board without success.
The Microsoft Help functions were also silent.
Sub elaseval()
For each cell in Range(“B7:B50”)
Range("cell.Value").Select
Selection.Copy
Sheets("Input").Select
Range("$E$7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sensitivity").Select
Range("C” & cell.Row : ”T” & cell.Row").Select
Selection.Copy
Range("C" & Cell.Row).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next Cell
End Sub
I am trying to apply this set of commands to each cell in the range B7:B50. For each of these cells, I need "cell.Value" to refer to the value in the respective cell and "cell.Row" to represent the row of the cell that is currently being used.
I am trying to remove File Commands from this specific workbook and force save and send functions through command buttons.
This is the code I tried in the workbook module to remove file commands(Excel 2003):
__________
Sub Workbook_Open()
Dim a As CommandBar
For Each a In Application.CommandBars
Application.CommandBars(a.Name).Enabled = False
Next
Application.CommandBars("File").Enabled = False
Application.CommandBars("Edit").Enabled = False
Application.CommandBars("View").Enabled = False........
I have made an Excel sheet that uses ActiveX controls (Option Box and Check Box). I have included a couple of screen caps at the bottom of this post to better explain the layout. Here are the specifics:
There is one Option Box Group labeled "InputOptionGroup" with choices labeled: "FirstOption", "SecondOption", "RefundOption" and "NoneOption".
Adjacent to the Option Box is a series of Check Boxes that correspond to the choice made in the Option Box with the exception of "NoneOption". The Check Boxes are labeled "FirstCheck", "SecondCheck" and "RefundCheck". Each Check Box object belongs to the group labeled "OutputCheckGroup".
I do know that the value of the Option Box choices and Check Boxes can be either True or False depending on if it has been chosen or marked, but I do not know how to incorporate these values into the action script. With that being said, here's what I'm trying to accomplish:
...when "FirstOption" is chosen, clear values of all Check Boxes and place a check mark in "FirstCheck".
...when "SecondOption" is chosen, clear values of all Check Boxes and place a check mark in "SecondCheck".
...when "RefundOption" is chosen, clear values of all Check Boxes and place a check mark in "RefundCheck".
...when "NoneOption" is chosen, clear values of all Check Boxes (no furthur action).
Also, there are a couple more requests:Is there a way to lock the Check Boxes from user input? I want the Check Box values to be altered only by the choice in the Option Box.
Take the "GPIN" field, the one on the right is locked from user input. This particular cell references user input from the "GPIN" field on the left. Is there a way to return a blank value when the input field is blank insted of returning a "0"? If you notice, all fields on the right reference blank values as "0".
In the left "Amount" field, I need to display a "$" followed by the value in the input "Amount" field
I need to disable the "save as" file menu command when a user opens a shared document. Upon exit I want to enable the save as so that other opened worksheets are not affected. This code works: CommandBars("File").Controls("Save As...").Enabled = False
My problem is this. I can activate this command by selecting run in the VB editor.
But I need for this action to be automatic when a user opens the worksheet. I did this in the editor:
Under general
Sub dISABLESAVEAS()
CommandBars("File").Controls("Save As...").Enabled = False
End
Sub WORKSHEET_BEFORECLOSE()
CommandBars("File").Controls("Save As...").Enabled = True
End
View attached file. I have a complexity rating that I have developed for a set of projects that I am working on. Management of my company have signed off on this matrix as they believe it is most appropriate. In my projects pipeline tab, I have created the risk and complexity ratings using logical commands.
projects.xlsx‎
I have the code that accompanies the attached spreadsheet model that locks the copy, paste, cut, double click and so on, with the right mouse button and shortcut keys, however, the toolbar excel these functions still remain active. How well I block access to the toolbar.
Desable_Ribbon.xlsm‎
I need to transfer two commands to command prompt. The first one is always the same and it calls a command line of another application (BEAM). The second command is for the BEAM command line. It is taken from an Excel cell. The code I managed to write is transfering only the first command, probably because after execution of the first command command prompt is getting BEAM command line. I
[Code] .....
I have not been able to find any references to anywhere, so here I am....
I'm using Excel 2003 that has a menu bar added by another program that interfaces with it (via DDE, as I understand it). The DLL's from the other program have no documentation and/or ability for me to connect to the particular drop-down menu command I'm using. It is a refresh command that re-imports stock market trade data because DDE drops trade messages.
What are the different commands for pasting data from excel to word? The code I have below pastes excel cell data to word as a picture. What if I want to paste excel cell data as embedded in the word document (I don't want it linked, just want the user to be able to edit it as an Excel sheet within the Word document). Thinking that I need to change the "DataType:=wdPasteText" part, but I don't know the correct syntax. Tried wdPaste but that did not work.
sub mysub()
Dim WdObj As Object, fname As String
fname = "myfilename"[code]....
Is there a way (with macros, I''m assuming), to make my workbook always return to the Main tab and Save automatically --- every time somebody closes the workbook from any tab - so that the next person who opens the workbook will have it ready to go on the main tab?
View 5 Replies View RelatedIn the past when I copy and paste a cell, the new cell contains the formula from the old cell, with relationships between other cells maintained.
Recently my excel stopped copying formulas and copies only cell values from the old cell. It now acts as it used to act if I did a Copy-Paste Special -Values operation.
If I do select Paste Special, it grays out the formulas option and only lets me cut and paste values.
I have two subroutines. One subroutine updates some figures on spreadsheet 1, and then calls a subroutine that updates some figures on spreadsheet 2. Spreadsheet 2 is supposed to save and close, and return to spreadsheet 1, but it only gets as far as opening spreadsheet 2 and updating the figures. These are the two subroutines:
Code:
Sub UpdateLegalAndMeans()
Dim wBook As Workbook
Dim count As Integer ' counter for counting down backlog figures
[Code]....
