I am trying to do this preferably not in a macro if possible. Basically I have a monthly metrics spreadsheet that pulls it's values from a data entry sheet. I setup the display sheet so that if there's no data for the month the cell is blank in order to avoid errors. So, now what I want is one cell that will update and tell me if the last month's metric was good or bad. So, I am trying to create a cell formula that will be able to find the last row with data. In this case it would be the row corresponding to September's data, then evaluate the value in that cell to see if it's above or below 90% = 0.90 in this case.
Ok, this should be an easy one. I have a formula that one of the functions needs to check if certain cells are blank.
For example; I want to check if A2, B2 and C2 are blank.
Currently, the only thing that seems to work is,=IF(OR(ISBLANNK(A2),ISBLANK(B2),ISBLANK(C2)),"PASS","FAIL")
This is just a piece part of the entire formula and I have to evaluate the data based on multiple criteria. The ISBLANK() portion has to be able to evaluate at least 15 cells. Is there a way to evaluate the cells all at once instead of typing out ISBLANK() over and over. I have tried everything I can think of in regards to syntax.
I have a ss that has item descriptions, quantities and pricing.
Item descriptions are identified by a letter (a, b, c, etc) and in cells C20:c32. These are selected by drop down box. Item quantities are in cells E20:E32.
I want to evaluate cells C20:C32 and determine what letter is chosen. If A is selected in any cell C20:c32 I want to count the quantities for A in cells E20:E32. I can't quite figure out how to do this.
I have been given a project to develop a spreadsheet using excel that will take data that is for a whole month, and evaluate by DAY to see if the same doctor was listed as attending two patients in surgery at the same time. If the Doctor's name is the same for two patients within the same timewindow for a certain day, a code is applied to EACH RECORD that the doctor was attending in the code field. If there was NO DUPLICATE patient, a different code is applied in the code field.
The tricky thing about this is: 1) Number of records will vary month-to-month 2) There could be 1-xx patient records where the same doctor is listed and there is overlapping time in the SAME DAY OF THE MONTH. 3) The code applied to multiple patients in the code field applies even if the minutes of overlap = 1 minute. 4) Start/End times and length of the patient visits will vary. (Example - Dr. Jones has two patients on the 15th of the month. The start time for patient 1 is 9:00am and ends at 10:15am. The second patient's start time is 9:30am and end time of 11:00am. Under this situation, the multiple patient code would apply)
Does anyone have any experience evaluating by date, then within a time-window to check for record overlap (doctor name in this case)? There could be 200-1500 records per month, and 100/day to evaluate for overlapping.
My company uses financial analysis software to evaluate data on a monthly basis. The software has a report writing package that is similar (but not exactly like) Excel. There are 2 functions of this report writer that I would like to find out if
Assume A1=4 and B1=5 (but the data may vary in the future). In C1, I want an green up arrow if B1>A1. If B1
The 1st set is in a column. Nice & tidy all twelve characters in length & alpanumeric
zx0123123123 zz0123123124 zy0123123129
The second set is a mess, taken from a lotus notes inbox from the subject line off an email, eg. there Blah blah zz0123123124 etc etc. What I want to do is do a reconcilliation between the two with a lookup, but have no idea which route to go down to get round the messy data. I have been using =IF(ISNA(MATCH(A1,'[spreadsheet to lookup to.xls]sheet1'!$A:$A,0))," No match"," Match") but here the data is clean. I have searched teh forum ;o) DaveH & found something about > Instr() but this can't be used in formulas..
I am looking to Find and Replace (via ctr-F) but I need to replace it only within a range of selected cells. Each time I try to do this it replaces everything even though I have tried selecting each cell and replacing only that. Is there a way to select a certain amount of cells and replace?
One of my datasheets by necessity ends up as about 25,000 rows long, but only about 40 of those rows have data in. The rest of the rows are empty.
If a row has data in, there will be data in every column on that row, for example if row 2 was not empty, there would be data in A, B, C . . . and all the way to the last column without missing cells along the row.
I need to copy those 40 rows without blank rows in between. At the moment, I have a macro that deletes blank rows, but it takes hours to run.
This is the code that's being used at the moment:
Sub DeleteBlankARows() Dim r As Long For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 If Cells(r, 1) = "" Then Rows(r).Delete Next r End Sub
I have a spreadsheet where a date is entered in column A with data in B-F. I am trying to write code to look in column A for a specific date and enter additional data into columns G-L on the same row. It needs to be able to skip the rows that are blank. So I have the dates of 12/1, 12/2 and 12/3 in column A rows 1,2 and 3 and I want to enter data for 12/2 on row 2 skipping the blank cells in row 1 for 12/1. Here is the code I have below.
VB:
Private Sub CBSecure_Click() Dim my_name As String Dim r As Variant Dim l As Long sFind = DockDoorCal.Value If Trim(sFind) = "" Then Exit Sub
I have a report that I pull that I pull fairly often that is in this format and shows which footage of products each store is getting (out of over 1800 stores)!
Above is the format that it comes back as. And here is a spreadsheet that shows what steps I take to find correct values in detail.
So each FTG has an ID# and Desc. The ID is in text format and each ID is seperated with a comma, no space. Description also. Date is seperate by a space and comma. Stores change footages a lot so I want to find out which footage is effective today (1/27/2014) For ex: Store 63 would have the 5ft effective right now because we are between 5/23/08 and 5/22/14. So each ID# and Ftg Description is in the same order as Date.
When I have hundreds of stores, it is difficult to go through and get each one (even with the way I've been doing it.) But my ultimate goal is to create a macro to put only the current footage ID#, Desc, and Date in Columns C,D,and E. I'm not sure even where to start with doing a macro.
I usually just find the store with most ftgs, count them, insert that many rows after ID and Description columns, then do a text to columns (comma, delimited), sort by 2nd date column (so they all come up top) and then manually go through them and delete unneeded columns once I have them all.
I’m keeping tabs of some clay pigeon shooting scores. I go to alternate locations each week and normally shoot 100 clays, however sometimes it’s only 50. I’ve used MAX to find the highest score in Column D of a spreadsheet and it did what I required. However I now wish to find the highest number in Column D - but only include rows if Column C = P and column E =100.
Col B Col C Col D Col E Col F date Location score out of % hit
In other words I want to find the highest score for location P but only if that week it was out of 100 shots. It would return 66. Then I can do the same formula for location A and it would return 62. I can’t see how to do this and have searched the forum to no avail. It doesn’t look like I can just use the MAX anymore and I’ve tried incorporating that into a (nested) IF but unsuccessfully.
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
I cant figure out why this "evaluate" function is not working as I expected. I have number/text from $A$1 to $A$5 to try and learn how to use this function but have not had any luck. I need to learn how to use it properly so I can use a for loop to change multiple check boxes on a user form that I am trying to make.
I need a way for tell Excel to NOT evaluate a formula. Basically, I want Excel to leave the value being displayed in the cell alone. The formula in the cell is a function that I have implemented in C++ and registered with Excel through the Register call.
I want to do it evaluate each row and if the last value for that row is X, then highlight that cell and the Server name for that row.
I have used conditional formatting but it seems that I cannot specify an option to look at the last cell in a row, evaluate that cell's value and then apply the formatting. I can only have conditional formatting highlight every cell in that row that matches the condition.
I only want the last cell, which represents the most current data, to be highlighted, as well as the category name, if the condition is met.
I have two sets of information, on one hand I have telephone numbers and in the other set I have prefixes and countries. My goal is to tell to which country each number belongs:
i.e.
Numbers Prefix Country 4476324125 44 UK 3354326544 33 France 9713425432 971 UAE 9143253245 91 India 1343543253 1 USA 4432412412 4924241214 7431242424
So I would need to add a column next to "Numbers" saying to which country each number belongs.
My list has a few thousand numbers and a couple of hundred prefixes. I tried with some array formula, but cannot make it work.
I have to run an Evaluate function which is linked to an Array formula, but when I try just to put =ev(), the Array Formula which is located in another cell, does not run properly.
Im looking for a formula that will look at the the products in column A and see if their coresponding value in in Column I is equal to "website"
I would like it to count how many website sales the product has and return the product name in order of number of website sales into the top ten list at the bottom.
I have attached a test file which should clear things up
I have a problem in getting VLOOKUP to evaluate consistently. It is best understood from the screenshot linked (Office 2010), where cells B3 and B4 are not showing the expected values. I wanted to upload the excel file, but cannot see the option to do it.
The screenshot is at Book2 | Flickr - Photo Sharing!
This is just a small segment of the big excel sheet.
If I copy "mysum" contents to a cell and replace "lista" with a defined list, it works just fine. But whenever I run this code, it gives me syntax error.
Sub aaa()Dim i As Integer, myvar As Variant, tester As VariantDim mysum As Variant, lista As Variant, alpha As Datelista = Sheet2.[D6].ValueWith Worksheets(Range("A10").Value) Do i = i + 1mysum = Evaluate("SUMPRODUCT(((ISNUMBER(MATCH($B$8:$B$10007," & lista & ",0))) *($A$8:$A$10007>(TODAY()-180))*(($E$8:$E$10007)+($F$8:$F$10007)+($G$8:$G$10007)+($I$8:$I$10007)+($K$8:$K$10007))))) myvar = Evaluate("=TIME(10,0,0)>" & mysum) Loop Until myvarEnd Withtester = 35 - Sheet2.[c10] alpha = mysumMsgBox "VALID for [" & lista & "] after " & i & " Day(s). hours in last 180 days after " & i & " Day(s) will be (" & alpha & ")"End Sub
However when I change this to a dynamic range I keep getting a syntax error. Can anyone see where I have gone wrong? I know its probably only a " or an ) out of place but its driving me mad!!!
I have 2 sheets (book1.xls and book2.xls) book1 has over 20 sheets and has my primary data. Book2 is for a report. The report I want to only display one sheet at a time, by me typing in which sheet to access.
What I want to have: A2 = Sheet Name C2 = ="'C:Excelfiles[book1.xls]"&$A$2&"'!$B$1" C2 should have the value from B1 on 'Sheet Name' from the file book1.xls
however, all C2 will display is the text, I cannot get it to actually do the reference. (appears as 'C:Excelfiles[book1.xls]Sheet Name'!$B$1 )
Is there a function to say, evaluate data output from another cell? (less complicated example) A1=1 A2=2 A3=A1+A2 A4=3 (formula to evaluate output of A3)
