I have a round formula,
=round(D65-E65,4)
The issue with this formula is if D65 is 0...then the result is a negative number. Just for clarity,
D65=0, E65=10%.....
So I end up with -10%. I can't have a negative number so I'm looking if the result is negative then the cell just equals 0. Is this possible with an IF formula?
I have a column of numbers that after each sum there will be a subtotal. If the sum is a negative number then the new subtotal will be 0. Attached is a sample.
View 3 Replies View RelatedI have tried a range of "sumproduct" and "count if" and I am still not having any luck.
What I would like excel to do is to count what surgery/hospital (B)is referring for what product (F)
help.xlsx
I have data starting in E7. I want it to go down the column and find the negative numbers. If it finds one then I want it to change the number in the row to the left of it to a negative. So if E67 is a negative number, make D67 a negative and so forth down the line Sounds "simple" but how do I do it?
View 7 Replies View RelatedI need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
View 7 Replies View RelatedI have a sheet where there is a column showing all the totals of previous entries, however a couple of the cells shouldn't total but just show the last entry.
The cell is for an ongoing count of year to date applications received.
Can anyone tell me a formula that would look for the last data entered in a row and return that value
I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.
View 3 Replies View RelatedIm using this to loop through ID's for my code.
Sub Test2()
' Select cell A2, *first line of data*.
Range("A2").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub
How can I set a cell value = to what cell I the loop is currently on?
I have a formula =IF(D87=0,"",E87(D87-E87)/ABS(E87)) that returns a blank if D87 is Zero. if D87 and/or E87 is also Zero how would the formula look?
View 2 Replies View RelatedI have sheets that return their name with the following formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
All good, but on my summary sheet (that is summarising the data in each sheet) I want a cell to return me the name that each sheet is called, but simply saying ='SheetName'!B1 is not working
So how to I get the cell on my summary page to return me the text of the cell that is using the above formula.
I have created a file in which other users use on a daily basis to input their time in and time out hours per activity. There are 16 rows allowing them to input up to 16 activities completed in a day. More often than not they do not use all 16 rows. My issue is that these users are very computer illiterate and will often delete the zero that is in their time in or time out hours on unused rows. This results in their total time displaying "####" I have explained to each of them multiple times to input a zero and their problems will be corrected, but they can't seem to grasp that concept.
How to allow the users to input their times, but if they hit delete on a cell and the cell is blank for it to automatically populate the cell with a zero no matter what the user does. I have all other cells with formulas on the sheet locked, but if I lock these cells then they can't input their times.
I am familiar with the macro DeleteRowOnCell, but I want to delete rows when a cell in a selected column has a formulae which is returning empty text. I tried the macro DeleteRowsFastest without success.
View 2 Replies View RelatedIs it possible to run a macro from an excel cell formula?
ie.
=IF(A1=1, RUN MACRO, 0)
I have a 700 line spreadsheet and would like to delete the cell next to another cell if it contains a certain string. I.e if A1 to A3 equals "Test" then i would like to delete the contents of B1 to B3, But if A2 equals "Completed" then only B1 and B3 should be cleared.
View 4 Replies View RelatedSo I am having trouble figuring out how to do multiple text to equal multiple numbers. For instance right now I use =IF(E9="JOHN DOE","07", but I would like to have it where IF E9 = John Doe the cell shows 07, but IF the E9 = Sam Doe I want the cell to show 08, and the same IF E9 = Pat Doe the cell shows 09 and so on.
Meaning is there a way to have multiple functions go to a cell dependent on what is entered into E9 for example. I just want to have a number come out in one cell if a certain name is entered in a different cell. I loooked at this post [URL]....which got me started,
I am trying to insert a row every time a certain value appears in row.
Example attached.Example 1.xlsm
I need to copy the entire row that has the value 2 in column B and insert it in the line above (not fussy). It must be inserted and move all data in other rows accordingly.
I want to format cell A5 so as to where if I put in one of several keywords into it, cell A7 puts several letters (with spaces)
example A6:Normal = A7:AA AA AA Ive got about 10 select words so just one example would be awsome! 07 excel BTW
I am looking for a formula to allow an image file to show when a cell equals a certain text string. If it can be done can it be achieved with multiple images? I am making a database that will calculate from many variables my required glass sizes, given the size and window type and I was hoping to go one step further by making it raise a purchase order from a series of images and other related data.
View 14 Replies View RelatedIs there any way of playing a sound (or even better, speech) whenever a specific cell equals a certain value or contains a certain string (such as "Alarm")? This has to happen automatically as the cell updates and require no interaction.
View 3 Replies View RelatedI have a spreadsheet with a lot of account numbers. I want my Macro to highlight the row A:C if certain accounts are found in the spreadsheet.
For Example:
Account numbers "10998-0000" and "18999-0000" are allowed to be in the spreadsheet so don't want them highlighted. But Account "10110-0000" should be highlighted.
I know the Macro will be big but I was hopping something like:
If ActiveCell is not accounts .... I would then list all my account numbers... Then
and at the end a loop until ActiveCell is Empty.
I have this so far.
Range("A12").Select
MyColumn = 1
i = 12
Range("A" & i).Select
Do While ActiveCell.Value ""
[Code] ........
Is there a formula I can use to return a worksheet name. I want cell A1 on Sheet1 to = the name of Sheet2
View 4 Replies View RelatedI have a row range 5:20004. In that row range column R may have 'QLD' in the cell.
I am trying to delete all rows within that range that have QLD in column R or delete the row if R <> 'NSW' (would be a better way just in case there is something other than QLD)
to do this on opening the file automatically.
I would like to return a unique list of values in column b sheet2 IF the value in Data!A is equal to A1 on sheet2.
I tried doing if formulas and v lookup formals but they dont give a unique list or only include in instance of the associated value.
I have students Kg-5th Gr. I need to identify the ones over-aged when I enter the information. The following is what I'm trying to use but I get Y1 for ever cell. I need it to Identify the student as Y1 for One year overage and Y2 for tow years overage.
View 3 Replies View RelatedBasically, I have a table of data and I want to make a VBA coding so that Any row with "cell value = 0 in column C" will be deleted.
But I can't get my code working and i don't know why. There's no error pop up either.
I am looking to insert a row every time any cell in column A = R
This will happen around 50 times.
The inserted row will need to have an R in column A and the number 349273 in column B.
How to link an image to a cells value? The linked image is a range of cells and that range is A5:K30
I will take a macro if that is the only way.
linked images path = cell B2
B2 = ("P:path"&TEXT(TODAY(),"yyyy")&"_stuff"&TEXT(TODAY(),"mmm")&IF(OR(TEXT(TODAY(),"mmm")="Nov",
TEXT(TODAY(),"mmm")="Dec"),TEXT(TODAY(),"y")-1,TEXT(TODAY(),"y"))&""&TEXT(TODAY()-1,"ddmmmyy")&" stuff.xlsx")
I don't know what formula to use to do the following:
In Column E there are cells which contain numbers which fall between 0 and 400. What I am trying to do in column F is show the a letter (E, D, C, B, A, A*) if the number in column E fall between a range.
For example:
If column/row E2 is between 160 and 199 it equals E, if column/row E2 is between 200 and 239 it equals D, if coulmn/row E2 is between 240 and 279 it equals C etc etc....
I have six images in "Sheet3" I want a macro to copy and paste image one if cell A3 = 0. How do I know the "name" of each picture so I can change the image based on the value?
View 2 Replies View RelatedI need to enter a countif formula if a cell equals the name of the active worksheet and also cell A1. My worksheets are not always the same and I also want to be able to copy this formula to multiple worksheets so if the formula states active worksheet then is should work properly when I past it to multiple tabs (at least in my head)
View 9 Replies View Related
