I need to create a formula that counts up the number of times a specific word is put into a cell lets say 'Carpet' only when another cell is left blank.
Count the number of times between C1:10 the word 'Carpet' is input only when D1:10 is left BLANK?
If looked into DCOUNTA formulas and SUMPRODUCT formulas but seem to be clutching at straws.
My problem seems to be at referring the formula to the BLANK cell?
I cannot work out the syntax to run an advanced filter for all Non-Blank Columns! I have attached an example sheet, and you can see my syntax in cell D4. I am looking to bring through a list of all line that are both Status= "NOT QUOTED YET" and Project Name= NOT Blank. Unfortunatley, the Status Row will be set as NOT QUOTED YET when there is no project name enetered, hence the problem.
View 2 Replies View RelatedI am trying to create a SUMIFS function to sum estimated hours for specific systems that do not have a Completed date. I do not want to sum remaining hours if it has been completed. i.e.
=SUMIFS(Hours, System#,"110-000-00, Complete (Date), ISBLANK)
System #
HOURS
Complete (Date)
110-000-00
0.5
210-000-00
0.5
[Code] .........
I'm trying to set up my formula so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
(I recommend skipping the below text and just opening the attached spreadsheet, instructions/what I'm looking for and trying to achieve and well laid out requirements and examples are in the spreadsheet and much more thoroughly explained then I can do below)
I'm setting up a table that grabs data from columns in a raw data spreadsheet.
For the purpose of the table I'm making, the formula needs to match up both the House, and the year/month from the raw data sheet, then if the two match and are side by side, return the value to the right either in the Plan Field or the actuals field depending on which field in the table the formula is in. If there is no entry or matching criteria for both the house and Month then it should leave the cell blank, not show a 0
So I have a SUMIFS formula that I'm trying to set up so that if there is no criteria match it leaves the cell blank.
So =SUMIFS(sum range, criteria& range, criteria&range), if no matching criteria can be found, leave cell blank, else if matching criteria found then return value from the sumrange.
This is the exact formula I am using:
=SUMIFS(INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B9, 'Raw Data'!$1:$1, 0)), INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0)), Table!$A$9, INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B$8, 'Raw Data'!$1:$1, 0)), TEXT(Table!C$8, "yyyymm"))
Yeah kind of hard to follow, so attached is a very detailed and well laid out spreadsheet showing the example of what the formula is, how it's working/what it's returning and an example of what i want it to return/look like, so please check out the spreadsheet, it explains it a million times better.
Essentially though i want the formula set up so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
Ok, basically C3 is a dollar amount. The default total for C3 is $0.00. If C3 is $0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.
I tried this but it's not working:
=IF(C3="0","",C3-C2)
I am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx
I have a macro, and it mainly works, except the second part where it checks if the value is less than 7, and returns it to a white background.
The cell has a formula in it that returns "" based on another series of cells. I have considered changing that to a zero and formating, but for reasons that don't need explanation, i have chosen not to.
here is the macro. I need the second part to be activated if less than 7, as well as if the cell returns ""
I have this sheet(sheet2) with some data , I have 2 macro, my problems at this time is that I am looking for macro that delete only the blank rows that under the rows with {SELECT ....} and the under the blank rows with {CELL-ENTER......} and all the rest blank leave in place , I am also looking for option to integrate the new macro with the existing one and come up with one macro that I can refer to click button , in case that it is unfeasible , it's ok with me , I just will call the other macro at the from the first macro that I have.
View 4 Replies View RelatedI am trying to write a macro which will insert a blank row at the end of each year(A column) (Last cell i.e Dec 95 and so on).
I have attached the file for the same ....
I have the following data:
Excel 2010ABCDEF2Subset 13datadatadatadatadata54datadatadatadatadata65Subset 26datadatadatadatadata47datadatadatadatadata48Subset 39datadatadatadatadata410datadatadatadatadata411datadatadatadatadata412datadatadatadatadata4Sheet1
I would like to count up from the last row and color row with blank cells in column F light blue, but only through Column F. THe result would look something like this:
Excel 2010ABCDEF2Subset 13datadatadatadatadata54datadatadatadatadata65Subset 26datadatadatadatadata47datadatadatadatadata48Subset 39datadatadatadatadata410datadatadatadatadata411datadatadatadatadata412datadatadatadatadata4Sheet1
I need to insert code to add a page break whenever there is a blank cell in column A. Here's the code I'm using to print.
[PHP]Sub Printlocation()
Columns("C:C").EntireColumn.Hidden = True
lngLastRow = Columns("H:H"). Find(What:=" ", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With ActiveSheet.PageSetup
.PrintTitleRows = "$5:$6"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$5:$P$" & lngLastRow
ActiveSheet.PrintOut Copies:=1, Collate:=True
Columns("C:C").EntireColumn.Hidden = False
Range("A7").Select
End Sub[/PHP]
i am trying to Put a specific value i,e "NO Deliveries" in blank Cells in the same row based on value in 1st cell of the Row which has a fixed value . note:the columns and rows are dynamic for example: I want to put the value "no Deliveries" in cell C because its blank.
A B C D
% of Availability
0.00%
80.00%
I want AB1 to highlight red if date in S1 is not blank and N1 doesnt equal AA1. Is this possible?
View 1 Replies View RelatedI have a worksheet that contains 15 instances of a repeated table over 700 rows. Each table is 45 rows in size and is housed in between the natural page breaks in the spreadsheet.
These tables are populated from data form another worksheet but may not all be used (8 out of the 15 may be used but will always start from table 1 and there will be no missed tables).
In the very top right of the table is a cell value that is only displayed if the table is in use, so will be blank if not used.
Code that will hide multiple rows (45) based on a cell value being blank.
I know that I need to use Conditional Formatting, but I can't get the formula code correct.
This is what I need it to do:
If cell A2 is populated (not blank) AND cell B2 is blank, then cell B2 to be highlighted in bright red fill.
If cell A2 is populated (not blank) and cell B2 is also populated (not blank), then leave formatting as per normal
If cell A2 is blank then leave formatting as per normal
Basically, as soon as someone types anything into cell A2, cell B2 to turn red. Then once they have input something into cell B2, the red highlight can disappear. The inputs will be text, not numbers, so I can't use </>
I have tried varying circumstances of IFs, ANDs, NOTs, ISBLANKs etc and I can't crack
I used this vba code before:
[Code] ......
And it worked perfectly, but now i wanted to use it in an other file but just change the sheets and I keep getting an Error 13.
It should copy the data from sheet insertmeasurements c23 till end of data in the column next to it (is dynamic) and based on cell B1 and the matching category code in column B, put that data in the matching cell in column C to the matching cell (based on the criteria in column B and C) on the sheet storedata.
Attached File : Profitibility Database v1.4.xlsm‎
I've sent out a survey asking people to sign up for one of two groups, A or B. I have a table with a long string of responses, with the name in column A, and in columns B or C there is an X that shows whether they have chosen Group A (column B) or Group B (column C)
I am trying to write a bit of code where it would paste the names into a new table with the headers Group A and Group B. Here is what I have pieced together so far:
Sub Create_Groups()
For i = 2 To Range("B2").End(xlDown).Column()
If Range("B" & i).Value = x Then
[Code].....
G5 - can contain True or false
G6 - contains text but the if is on the basis of this cell being "Hand Delivered" or not
I have tried the following to illustrate what I after:
I have a list of teams, and beside them I need a calculation to be filled.
The criteria is based on a cell reference (a month which can be changed from a drop-down in BM2) and also the name of the team.
dummy 16.xls
In my country, students are penalized when they fail Mathematics subject. That is when a student gets "F" in Maths. The penalty is only for those scoring Division I and II. The divisions change into Division III for those penalized but points remain the same. For example if a student ie Juma gets Division I of points 17 and he fails Maths, he should be penalized and get Division III of points 17. NB: Those who score Division III,IV and 0 are not penalized even though they fail maths.
View 5 Replies View RelatedI'm trying to write a macro that clears the cell in the range V10:X14 if the value of that cell equals 99.
Sub MyDeleteCell()
For i = 10 To 14 Step 1
For j = 22 To 25 Step 1
If Cells(i, j) = 99 Then Cells(i, j).Clear
Next i
Next j
End Sub
It's giving me a "Invalid Next Control Variable reference" I've tried defining i and j as Integers but that didn't work.
I have a range of cells containing text values in BS15:DS50000.
What I want to do is look at each cell in the range BS15:DS50000 and if the cell contains the "" character, then populate text from column BH (if any) as a cell note.
For example, if cell DB42 contains the "" character somewhere within its text, then populate the text in BH42 as a cell note within DB42.
If BH42 is blank, then no cell note. (Also, if DB42 does NOT contain a "", then no cell note either.)
I have a series of data that I am trying to process based on various criteria and though this site I have solved most of the (simple) problems I had. I have one last problem to solve.
I need to add the previous cell to the next one based on a cell value in a different column.
Example: I want to add F2 to F3 if H3=3 but I need it to work so that if H4=3 it will add F3 to F4 and F3 now equal the new value i.e. (F2+F3). If H5 doesn't = 3 then F5=F5. I need the output to be a value not just an equation as I plan on going back and deleting all rows with H=3 in them.
P.S. I don't mind if the output of the calculation of the F column is to a new column or overwrites F. Overwriting would be easier for when I go back through and delete rows based on H=3.
I want to parse a cell if a date is present for example I have 03/26/1985 AL in one cell and want to parse to two different cells however in between those cells I have other cells with different data that I dont want to parse. Basically I need to parse the cells only if a date is present.
View 7 Replies View RelatedI am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View 5 Replies View RelatedI have two spreadsheets.
spreadsheet 1:
Lookup from Order numbers listed from A5:A177.
requested formula in I5: I would like a lookup to sheet 2 based on the order number (F19:F191), to return the cell above the first non-blank value.
spreadsheet 2:
Lookup value:Order number listed from F19:F191.
Data search:AY19:CI191
return the (date) which is in the range above the data search from row AY18:CI18.
I've had a look at few forums but i'm getting mixed responses, having to use index / match / lookup / min / --.
I need to write an excel function/macro/code to assign yes or no to a cell if the Description fits a certain category. I have attached a sample workbook and will explain what I mean.
In the sample workbook, there is data on a company. This included a unique ID, city, state, and description. What I want to do is fill out the yes/no column. So if a particular company has 2 of the 4 necessary "Descriptions", then column G will return yes, otherwise No. It must return yes for a particular company/ID if 2 of the "Descriptions" are "Sale", "Service", "Business", or "Par". I only want it to count at least two unique descriptions.
For example, in the sample workbook, Company A has all 4 of the necessary descriptions, thus everything in column G for this specific client should be yes. For Company B, only 1 of the 4 descriptions matches, so it returns No for all Company 2 columns. Company C has 2 of 4, so it returns yes for these columns. Note, it doesn't matter for Company C that "Business" was listed twice. It only needs to count the first unique time that description occurs.
filter my pivot table using a cell value, rather than manually selecting from the dropdown.
The report filter title is Rnd H, and values start from 0 through to 2, at .1 intervals (so 0.1, 0.2, 0.3, 0.4 etc).
Rather than select "0.3" manually from the dropdown, can the pivot table do this if "0.3" was written in cell A1 for instance?
I need to first merge cell entries of column 2 if cell entries of column 1 are the same; following columns always retain their corresponding first row (see cells highlighted in yellow in attachment).
View 14 Replies View RelatedI'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do.
Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest.
Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN.
There could be 100 or 1000 transactions generated based on the day.