Zero Amount If Cell C14 Is Blank AND Add Cells
I need a formula that will give a zero amount if cell C14 is blank AND add cells C14 & C13 if cell C14 & C13 are not blank AND add Cells C14 & C12 if Cell C13 is blank but cell C14 is not.
I tried this formula but it will not work: ....
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do: I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
View Replies!
View Related
Add Multiple Sets Of Numbers Between Blank Cells
My spreadsheet is multiple rows and columns of sales data with two blank rows between salespeople. Ex: $100 ABC Inc. John #101 $150 XYZ Inc. John #101 $200 Golf Inc. Sue #102 $150 BBall Inc. Sue #102 What I need is this: $100 ABC Inc. John #101 $150 XYZ Inc. John #101 $250 $200 Golf Inc. Sue #102 $150 BBall Inc. Sue #102 $350 Where the sum of each salesperson's sales figures is shown and formatted. I've been doing this manually. I'm having difficulty figuring out how to do this using VBA. I can do the formatting, and conceptually I see what needs to be done.
View Replies!
View Related
Add Blank Cells For People Not On Each Years List So All Names Align
I have 10 columns that are as follows. Name, rank 04, name, rank 05, name, rank 06, name, rank 07, name, rank 08. The longest name and rank list is over 1200 names long. What I need to do is create 1, 3, and 5 year averages based on rank. My question is, is there a way to sort alphabetically that will automatically align column a,c,e,g,and i alphabetically that will also align each row by name. example: name 04 05 06 joe 75 72 paul 82 carl 72 48 Joe isn't present in 05, Paul isn't present in 04 or 06, and Carl isn't present in 06. Basically I need excel to add blank cells for people not on each years list so all names align.
View Replies!
View Related
Add Amount In Column
Trying to add a formula in my spreadsheet that will add every amount in column AQ8:AQ200 if the date in AQ8:AQ200 is march. The format for the date is 3-12-09. Here is what I'm trying. =SUMIF(AU8:AU200,MONTH=3,AQ8:AQ200)
View Replies!
View Related
IF Statement- To Remain Blank Until An Amount Is Entered In That Row
Amount Amount Item Total Account $ - No Profit$ 629.50 $ - $ 629.50 No Profit$ 46.29 $ - $ 46.29 No Profit$ 2,333.01 $ 233.30 $ 2,566.31 233.3 $ - No Profit $ - No Profit $ - No Profit $ - No Profit #1 If there is an amount in column B, I want column D to reflect that amount. #2 If there is no amount in column B, I want column D to state the words "No Profit." #3 Already in column B is the formula =C1-A1. I tried =IF(B1=0,"No Profit",B1) but when I copy that formula down the spreadsheet, it inserts "No Profit" into all of column D. I only want "No Profit" to exist when there is a $0 resulting from the formula mentioned in point #3. When there is a blank cell in column D, I want it to remain blank until an amount is entered in that row?
View Replies!
View Related
Find Blank Cell In Column To Add Data
I need to write entries into an open spreadsheet with data input on a userform. i need to use the xlup facility to find the last used row in the spreadsheet, select the next line and then enter the data, but how to return to VBA the actual cell reference that has been selected after doing the xlup and down one row. I need to be able a total potential 31 rows of data from the userform.
View Replies!
View Related
FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View Replies!
View Related
Formula To Add Amount Of Rows In A Column
I know a bit about excel nut not a hugh amount! i need to know a formula that adds the number of rows used in a column. Does that make sense?? ok NAME rob dave jim darren adam lee i need a formula that will tell me there is 6 rows used in that column (not including the title).
View Replies!
View Related
How To Add Values For Amount That Are Higher Than Same Row In Another Column
I have two columns (let's say column A and C), i would like to add the values in column A only if they are higher than the ones in Column C in the same row... how could i do that? The sheet looks something like this, i would like to add the values in the first column only if they are higher than the ones in the third column and then of course get the sum of them... i would also like to know how many were there in total, but i guess i could do that with a count formula i just need the parameters $ 67,100.00 $ 110,000.00 $ 165,100.00 $ 146,034.00 $ 239,400.00 $ 220,100.00 $ 90,184.69 $ 147,843.75 $ 168,500.00 $ 51,011.25 $ 83,625.00 $ 95,100.00 $ 89,860.32 $ 147,312.00 $ 198,200.00 $ 159,121.36 $ 260,854.69 $ 26,000.00 $ 129,941.44 $ 213,018.75 $ 235,700.00 $ 88,869.38 $ 145,687.50 $ 95,600.00 $ 27,772.54 $ 45,528.75 $ 43,000.00
View Replies!
View Related
Re-Calc From New Cell If So Many Cells Are Blank
I have 2 columns. Column E has the amount of time each day. Column G is where I want the Sum to be displayed. I need Column G to do a =Sum(E4:E33), but if anywhere in between E4 and E33 there is a period of 5 days where it totals 0 then I want column G to re-add from the first day where there is a amount in it. Ex: E4 to E10 totals 7 (1 per day), from E11 to E15 totals 0, from E16 to E33 totals 18 (1 per day again). In Column G at G10 it should total 7, at G11 it should be 6, G12 5 etc until it hits G15 where it sees the 5 previous days nothing was entered so it enters a value of 0, because G15 is set to 0, G16 will start adding from E16 till E33, until somewhere down the line it sees another period of 5 days where it adds up to 0 again.
View Replies!
View Related
Count Cells Until First Non-blank Cell
So what I have is an unknown number of rows, starting at row 2. The first row (row 2) contains information from A2:M2 but all rows after this in column M will be blank up to a particular row. What I want to do, is count how many rows are blank in the column M up to the last blank cell and fill this with a specific word. I have attached a basic layout workbook to show what I want. There is a comment on cell M3.
View Replies!
View Related
Copy Cell Above Into Blank Cells
The attached picture shows the problem. I want to automatically fill the blank cells in column A with the value immediatey above them. I want to do the same for column B and C. I have never worked with VB except to copy key strokes.
View Replies!
View Related
Fill Blank Cells From Cell Above
I am trying to fill down data in columns A:C but only if there is data in column D. I have butchered VBA into this working bit but the fill down stops too early. Sub fillall() Set x = Worksheets("Paste"). Range("A3", "c3") Do Range(x, x.End(xlDown).Offset(-1, 0)).filldown Set x = Range(x.End(xlDown), x.End(xlDown).Offset(0, 2)) Loop Until x.End(xlDown).Offset(0, 3) = 0 End Sub
View Replies!
View Related
Amount Of Cells In A Range
I need to come up with ways to work around this. Say I have a merged area of A1:A*, what I would like to know is how many columns are actually used in this merged area. A1:A4 would return 4, A1:A8 would return 8, A1:C8 would return 8 et cetera. Now I've played a bit with MergeArea.Address and although it returns me the correct range, I'm stuck with actually using it further.
View Replies!
View Related
Leave Other Cells Blank If No Data In Cell
I have 2 similar question. I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2) The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5. If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10. I think it has to do with using an IF statement followed with ""? Am I on the right track? Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0? I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
View Replies!
View Related
Count The Amount Of Cells In A Column
I am trying to count the amount of cells in a column (Col H) containing data against a certain date which is displayed in another column (Col B) Eg. Col B................-...........Col H 13/06/09.................FIRST LAST 13/06/09................. 13/06/09.................FIRST LAST 20/06/09.................FIRST LAST 20/06/09................. So that in the sheet next to it it would appear: 13/06/09 - 2 20/06/09 - 1 The dates don't appear in row order and the sheet has a filter on it so I am unable to select the Col H and just use COUNTA
View Replies!
View Related
Insert Cell To Columns Having X Blank Cells In Row
I need to put an extra "spacer" cell in each row that contains either 8 or 9 blank spacer cells in columns A through H or A through I, respectively, so that everything in those rows shifts to the right by one column. The problem is that all the other rows don't need any extra spacer cells. There is no pattern to the rows that need the extra spacer cell (such as every 5th row, or something).
View Replies!
View Related
Format Cell Color Based On Others Cells Not 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
View Replies!
View Related
Fill In Blank Cells Referencing Non-Blank Cells Above
I have sporadic cell values in a column. I will be describing the lamen logic I use to fill in these blanks manually. Find the first nonblank cell in the column, then look for the next nonblank cell in the column. I count the number of blank cells in between. If even # of blank cells, then I give the first half of the blanks the value of the first nonblank cell and the second half of the blank cells the value of the next nonblanck. If odd # of blank cells, then I do the same with the exception that the odd cell that falls in the middle will be randomly designated the value of the either first or second nonblank cell.
View Replies!
View Related
Imported Data Creates Blank Cells That Aren't Really Blank
Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting to count nonblanks. However, the data was imported from Access and Oracle, and Excel treats what appear to be blank cells as nonblanks. I've tested this theory by highlighting a couple of "blank" cells and deleting them, and my count changes. So, can I get Excel to put a value into my "blank" cells, so then I could filter it out, or create a formula that would only count dates in my columns (which is what I'm after). This is what I'm looking at: A B C 1 2/4/2006 2/6/2006 ("blank") 2 ("blank") 12/13/2005 1/7/2006 3 2/20/2006 1/15/2006 ("blank") In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A, 3 for B and 1 for C.
View Replies!
View Related
Adds Up Various Cells Then Add The Same Cell
I have a spreadsheet for my local camera club that needs a few "IF" formulas that I have not a clue at how to accomplish. I have done the rest of the spreadsheet. The formula needs to be IF cell A1 = 22 or 23 or 24 (a manually entered number) then the answer = 1 IF cell A1 = 25 or 26 or 27 (a manually entered number) then the answer = 2 Also need another IF formula that adds up various cells: A1 + A2 + A3 + A4 and then adds the same cells on a 2nd and 3rd worksheet, and if the total is zero, then the cell will remain blank in the answer field, rather than having a zero in it.
View Replies!
View Related
Cell With VLOOKUP Needs To Add Two Cells
I have a cell with the VLOOKUP function. I know that VLOOKUP searches down Column A until it finds what you told it to find, then returns the value of column you specify. The problem I am having is I will have the "item searched" listed more than one time and will need the sums of that item totaled. My formula looks like this: =VLOOKUP(A63,March!$A$9:$G$42,7,FALSE) For example, lets say that "Apples" are in A63. On the 4th of March I sell 4 apples, and on the 18th of March I sell 5 apples. I need the total in the formulated cell to read "9".
View Replies!
View Related
How To Add Two Text Cells Into One Cell
I tried to find this in the help section but was unable to locate it. Could you tell me how to add a name in one cell plus the name in another cell into one cell with the "&" sign between? So it would read "Don & Rose" in the final cell making the structure similar to: Column A Column B Row 1 Don Don & Rose Row 2 Rose
View Replies!
View Related
Returning A Value If The Dates/times In Two Cells Are Between X Amount Of Hours
I have a spreadsheet used for calculating information based on the dates specific shifts are requested/cancelled by our clients. I have a formula for working out if a date & time of cancellation is less than 48hrs notice of the shift starting. This is because we have cancellation fees based on this. What I have is this formula: =IF(A16="","",IF(INT(A16)-INT(G16)<2,1,"")) that returns a 1 if that shift is cancelled within 48hrs notice. This works fine but I have to now change the notice periods to the following: 72hrs+ - return 1 48hrs-72hrs - return 2 13.5hrs-48hrs - return 3 0-13.5hrs - return 4 edit this formula to take this into account? I figure it's using multiple IF's and changing the <2 into something else like the number of hours but I'm not sure of the exact syntax.
View Replies!
View Related
Macro To Add 2 Blanks Cells Below Cell With Words
REPLACE ENGINE OIL & FILTER, VEHICLE INSPECTION, FILL FLUIDS RESET THE OIL REPLACEMENT REMINDER LIGHT. (blank row ) (blank row ) E I have an excel file with many columns containing information at the end of each column like this. It lists car verbage, then 2 blank cells are required , followed by an E. I was wondering if a macro could be used to where it looks at a column, finds where the verbage ends, and automatically inserts 2 blank cells below it followed by an E. In other words it needs to make sure only 2 blank cells are after the verbage and an e should follow in the cell below. If the column has 3 blank cells below the verbage it should fix it and add the E at the appropriate space.
View Replies!
View Related
Add Multiple Lines From Cells To Cell Comment
I am trying to add multiple lines in comment. problem is the text to this mutiline comments is stored in a range Range("J15").Select Range("J15").AddComment Range("J15").Comment.Visible = False Range("J15").Comment.Text Text:="Line1" & Chr(10) & "Line2" (This is recorded). assuming that range "A1:A15" is where text for this comment is stored from a different VBA program.(15 number of line is the max i have programmed to accept). assuming there are 10 cells currenty full. how can i add this 10 text lines to a comment box.
View Replies!
View Related
Add Formula To Cell To Equal Product Of 2 Other Cells Via Macro Code
I'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors Worksheets("Output").Range("K14").Formula = "Worksheets("Calcs").Range("D17")*worksheets("Calcs").Range("D17").Offset(10+T,0)" When I record a macro it gives me this, but again, I need the last cell in terms of "T" ActiveCell.FormulaR1C1 = "=Calcs!R[3]C[-7]*Calcs!R[14]C[-7]"
View Replies!
View Related
|
TRACKER
