I have a truth table, let's say it is an ordinary 2^3
ID 1234567 8
condition AYYYYNNNN
condition BYYNNYYNN
condition CYNYNYNYN
Currently I am pasting the results of a DB query into the left half of my sheet and I analyse this data using Excel functions in the right half.
Among these functions are cells that evaluate the conditions above, e.g.:
cond A is evaluated as true or false in column H
cond B is evaluated as true or false in column K
cond C is evaluated as true or false in column M
What I would like is a function to place at the end of the row (col T) which will display the ID of the relevant column in the truth table that the three values together correspond to, e.g.:
A=T, B=T, C=F gives result 2
A=F, B=F, C=T gives result 7
I have a macro that creates a new sheet, the name of which is based off of an input box. Does someone have some generic code to ensure that the user inputs a valid name? Here's what I have, it only checks to see if the name is a duplicate:
'Prompt user for the name of the output sheet newsheetname = inputbox("Name Your Output Sheet:", "Notice!", "Margin Output")
'Check whether or not the name is taken TestName: For a = 1 To Sheets.Count If Sheets(a).Name = newsheetname Then newsheetname = inputbox("Name Taken. Please Rename Your Output Sheet:", "Notice!", "Margin Output") GoTo TestName End If Next a
It's the last road-block left to making this function work. The Problem is in the If...Then statement. If A is true the formula works, but is it is not, then it returns a value. The Expression
IBGLink = (E & CustomDate & EndOfLink) works on its own, so there is an issue with If Application.WorksheetFunction.IsError(A) Then Does anyone know an alternative way of validating a variable or a trick of sort that can be used here. A to E and CustoDate and CustDatePN are declared as global variables.
Function IBGLink(IBG_URL As String, FrstDate As Date, ScndDate As Date, Optional EndOfLink As String) A = Mid(IBG_URL, Application.WorksheetFunction.Find("/200", IBG_URL), 8) B = Left(IBG_URL, Application.WorksheetFunction.Find("/200", IBG_URL)) C = Right(IBG_URL, Len(IBG_URL) - (Len(A) + Len(B))) D = Left(C, Application.WorksheetFunction.Find(".200", C)) E = Left(IBG_URL, Application.WorksheetFunction.Find(".200", IBG_URL)) CustomDate = Format(FrstDate, "yyyymmdd") CustDatePN = Format(ScndDate, "yyyymmdd") If Application.WorksheetFunction.IsError(A) Then IBGLink = (E & CustomDate & EndOfLink) Else: IBGLink = (B & CustomDate & D & CustDatePN & EndOfLink) End If ................
i am trying to make a user form with two msg boxes (To and From Date). In excel spreadsheet It was rather easy to bring a popup msg if they were invalid under some stated rules. see below a rationale of the rules where A3=From Date and B3 To Date and the desired outcome of is:
[Code] .......
So i wonder if it is possible to make a VBA code to validate these two dates? and bring a warning msg after keypress or after pressing submitcmd or any other available if available.
I'm attempting to require a numerical entry in a cell using data validation. The function =AND(ISNUMBER(cell),NOT(ISBLANK(cell))) does not perform as intended. Unchecking "Ignore Blank" has no effect. The ISNUMBER function evaluates to TRUE on a blank cell. When used outside of data validation, NOT(ISBLANK(cell)) evaluates to FALSE on a blank cell, making me think the AND(...) function should be sufficient.
I have been trying to achieve the following with formulas but have not been successful. I am making a simple schedule, and I am trying to validate the cells so that you can not schedule someone for multiple tasks during the same time range. In the example that I attached, Harry is diswashing from 1:00 - 4:00 but is also scheduled to paint from 2:00 - 5:00 (this should not be allowed). How can I achieve this with code?
I'd like to create a data validation with 1 dropdown list in the second worksheet in B4, relating to 3 cell ranges on the first worksheet (COS, Expenses & Capital). If 3 can't work, I've created 1 called 'dropdown' which incorporates all 3.
which formula I need to write into the validation, or what else I need to do in order to find a solution to this.
I have two userforms. When the user chooses the choice "Other" in the userform frmTradeSickOther, another userform called frmOther will be called. At this point, the user will enter some required comments in a textbox (called txtOther) and click the "Add Comment" button. If the user does not enter anything, a prompt will inform the user to type something in the textbox.
My problems: Once the user clicks okay to the prompt, frmOther is called back. When it's called back, I want the cursor to blink in the textbox. Eventhough I have SetFocus on the textbox, it does not work. I don't know why.How do I prevent the user from just hitting enter (i.e., accepting blank lines as comments) and bypassing the commenting part. The code below accounts for hitting enter once (i.e., one blank line), but not multiple times. How do I do this?
Here is what I have when the user clicks on the "Other" choice in frmTradeSickOther:
Code: Private Sub optOther_Click() Unload Me frmOther.Show frmOther.txtOther.SetFocus 'This gets the cursor to blink in textOther when frmOther is called. End Sub
Here is what I have when the user clicks the "Add Comment" button on frmOther:
Code: Private Sub btnAddComment_Click() On Error Resume Next txtOther.Value = Trim(txtOther.Value) 'This is to make sure spaces aren't accepted as a comment. If Me.txtOther = vbNullString Then Me.Hide MsgBox "Please enter the necessary information.", vbInformation, "Required Field" Me.Show Me.txtOther.SetFocus 'Here is where I set focus on txtOther. Exit Sub End If
'Everything below is to add comments automatically to each cell when a value is changed.
Application.CommandBars("Cell").Controls("Delete Comment").Enabled = True If Not ActiveCell.Comment Is Nothing Then Application.CommandBars("Cell").Controls("Edit Comment").Enabled = True Range(curCell).ClearComments
I want to inform my users via a message box if they have not entered the previous month's information. The months are populated via a User form using a combo box selection. The months start from April through to March and are entered into the worksheet range ("aa3").
Data is entered monthly by the team. I don't know how to begin with this. I've managed to inform them when they've already entered that months information, but I don't know where to begin with this.
while trying to limit the user's input to a userform textbox to no avail. For example one textbox on the form should only be numbers and I therefore want to restrict the user to typing in a two digit code like 02 or 72 (not for calculations). Another textbox I only want to allow the user to input 6 characters in the format letter, four numbers and a letter. If the user inputs the wrong stuff a message box pops up and the focus is reset
I'm building a form comprising some text boxes and drop down lists. I'd like for data (once input into the form by the user) to input, upon click of a submit button, into an excel spreadsheet, row by row.
Here's where i'm struggling: I need the form to validate data before submitting. Namley, the form must not allow null values to be submitted and will show a message box telling the user what is needed.
Below is what i've got so far. I've tried playing around with this but am struggling to implement the above functionality:
I have been using excel form last 1 year. I do have good knowledge for macro but for validating emailID entry I am not getting success.,So if any body can give me a sample code for validating email ID entry or a macro for checking and highlight those email ID which r wrongly formated..
I'm currently developing a calendar that has a list in it with lets say 4 options. What I want the calendar to do is calculate at a specific 'cell' the number of entries that are selected during the month.
The idea is to have a drop down on each 'day' and a counter that calculates the number of times one specific options has been selected. Once the option has been selected the 'day' will change to the corresponding color.
I have generated a matrix in excel through iteration (I'm trying to calculate a dinamic covariance matrix between 50 values) which looks like this:.......
A 50x50 matrix. What I have generated in each cell is not the formula, but the text of the formula. Somehow Excel has a valid formula in a specific cell, but "doesn't know yet" that within the cell there is no longer a text. So, to make every formula run, I have to go cell by cell pressing F2, then enter, 2.500 times. Notice that in each formula I don't have something like this:
+"+COVAR(Rends!C4:AB4;Rends!C4:AB4)" or '+COVAR(Rends!C4:AB4;Rends!C4:AB4)
but the valid formula: +COVAR(Rends!C4:AB4;Rends!C4:AB4)
I am trying to find out how many projects might be active as of a certain date.
I have a tab in excel that contains project data. For each project there is a "Start Date" and there might be an "End Date"
My question is, how do I count all the rows where the start date is Less than a given date and the end date is after a given date? The wrinkle is... if the end date is empty then obviously it should be counted as well (assuming the start date is still before the given date).
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100. I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest) The problem is i will get "....A file name already exist...do you want to overwrite.." prompt. Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields: Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
I have a very large excel data file, which I want to analyse using pivot tables. The problem is that while most of the columns are headed with the variable name (e.g. country) and have the list of variables displayed under that heading for each observation (e.g. Italy), the years are spread across the columns - i.e. the heading for column X is not "Year", but is 2003, with the next column being 2004, etc.
Is there a quick way I can re-arrange the data so that the layout is consistent and so that I can use it for pivot tables? I have way too many observations to do this by hand.
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
There is pivot table for some reason the last column (YEAccts), which sums all the other columns values is exluding the 1st column for some reason. This "YEAccts" shows up fine in the pivot table "field list". And I followed the source for this and it's a table in a different tab that pulls data from an access query connection.
The name of the column that is summing up the rest of the columns is "YEAccts", but for the life of me I can't seem to find where this column is in the source table tab or even in the access query where the data is being pulled from.
How to find this "YEAccts" column? Also, why would it exclude not summing up the data in the first column?
