Im looking to count fields in column G, where the first 4 letters of column G do not begin with the letters "free", I came up with the following formula which doesn't work at all!!
Im also looking to do the same but with a sumif to perform the sum on a different column of data, but with the criteria LEFT(G15:G164, 4) & "" & "free") - (not sure if this bit is right either) staying the same.
I've been pounding away at a project for two days trying to determine a way to identify a solution, to no avail.
Here is some experimental code. Under the assumption that in Column "A", every fifth line contains some text (a, b, c, etc) I want to place a function in column B that would identify the closest occupied cell above and to the left of the cell containing the function. The sub achieves this, but the function has a problem because of the Activecell line. Is there a way to identify the location of the cell containing the function to act as a reference point for the range?
Sub find_last_heading() Application.ScreenUpdating = False x = ActiveCell.Address ActiveCell.Offset(0, -1).Select last_heading = ActiveCell.Text Do Until last_heading "" ActiveCell.Offset(-1, 0).Select last_heading = ActiveCell.Text Loop MsgBox last_heading Range(x).Select End Sub Function cathead(Optional reference)
How to correct my formula because it does not work?
Here the formula: =COUNTIFS(Data!C2:C24005,A17,Data!M2:M11149,"<=0")
I need to insert the formula in column C (Findings tab) which counts the rows in Column M (tab named "Data") that equal A17 (Column A in "Findings" tab) and which are less or equal zero. In addition if I drag the formula down I want to only the values be entered in highlighted in blue cells in column C (SKUs With Zero Sales in "Findings" tab) and empty cells in not highlighted cells. I attached images of these two tabs: Data and Findings.
i'm trying to get a column to count all blanks but only if there's and entry in the cell to the left. for example i have a list of names which is picked up from my main database in column a, then in column b there's dates, non applicables and blanks. however the columns are longer than the list of names to allow for growth, so there's a lot of blanks at the bottom which i don't want to count. so is there a way to count only the blanks in column b if there's a name in column a alongside it
Simply need a cell to count another cell is equal to certain value.
For example if B3 has a value of 6 then I need J3 to equal 1 or if B3 is equal to 3 than I need G3 to equal one. I tried the the COUNTIF but I am sure i am using it incorrectly.
I need to count the number of equal cells in col D beginning at the top of the column. The counted cells must begin with a text prefix of "Category:" without the quotes.
Some but not all of the cells in col D begin with a prefix of "Category:" without the quotes, followed by a word or words following the word "Category:" See examples below. All of the terms prefixed with "Category:" in col D are in alphabetical order. I need to count the number of identical cells in col D with the "Category:" prefix.
Examples of the contents of cells in col D with the "Category:" prefix are as follows:
Cells above and below cells with a prefix of "Category:" in col D are not adjacent.Cells above and below cells with a prefix of "Category:" in col D are separated by 3 to an undermined number of rows.
I need to count the number of equal cells in col D and insert the count in col A at the last equal term. For example, col A above would have 93, 1, 1, 5, 10, 8, 3, and 12 inserted into col A.
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like Variable 1 Variable 2 Variable 3
VBA code which can distinguish whether a cell has been edited by:
selecting an option from a data validation list (DVL) or manually editing the text in a cell?
The ""Show error alert after invalid data is entered"" option is unticked to allow both selection from the DVL as well as free format text entries.
The problem arises when using the code below which makes an edited cell within a range display the new value as well as the prior value(s). This works well when using the DVL but not when editing the cell.
Is there any code which can distinguish between the 2?
I'm creating an Excel sheet with statistics for a yearly salary review.
In the document that the managers will fill in there is a column for the employees performance (+, = or -).
I want to use this to compare how much salary raise employees has gotten depending on their performance rating. This is all fin with the + and - just using the regular countifs formula, but when it comes to the = Excel refuses to count them?
I'm looking at test results and would like to know how many times in a row correct answers were given before getting one incorrect. The way I have this currently set up, is if a student has the answer correct it returns a "TRUE" value, and a "FALSE" value for an incorrect answer. A typical test may have 50 questions, so I would have either "TRUE" or "FALSE" values in rows A1 through A50.
So I've been trying to figure out a formula that counts the number of times "TRUE" rows repeat 'consecutively' in a given column, in this scenario column A. The formula would need to check the current row and if "TRUE" check the row immediately after, if that row is also "TRUE" then check the row immediately after that, until it runs into a "FALSE" string, then display that total count of consecutive "TRUES" in another cell. I understand there may be duplicate results but I'm only really interested in the longest value, so duplicates can be ignored.
If it makes the formula easier, each possible result can be assigned to it's own cell. Let's assume the test has 50 questions. So if a student got all 50 questions correct the result count would be 50 in cell B50, or if they got 25 correct before the first incorrect answer, cell B25 would say 25, etc. I'm guessing I could then easily query this column for the largest value to come up with what I need at a glance.
I have attached an example. Basically I had this set up to calculate the row and give the answer at the end. But now I cant see the results without scrolling, so I want to move the answer fields to the C column and add new data into the D column while keeping all the data in the formula.
count 0 and negitive number cells since last positive number from right to left.xls
I'm trying to use logic to identify trends...in each of the strings below, I want to count the occurrences of the left most character appearing consecutively. The answer is to the left
I'm trying to do this via a formula vs. vba if possible
I have a column that is giving unwanted value . dont know the reason as that excel file has been created by some other guy and I just started working on it .
My Question is how to move to 2 cells left(A for example) from from that unwanted value column. and check if A is equal to cell above it , means B Cell(Row above A but same column).
As my excel file is totally based on Forms, Macros, I am not quite familiar with macros.
Is there any way to put if condition in one cell (column) and drag it all the way down which should work for all the values in these 3 column.
And also if A=B then I want to make that unwanted value cell="".
have six different locations in a city in which I have 6 beds for people with psyhiatric problems to sleep in.
The managers of the different location send me whenever somebody new comes or somebody leaves the following information:
Name of client Start date of stay (when new clients arrive) End date of stay (when client leaves)
I have to keep all record of clients that have used our locations, so the list gets very long during the year and it is then very hard to see which beds are still available.
What I would like to see in a separate sheet is which location still has how many beds available. I then mail this overview weekly to organizations that assign people to my locations.
Does anyone knows how to calculate if translator is free or not, regarding that weekends are free ( I mean if weekends are free how to check if translator is able to translate a document)
I have a pivot table with slicers to easily filter the data. I need to add a column with editable, free text that is filtered along when using the slicers.
If I try adding a column in my data source of the pivot and I change one value in the pivot then all rows that have this value change along.
Im looking to use this code to move the selected rows of a workbook over to a new worksheet in the sameworkbook. It works fine however for some reason it pastes the row over the last row. how to modify it so it pastes the row on the first empty row?
Sub MoveSelectedRows() Application.ScreenUpdating = False Dim strSheetName, strCellAddress As String strSheetName = ActiveSheet.Name strCellAddress = ActiveCell.Address(False, False) Rows(ActiveCell.Row).Cut Sheets("Closed Projects").Select 'Change sheet name to whatever consolidated tab name is. Range("A" & Range("A65536").End(xlUp).Row).Select ActiveSheet.Paste Application.CutCopyMode = False Range("A" & ActiveCell.Row).Select Sheets(strSheetName).Select Range(strCellAddress).Select Rows(ActiveCell.Row).Delete Application.ScreenUpdating = True End Sub
Also, as a side note, how do I put a prompt in so that once I press the button to move the row the user is prompted asking if they are sure they want to move the row? 'Are you sure you wish to move row X?' Yes/No.
I have a macro that is performing a lot of string comparisons and sorting out a lot of unformatted data using a loops and instr calls and passing results into arrays to store in a database. The macro works flawlessly, however as the macro runs, over time is gets slower and slower until its barely moving 10% as fast as it was to start.
I run the macro in a separate windows session so I can continue working w/o the macro affecting other excel related tasks I work on. I can tell the computer begins to slow down as the macro runs. Is their any way to clear the system memory while the macro is running, maybe every 5 minutes or something?
How can I allow users to either select the SKU from the dropdown menu, or to type in the SKU # in the dropdown menu (C5 through F5) and have it populate the data fields for that SKU when they press the 'Enter' key?
Currently using Data Validation ='KVM Comparison Data'!$B$4:$OP$4 and cell logic to allow the information to be pulled from a hidden tab =INDEX('KVM Comparison Data'!$D$2:$OP$65,MATCH('KVM Product Comparison'!$B6,'KVM Comparison Data'!$A$2:$A$65,0),MATCH('KVM Product Comparison'!C$5,'KVM Comparison Data'!$D$4:$OP$4,0))
In short, I want to keep it the way I have it, but allow users to also have the option of typing in a SKU and pull the data up that way. I know this is possible, I just can't figure out how to do it, while keeping my current solution in place as well.
I need to know how to format columns in a spreadsheet. What I want to do is autofit the columns, all except for Column A; I want that want at a fixed width of 9. The code below is my attempt to do this, and it's not resulting as I wish. Stepping through the code, it performs the first part beautifully and autofits all of the columns. Where I have the line
Any excel formulae to build in into my financial model for tax free period/tax holiday, eg for first 6 years, the project company no ned pay tax? it is best if the formulae can bring flexibility for user to change the no. of years (tax holiday).
I've found this calc but it doesn't compare to my particular scenario/goals: http://dinkytown.net/java/InvestmentDebt.html. The major difference is that this dinkytown calc requires a new loan to be put in place and I am not in a position to refinance my current mortgage. My plan is to pay an extra $267 (b3) to the mtg (b4). It will take 128 months (b6) to pay off the mortgage. After that point, I need to calculate what the newly freed capital (b5) would do if I put in an account (i.e. a CD or a simple US savings account) that had an Annual Percentage Yield (APY) of 1, 2, and 3% for 20, 15, 10 and 5 years. I thought that I had the right formula in place for cells b7:b8 should the account earn a 0% rate of return, but I think it's faulty since it gives me a negative number for 10 and 5 year accumulation periods.
I am having trouble filling a formulae series to the left on one spreadsheet, the fomulae being references to another sheet.
For example, I have two sheets 'Mtce Options' and 'Base Case'. In 'Mtce Options' I have the following formulae
A B C 1='Base Case'!A15='Base Case'!D15='Base Case'!G15
I want to fill to the left, incrementing the column references by a factor of 2 each time, eg. next two should be ='Base Case'!J15 and ='Base Case'!M15.
However, if I autofill to the left by highlighting A1, B1 and C1 or just B1 and C1 all I get is an inappropriate reference such as ='Base Case'!D15 or ='Base Case'!F15, respectively, in D15.
