Hi I have these formulas that look in a specific column and if the argument is true then it sums the numbers in the same row but different column.
I need it to count instead of sum. These are array formulas so in order for them to currently work you must press ctrl shift enter.
{=SUM(IF('sheet1'!I7:I510>D7,'sheet1l'!G7:G510,0))}
{=SUM(IF( 'sheet1'!I7:I510>B8,IF( 'sheet1'!I7:I510
I'm trying to add either a new condition to a formula or modify existing formula, so blank cells can't be taken as a number like 0 Here is the formula =IF(N14>0,1,0)
***This is what I do with formulas with similar problems =IF(COUNT(Y14,A14)=2,Y14/A14*100,"")
unfortunately the above condition its already a condition by itself so I don't know what to do whether to modify condition or add a new condition which I don't know how to do.
I'm constructing a dynamic chart and I'm having difficulty naming the series formula.
I wish the Y values to pick up values in a different sheet and have entered the following formula :
=Rnd4Projects.xls!,Timeline!ChtYLeics
Workbook is called Rnd4Projects and the data is contained in a different sheet, called Timeline.
However, I keep getting an error message to say there is a problem with the formula. how this formula needs to be entered in Series Values box?
I have the following formula:
=TEXT(D2-C2-TIME(,60,),"[h] h
s: m mi
s")
It's taking out 60 minutes for lunch. Now I want to use this to calculate straight time and not deduct any time. How can this be modified?
I need to use the valve in the last row in column 27 and subtract the value in column "Y". Looking to modify this "R37C27" to dynamic range
Range("AG1:AG" & lr).FormulaR1C1 = "=IF(R[2]C[-13]=""Nanner Table"",R37C27-RC[-8],RC[-6])"
I use the merge button a lot for a group of cells in a column. However then I have to edit the formula in the merged cells.
I'm hoping there is a macro that can be made to bring these together.
Here's an example of what I do:
select range C2:C4. Cell C2 contains the formula =sum(B2)
merge these cells.
edit formula in new merged cell to be =sum(B2:B4)
Can I group this process into one macro?
I am needing a macro to modify a cell reference contained in a formula. For example: I have "=B2" in a cell of my worksheet, and I need some code to select and increment that by 1 so it becomes "=B3". I know how to use the ActiveCell.Offset command, as shown below, to select a specific cell so that it can then be modified, but I can't figure out how to make the actual change. ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Select
View 3 Replies View RelatedI have 2 sheets, lets say SheetA1 and SheetA2. I am using Vlookup to serial number & getting data from SheetA1 to SheetA2. But I want to modifty the data. If i modify the data in SheetA2 that modification should relect in SheetA1. Is it possible...
View 5 Replies View RelatedI can drag it down to fill a series of cells within a column.
Let's say the formula in cell A1 is:
I am using the following formula to find out the No. of "HH" in the range given and I am getting the results simply with numerical value as 2, 3 etc. I want the result should be displayed as 2HH, 3HH depending upon the No. of "HH" available in the given range. The result should be displayed as (the No. added with the text "HH").
=IF(AND(DJ10="HH"),COUNTIF(DF10:DJ10,"HH"),"-")
I use excel 2002.
I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.
View 2 Replies View RelatedI want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.
But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.
E.g
Large Parts Current Stock = 50
(enter in) Large Parts Used = 2
Large Parts Current Stock = 48
(Clear field where 2 was entered into Large Parts used)
(Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
View 6 Replies View RelatedI am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.
The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
I want to count cells in column AA that are graeter than 160, and in column N = "RM" and in column A = "CBP". Can't seem to get this right.
View 4 Replies View RelatedI have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....
It looks something like this.....
Column A Column B
Document No Category
11000001 A
11000002 B
11000003 B
11000002 A
11000001 A
Is there anyway to do this without subtotalling for each document and then a count?
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View 2 Replies View RelatedI have a file where I want to count number of cells where the value is greater than 0. in the attachment, i just want to populate that count below the column indicated therein. So in the example, desired result is two.
View 4 Replies View Relatedin writing a formula that will count the number of times
the store is listed (Column B) when it matches with closed (Column C).
On the table listed below I will return the data using a match.
From this table
A B C
1/8/2006 9:45Store 1Closed
1/8/2006 9:57Store 2Closed
1/8/2006 10:05Store 3Closed
1/8/2006 10:09Store 4Closed
1/8/2006 10:15Store 5Closed
1/8/2006 10:24Store 1Closed
1/8/2006 10:36Store 2In Progress
1/8/2006 10:41Store 3In Progress
1/8/2006 10:50Store 4Closed
1/8/2006 10:58Store 5Closed
1/8/2006 10:59Store 1Closed
1/8/2006 11:15Store 2Closed
1/8/2006 11:22Store 3In Progress
1/8/2006 11:24Store 4In Progress
1/8/2006 11:33Store 5Closed
1/8/2006 11:51Store 1Closed
1/8/2006 11:56Store 2Closed
1/8/2006 11:57Store 3Closed
1/8/2006 12:03Store 4Closed
1/8/2006 12:16Store 5Not Started
1/8/2006 12:23Store 1Closed
1/8/2006 12:28Store 2Closed
1/8/2006 12:57Store 3Closed
To this table
A B C
1/8/2006 9:45Store 15
1/8/2006 9:57Store 24
1/8/2006 10:05Store 33
1/8/2006 10:09Store 43
I have a spreadsheet that keeps track of document collection.
Column A is document name
Column B is department name
Column C-N represent quarters of the year. Ie 1st qtr 2012, 2nd qtr 2012 up to 4th qtr 2014
Conditional formatting changes the row to red if the last day of the qtr is less than today showing those documents as past due.
I mark the Cell "Good" if the documents received meet quality checks.
What I would like to do is:
Create a formula showing the present completion percentage by department.
The trouble I'm having is discounting the future cells that aren't applicable until they become past due.
I thought just counting the red cells and green cells but I can't get any of the conditional formatting counting codes to work for me. Tried pearson's CF vba and similar.
In one cell I can get the CFColorIndex to work and pull back the color index but in another cell trying same syntax trying to get the color index of a different cell I get #Value. CountCFColorIndex I just get #Value no matter what I try.
Can I count blank cells in a range if the Qtr ending date is less than today?
Would I have to have a multiple if formula to capture each qtr?
I have some code that loads and unloads a command under the "DATA" menu bar. Is it possible to add an item to the menu bar and put the commands under the new menu item? For example: Add "XYZ Consulting" as a menu bar item so it would like as follows: File, Edit, Insert, Format, Tools, Data, Window, XYZ Consulting.
View 5 Replies View RelatedI am importing some external data and I need to move/modify columns using VB. My spreadsheet looks like this: ....
View 9 Replies View RelatedI have a code here, which thanks to Jindon helps on yesterday, But now, I want to change the code to count by column instead of rows, to continuous copy & paste my date,
How can it to modify:
Sub test()
Dim home As Worksheet
Dim Filename As String, myDir As String, fn As String
Set home = ThisWorkbook.ActiveSheet
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
If .Show = -1 Then
Filename = .SelectedItems(1)
myDir = Left$(Filename, InStrRev(Filename, ""))
fn = Mid$(Filename, InStrRev(Filename, "") + 1)
With home.Cells(Rows.Count, "E").End(xlUp)(2).Resize(2)
.Formula = "='" & myDir & "[" & fn & "]MAN_SUM'!k6"
.Value = .Value
End With
End If
End With
End Sub
Find below a vba code that i attached to a button that converts my print area in excel to PDF via Cute PDF writer.
Sub PDF_Sheet()
Dim Filename As String
With ActiveSheet
Filename = .Range("A1")
.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True
SendKeys Filename & "{ENTER}", False
End With
End Sub
I need a VBA code for the Excel sheet that i am cuttently working in that will do the following:
i have text and pictures in range O86:W97 that must be automaticaly romoved & replaced by another range after the above mentioned code hase complete
the range that must be replaced with the following,
O101:w112 Pdf code must run then
O113:w124 pdf code must run then
O125:w136 pdf code must run then
O137:w148 pdf code must run then
O149:w160 pdf code must run then
note that the ranges run in a sequence 101to112 then 113to124 then 125to136 and so on
Now this needs to be repeated 190 time starting from range O101:w112. The range O86:w97 forms part of my print area of A1:W97, this means i will have 190 PDF saved pdf sheets when i the vba code is complete.
I have one excel file with three sheets marked 1,2 & 4
Sheet 1. is the place we add the description and the member number. When a member number is entered it gets the member name from sheet 4.
Sheet 2 is the page I need to change. Currently we can enter any score number in each box between 1 to 5. If we try to enter other numbers we get an error message.
I need to change the form so we can enter 1 to 10. Any other number needs to generate the error message.
I use the code below to automatically update formulas with the most recent data added daily to my spreadsheets. Can I copy this module, edit it so that instead of finding the last_cell_value it finds the value one_week_ago, and apply it to the same spreadsheet that the Last_cell_value module is used?
View 9 Replies View RelatedI am facing a problem with programmatically Add or Modify the Validation of cells...
The sample code in the Excel 2003 VB Reference is:
I've (almost) got a macro that modifies & saves a file. I don't want to post it yet because I need to clean it up and I've got a ton of REM'd out statements that I need to flush before I go public....
How can I modify it to open each file in the folder? I'm looking at several thousand files that need to be changed and put away in the correct folder.
(and I say almost because I had it and accidently ran it on my personal workbook and it deleted itself....
I need to run two macro when I modify a value in a cell
Here is the code for the macros:
modify this code to save as .csv? I tried just putting it after the file name and that didn't work.
View 2 Replies View Related