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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
If Statement To Accumulate An Amount In A Cell
In a cell I have a description, say "FOOD" then another cell with amount say £2, then in another cell I want it to be looking and if it sees "FOOD" entered, it takes the amount £2 and adds it to a running balance. Cell A1 = Food Cell B1 = £2 Cell H1 = (looks and if Food) adds £2 to cell
View Replies!
View Related
Sorting Certain Columns But Ensuring Certain Rows Remain Fixed To Their 'master Row'
I have created a spreadsheet which is tallying up various data. I have a number of different categories e.g. A, B, C, D and within each one of these are sub categories. I then have a tally for the number of each category and within that the number of each sub category. I then want to sort the data so that first the main category is sorted in order of highest tally to lowest. the sub categories however need to stay with their primary category. I then need to sort each sub category within the main category. the data looks omething like this: category tally sub cat number A 6 A 3 B 4 C 6 B 9 A 1 B 6
View Replies!
View Related
Blank Display If Figures Not Yet Entered
I have this formula in a cell: =IF(AND($D4=H4,$F4=I4),3,IF(SIGN($D4-$F4)=SIGN(H4-I4),1,0)) but i need to somehow incorporate a formula that only shows this if a cell has a value present. I know as an example its something like: =IF(C2="","",C3) but i dont know how that would go in my existing formula as i dont know how to stack IF statements?? (a very kind person on this forum did the original formula btw) the cell that i need to have as the 'blank or not' is D4.
View Replies!
View Related
Sum If Values Entered If Not Leave Blank
I need a formula that will do the following: Sum K4 (unit price) and M4 (shipping) and return the answer in N4 (total), if K4 and M4 are empty then leave cell N4 blank All responses will be welcome as this has been driving me mad for over an hour, and the answer is probably so simple!
View Replies!
View Related
Fields That Containa Formula Need To Appear Blank Until Data Entered
i have two date fields....one date requested eg 02/06/09 (cell C10)...another date completed eg 03/06/09 (cell R10) a third field (cell S10) contains the formula: =IF(R10-C10=0,"less than a day",R10-C10) so if a request was actioned on the day then it shows as less than a day, otherwise will show how many days it took but when this formula is draggeddown all other cells show - less than a day how can i make these cells blank whilst still holding the formula? also - is there a quick formula to add to show only the amount of wrking days a request took to complete?
View Replies!
View Related
Match Amount And Colour Row
I am trying to match by amount with 1 column to another in a different sheet. If Data in column A in worksheet 1 matches data in column b worksheet 2 then color the entire row in column A worksheet 1 red, I am matching by amount so it(amount in column a) can be within 1 $ of amount in column b worksheet 2 also color item in column b(worsksheet2) red if matches with column a in worksheet 1 both can have a threshold of 1$ difference
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
Inserting A Total Amount Field In Each Empty Row
to run a macro that enters a new row after each set of currencies (which have been sorted). I now need to enter a total balance cell within this empty row which calculates the total amount for each currency. I need to do this using the SUMIF function below: SUMIF($J$3:$J$300,"L/C",$I$3:$I$300)-SUMIF($J$3:$J$300,"L/D",$I$3:$I$300) the above basically lets me calculate the total but as no - and + signs were input it does this by recognising the 'dr' and 'cr' in the column next to the amount.
View Replies!
View Related
Ignoring Blank Cells In IF Statement
I've got four columns of data. The first column contains pricing for a bunch of products from our company. The other three columns contain pricing for three other competitors. So for example: Product | Our Price | Comp1 Price | Comp2 Price | Comp3 Price Gloves | $4.59 | $5.00 |$6.00 | $3.56 Hats | $5.00 | | | $4.59 In column G right after Comp3 Price I have an IF statement that says if Comp1 Price is less than Comp2 Price, Comp3 Price, and Our Price, to print "Comp3" in that cell. If it is not less than the comparable data, then check Comp2 Price then Comp3 price in the same fashion. If Comp1, Comp2, or Comp3 is not less than Our Price then print "My Company Name." This will allow me to see who has the lowest price for that product and also tell me if that lowest price is lower than our price. My problem is that I can't get it to work out so Excel ignores blank cells. So for example, Comp3 has the lowest price amongst our competitors for hats and is also lower than ours. But when I check for Comp3 price being lower than Comp1 and Comp2, it comes back as negative because Excel sees those cells as zeroes even though they're blank. My formula is: =IF(AND(C2<D2,C2<E2,C2<B2),"COMP1",IF(AND(D2<E2,D2<C2,D2<B2),"COMP2",IF(AND(E2<C2,E2<D2,E2<B2),"COMP 3","MYCOMPANY"))) This would do exactly what I need Excel to do if it would ignore the blank cells. The problem is that Comp3 has pricing for hats because they offer hats, but comp1 and Comp2 don't offer hats at all, so naturally Comp3 has the lowest price; however, according to Excel Comp1 and Comp2 have the lowest price because the cells are blank and counted as zeroes. So, does anyone know how to work this out so Comp3 will be counted as the lowest price for hats, ignoring the blank cells of Comp1 and Comp2?
View Replies!
View Related
Making Contents Of Cell Blank Upon IF Statement
I have a conditional IF statement, where I want the content of the cell to be blank if the result of the IF statement is false, ie I want the content of the cell to be as if there were no formula in the cell (this if so that the formula COUNTA(Sheet1!$B:$B) only counts the cells where the result is TRUE). For example, IF(A1=1,1,"") where A1 1, the content of the cell will have something in it (a space), and won't be blank.
View Replies!
View Related
Links Updating When New Row Is Entered Into Source
I have a master price list that will have items added or removed from it occasionally. I have sixty workbooks that contain detailed data on mixes of multiple products from the master price list. All the corresponding prices throughout the sixty workbooks are linked to their specific slot on the master price list. The problem: if the sixty workbooks are closed and anyone adds a new row to the master price list, when I open any of the workbooks the values update but the referenced cell does not. So if the price for Product 25 is located in cell K118, and two products are added in above K118, when I open any of my mix sheets that contain Product 25 the linked cell should be to K120. But it's not doing it that way. It is holding on to the referenced K118 cell and updating the value to the wrong price. I've played around with the VLOOKUP function but haven't been able to make it work. Linking the cell as I have has worked great, if I could just get it to adjust the reference as new rows are added to the source workbook. I've figured out that it will update the workbooks when I add a row if all that are required are open when the addition is made. But my boss will not open sixty workbooks every time a change needs to be made to the master price list, which he controls.
View Replies!
View Related
Copy Formula From Previous Row When Data Is Entered
This is just a sample worksheet. I have got a worksheet with having 3 coloumns A, B & C. Column A contains E Code, while Column B is of time which user will enter. Column C contains the time in Hours. I have entered one record for example. Now, whenever user enters the value in B3, then formula from C2 should be copied to C3 i.e it should be =B3/60. I want this to be done using VBA. Pls help me out. I want to use this feature in one of my another files which requries this feature.
View Replies!
View Related
Toggle Button To Remain On/Off
I wanted to know how do I keep a toggle button in the on(ture) position after I close the userform? How do I keep the toggle button off(false) positoin after I close the userform? In order words, once I run my macro again, I want it to remain in memory that my toggle button was in the on(true) or off(false)? For example,I have an application that I am developing which involves some what-if analysis for some projects. There is a command button located on my worksheet. After the command button is clicked, I have include some toggle buttons on a userform for all of the various projects. If I assume that sine projects will not be used, I click the associated toggle buttons. This puts them in the on(true) position. After I close the userform and reclick the command button, my toggle buttons are all back in the off(false) position.
View Replies!
View Related
Make Comments Remain
Attached is a screen shot of a bit of a file I made to use daily. It needs to be used by many people and always starts out new each day. I'd like to make the comments on the right stay "full size" when the sheet is scrolled down (it needs to be able to scroll). The top 3 lines are "fixed" and don't scroll. I've tried comments ( currently ), pictures, imported pictures...everything I know to try. How can I make the comments box persist without "rolling up" when the remainder of the sheet is scrolled?
View Replies!
View Related
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
Pop Up Box To Remain On Worksheet
I have a number of sub routines that manipulate data in various ways to which I have assigned various shortcut keys (Ctrl + whatever) However as there are quite a few of these I wondered if it was possible to create some sort of pop up box in which I could list the shortcuts, the box being able to be opened and closed at the users request. I can of course do this with some sort of userform but when this is open I can't then work on the sheet. Also as this would presumably be opened from within some code, waiting for the user to click the close button then wouldn't this prevent the other sub's from executing.
View Replies!
View Related
How Can I Copy A Formula To New Rows So That Some Values Remain Constant?
I have used the following long but simple formula. =SUM((B17*B14)+(C17*C14)+(D17*D14)+(E17*E14)+(F17*F14)+(G17*G14)+(H17*H14)+(I17*I14)+(J17*J14)+(K17* K14)+(L17*L14)+(M17*M14)+(N17*N14)+(O17*O14)) I need to copy it to numerous other cells. However I need the row fourteen values to remain the same while the other values change according to which row I'm copying it to e.g =SUM((B20*B14)+(C20*C14)+(D20*D14)+(E20*E14)+(F20*F14)+(G20*G14)+(H20*H14)+(I20*I14)+(J20*J14)+(K20* K14)+(L20*L14)+(M20*M14)+(N20*N14)+(O20*O14)) Is there any way of doing this without changing the formula manually every time?
View Replies!
View Related
IF Statement With Conditions: Start From Row 21
In the table I've created, I have a column (column A) that contains "Y" for the first 20 rows. I'd like to use an if statement in another column (column B) to create a numbered list starting with the first row (from column A) that is blank. For example, if column A contains "Y" in the first 20 rows, I'd like starting in row 21 (in column B) to return a value of "1" and then have the subsequent rows increase in value by 1, so row 22 would be "2", row 23 would be "3" etc. The catch is, if for some reason I make a change in column, let's say extend the "Y"s down to row 25, I'd like column B to reflect those changes by starting the list in row 26.
View Replies!
View Related
Custom Floating Toolbar Created - Icon And Size Questions Remain
I have created a custom floating toolbar that works perfect (thanks to all the reading on here!). My workbook is a stand alone estimating tool that many users will have a copy of. I have two things I want to do yet but I cant find it on here. 1) My buttons on my toolbar - I want to have custom icons. I can do this temporarily but when I exit my worksheet and re-open they are lost. Is there anyway to save a custom icon. I am willing to add it to the icon list if necessary and then when users open the workbook copy the custom icons onto their computer. But I am not sure how/where I could do this. Worse case, can I color my button text? 2) When my toolbar appears, it is wide, all buttons are going left to right. I want the size to be small width so the toolbar buttons are on top of each other. Any thoughts? I tried adding a .width but it didnt work.
View Replies!
View Related
Elseif Statement Find Next Empty Row
I have created a user form with a combo box and three text boxes. The drop down box is populated via the initialization of the form; code is posted below. the "add item" names are names that are already on the worksheet in column 1. What i have done is used the command button "submit" to populate the information from the textboxes to the worksheet. what i would ike to do is populate the empty cells below this area with the information that is in all three text boxes and the combo box. for the IF / elseif statments i have used the combo box as the condition. So the quandry is I cant input the information into the empty cells, i have tried a few things, my vba is very basic, as you can tell ihavent even DIM'd anything (i think that is another thread though :D ) here is the initialize code and below is the "submit button" code. Private Sub UserForm_Initialize() 'sets values for text boxes cbomaterialdiscription.Value = ""..........
View Replies!
View Related
Copy Row From Sheet 1 To Sheet 2 When Data Entered In Cell F
I have Worksheet 1, with columns A to E. I would like a row to be copied to Worksheet 2, as soon as cell F in Worksheet 1 is populated. Also the row to be deleted from Worksheet 1. So, as soon as F1 in worksheet 1 is populated and enter button pressed, row A1:F1 will be copied to the next empty row in worksheet 2, while being deleted from worksheet 1. So eventually all rows in worksheet 1 will be deleted and rows in worksheet 2 will be populated.
View Replies!
View Related
Insert New Row With String Based On Conditional Statement
I've been working on this project to propagate certain products through multiple categories. I chose to use excel to assign multiple categories to each product. On sheet1 I have setup products (column a) and qualifiers in the following columns (color, model, etc). What I would like to achieve is for the user to select yes or no for each category column and and if yes then have the corresponding category breadcrumb string (from sheet2) inserted into one specific column in sheet3. As the user continues to select multiple "yes" from the category columns for that single product, those additional category breadcrumbs get inserted at the end of the list in sheet3.
View Replies!
View Related
Next Blank Row
I created a form in Excel that gets filled out, then you click a macro button that copies all the data from the form into a row (say row "1") in another worksheet (called "Records") and clears the form so you can enter new information in the form and (ideally) click the macro button again, and have the new information fill in the row below (row "2") in the "Records" worksheet, with this trend continuing. Problem is, I've never used Visual Basic and can't figure out how to have the macro run through the same exact process, just paste the new information in the next blank row. I pasted my code below (it's probably very long-winded and unnecessary, I just don't know a better way to do it), but if anyone knows how to make it run the same exact process, just paste in the next blank row in the "Records" worksheet, I'd be very appreciative. If you could write the code out, that would be a huge help, as I don't know anything about codes. AM from Colorado ' Macro3 Macro ' copies entire PO sheet to Records tab ' ' Sheets("PO").Select Range("L4").Select Application.CutCopyMode = False Selection.Copy Sheets("Records").Select ActiveSheet.Paste Sheets("PO").Select
View Replies!
View Related
Copy To Next Available/Blank Row
I have an Excel workbook which uses the following code to copy the filtered row into a new sheet. as you can see the following code, clears the worksheet, how can I change the following code so if the first row is not empty in the sheet2, it copies the text to the second row and so on. Dim rng As Range Dim rng2 As Range With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(0, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) ...............
View Replies!
View Related
Scrolling To Next Blank Row
[code/]Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'This macro scrolls the Report to TopLeft when you click 'anywhere in the Report columns If Target.Column > 16 And Target.Column < 22 Then With ActiveWindow .ScrollRow = 1 .ScrollColumn = 15 End With End If If Target.Column >= 1 And Target.Column < 15 Then With ActiveWindow .ScrollRow = 1 .ScrollColumn = 1 End With End If End Sub[code/] ...but I cant figure out how to scroll it down the list to the NextBlankRow-20 such that the previous 20 lines of data are shown to the top of the screen.
View Replies!
View Related
First Blank Cell In A Row
What is the code to find the first blank cell in a row? I can find it for a column, but have not been able to successfully alter the code to find it in a row. My rows are going to be 2, 3, 5, 6, 8, 9, 11, 12, 14, 15 but the column will change daily.
View Replies!
View Related
|
TRACKER
