I 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'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 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
Im 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 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 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 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 RelatedI am trying to figure out how to go down a list of words and copy/paste a row if it equals a specific ring of words. For example, if want to go down a column and copy the word "Dog" if the word is "Dog" and paste it to the second sheet in a certain column... The thing that I can't figure out is how to step down to the next row until the cell is empty.
Sub Cats_and_Dogs()
For i = 1 To 100
If Range("i, 1") = "Dog" Then
Range("i,1").Select
Selection.Copy
Sheet2.Paste
End If
Next
End Sub
If Cell equals text than take networkdays and subtract by a number
If cell c = RD then take cell m (is a date 12/3) and add that by network days +18. This should tell me how many days to process this file.
is there anyway I can do a conditional formatting of something like that to color a cell if the date in the cell is a friday?
Dates will appear in a column like this:
16-mar
17-mar
20-mar (this is a friday, it should be red)
21-mar
21-mar
I have set up a rollover hyperlink so that when I hover over the cell it changes colour and creates a good effect. To do this I have another cell on the sheet that turns to 1 when the cell in question is hovered over.
I need a code so that when that cell displays the number 1. the object (which is rectangle 2) changes from white fill. to a picture I have saved on my desktop.
I cant quite figure out the coding and have been messing about with things like..
if cell range e.g a1 = 1 then rectangle 2 .userpicture " path " else .solid etc.
If cell a1 equals todays date add cell b2s value to it and put the new date back into cell a1.
View 6 Replies View Related