Formula Not Working On Large Number Of Records (500,000) - 2007
Nov 2, 2009
I am trying to use a simple formula: COUNTIF($CT$2:$CT:$430749,CT2)
I am trying to fill this down all the rows (430,749 rows). The formula behaves as its supposed to up to around row 650. After this point, all resulting values are all the same, which happens to be the same value that was returned from the original formula in C2. However, this is not correct. It's like the formula just stops working after row ~650. I tried F9 without success.
I'm trying to validate a cell in Excel 2007 that should contain only two letters.
Formula: Code: =IF(AND(LEN(A1)=2,ISTEXT(A1)),TRUE,FALSE) When the value of A1 is JK, the result is TRUE When the value of A1 is 12, the result is FALSE
When the value of A1 is 3K, the result is TRUE When the value of A1 is K3, the result is TRUE
When the value of A1 is 123, the result is FALSE When the value of A1 is JKL, the result is FALSE
When the value of A1 is 3, the result is FALSE When the value of A1 is K, the result is FALSE
The LEN function works as expected, but the ISTEXT function does not, whenever the cell contains a number and a letter. I've tested the LEN and ISTEXT functions separately, and get the same results.
I have a large workbook in which I do some data analisys. One of the analysis I have to do is to figure out the top 20 oldest items. Therefore, I have the following formula in T55:
hen I need to match that number, say 534, to the person who has that old item related to them, so I have this formula on u55:
=INDEX(OpenTickets!A:A,MATCH(T55,OpenTickets!$N:$N,FALSE)) and so on
this worked great for a while, but I just came up with an issue. I have 2 items that are 534 days old so the formulas =LARGE(OpenTickets!N:N,1) and =LARGE(OpenTickets!N:N,2) correctly returned 534 as the 1 and 2 oldest items.
The problem came when the formula
=INDEX(OpenTickets!A:A,MATCH(T55,OpenTickets!$N:$N,FALSE)) and =INDEX(OpenTickets!A:A,MATCH(T56,OpenTickets!$N:$N,FALSE))
Matched the same employee name. The issue is that the formula looks only at the first instance where 534 occurs and then matches the name.
Instead I need to match both 534 to each employee name whose name appear next to 534. In other words my master workbook (Open Tickets) looks like this and must return:
simplifying a formula which gathers data from about 50 worksheets from within the same work book.
The data to be gathered is in the same cell on each worksheet and is simply a number but i want the SUM of theses numbers carried forward to another worksheet. Each worksheet is named by date i.e. sheet 1 is named "16 June 2014" and sheet 2 is named "23 June 2014" and so on until "30 March 2015" (Each sheet represents one full week Monday - Sunday).
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range) If target.Column = 1 Then ThisRow = target.Row startRow = 1 i = 1 Set ws = ActiveSheet maxRow = Cells.SpecialCells(xlLastCell).Row maxCol = Cells.SpecialCells(xlLastCell).Column ActiveSheet.UsedRange.Interior.ColorIndex = xlNone Do While i
I'm trying to compare two very large files to find some that are in one AND also in the other. I'm using =VLOOKUP(C2,Parts2!C:D,1,FALSE) and my results are #N/A for all of them. I have formatted both columns as 'General' because there can be letters and numbers in the serial number, I have also tried formatting as text to no avail. I have confirmed no preceding or trailing spaces or other special characters.
I am however, able to locate values in both documents using Ctrl F. I'm totally puzzled because I've always been able to figure out what's causing my Vlookup issues when I have them but this time I'm not able to.
I have data that as 872 columns - each representing a question ID (headers in the first row). I then have 1494 rows of data where each represents 1 unique person. In other words, A2 = Person ID and B2-AGN2=the potential answers to the questions.
What I'd like to do is compact this into 3 columns: "Person ID", "Question", "Answer".
"Person ID" will have duplicate values for each question that is answered. "Question" is the Question Text "Answer" is each answer to each of the questions.
It would be fantastic if Excel has the functionality to ignore null answers and therefore just not even bother populating Question ID when an Answer is blank (e.g. they didn't report an age, so QAge doesn't show up under the new "Question" field), but I have no idea if that's doable.
I have a lot of datasets like this with a varied number of rows and columns, so any way to adjust whatever formula/macro is out there to work for those. I'm terribly new with macros and so I've been having difficulty adapting them if I need to.
I have inherited a series of Word reports containing links to charts in several Excel files. I can tell by the link in Word that one of the charts is named 'Chart 6'. Is there an easy way to locate which chart in the Excel file is 'Chart 6' (and on and on for all of the other charts linked in the Word doc)? There are upwards of 21 charts in each Excel file linked to each of the Word reports. I am using 2007 but the files are in 2003 format because of limitations of the program from which the data comes for the charts.
How do I make the column headings appear when I'm working on a large spread sheet so I don't have to scroll to the top each time to see where I am. This is on Excel 2007.
appending excel sheet number in a formula. Here is the example.
In an excel book, I have sheets 1 to 50. In each sheet i have a standard format to enter the details. And in one sheet I want to keep some summary with required cells. I made a table by applying formula, for example in cell A1 i kept formula ='9'!$D$9. In this '9' refers sheet9. In the similar way i want to capture the data from all sheets.
Is there any formula to append the Sheet number automatically (like dragging the shell) intead changing sheet number manually. because it is wasting so much time.
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
I have over 60,000 rows of data. In one cell of each row is a "student ID". I need to determine how many times a given ID appears just once, how many times an ID shows up twice, three times etc.
All my macros have suddenly started crashing Excel 2007 when I try to run any one of them. I can record a new macro but even that newly recorded macro will crash Excel.
Service Pack 1 was downloaded and installed by Windows Update two days ago and I had successfully run macros after SP1 was installed.
Today, it's a no go and I'm at a loss as to what may have caused this problem. I have not installed any software lately, other than the Windows updates on patch Tuesday.
This is on a Vista Home Premium machine.
When I try to run a macro, Excel crashes with this info:
I have a workbook that uses a index page that I created. It has lots of hyperlinks on it to route them to other sheets in the workbook. Each worksheet has a hyperlink to go back to the index page, and one to open an email. I have a user with 2010 now that is getting an error when clicking on the hyperlink. Error Message: "This Operation Has Been Canceled Due To Restrictions on the Computer. Contact Your Administrator."
application.FileSearch.NewSearch application.FileSearch.LookIn = Workbooks(ActiveWorkbook.Name).Path application.FileSearch.FileType = msoFileTypeAllFiles application.FileSearch.SearchSubFolders = True application.FileSearch.Filename = "Zone Selling*.xls" application.FileSearch.MatchTextExactly = True application.FileSearch.Execute filecount = application.FileSearch.FoundFiles.Count For i = 1 To filecount Worksheets("Run").Cells(i, 1) = application.FileSearch.FoundFiles(i) Next i For i = 1 To filecount......................
But it won't list my files which is how the macro was running, it was returning a list of the files in the folder and then running them based on the path returned
I've worked with Excel for many years and have just begun writing VBA in Excel 2007, so I am really stumped by this:
SR = 1 SC = 1 TR = 40 TC = 2
Worksheets("ChartData").Activate
' Works ActiveSheet.Range("A1:B40").Select
' All of the following Fail w/1004 Error ActiveSheet.Range(Cells(1, 1)).Select ActiveSheet.Range(Cells(1, 1), Cells(40, 2)).Select ActiveSheet.Range(Cells(SR, SC), Cells(TR, TC)).Select
At first I thought I had a bad install, but this is happening on two manufacturer installed systems... And I seem to be following the documentation for the use of Range with Cells. Am I missing something?
I am trying to migrate a model that I built in Excel 2002 to Excel 2007. The model has an advanced filter in it to get unique values. However in 2002 the filter worked correctly but in 2007 it produces nothing. The code excerpt is below :-
and i want to assign a number value of 1 to all the records that the medication start date is 2 days after the admission date. How do I do this using the ifs function.
I have a spreadsheet that users are filling in using a userform. Due to the fact that some data may be pre/post dated for entry I am trying to find a way that I can count the number of records per month.
What I have been trying to use (with no luck) is COUNTIF:
If I have the numbers 1-50 repeating in columns, 1-50, 1-50 etc is there a way to determine that number "1" falls 1st, 51st etc in the sequence without doing a manual count? Or that the number 2 falls 2nd, 52nd etc?
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.
I wrote a macro to list all the excel files from a directory and its sub-directories to an excel sheet. It is working in excel 2003 but shows error (object does not support this action) in 2007. Actually I have copied almost this entire macro from excel 2007 help only. Can somebody modify this to use in both the versions of excel?
I have used the following code behind a projects Command button for some time, and works well in Excel 2002/2003. It's used to look for and insert a Picture file, located on the users PC, select cell C2, re-size it to fit inside a bordered cell area and then nudge it over, off the border line.
Sub Load_Image() Dim oPict, PictObj Dim sImgFileFormat As String 'Open file GetPict: oPict = Application.GetOpenFilename("All Pictures (*.tif; *.bmp; *.jpg; *.gif; *.jpeg; *.png; *.cpt; *.tiff),*.tif; *.bmp; *.jpg; *.gif; *.jpeg; *.png; *.cpt; *.tiff") If oPict = False Then End Range("C2").Select Set PictObj = ActiveSheet.Pictures.Insert(oPict) With PictObj .ShapeRange.LockAspectRatio = msoFalse .ShapeRange.Width = 712# .ShapeRange.Height = 510# End With PictObj.Select With PictObj Selection.ShapeRange.IncrementLeft 1# Selection.ShapeRange.IncrementTop 1# End With Range("A1").Select
End Sub
Unfortunately, Excel 2007 doesn't seem to identify the Cell reference "C2". It modifies the Picture size OK but does not position the picture in the correct position. I've tried re-recording it but 2007 misses most of actions.??? Despite my efforts looking on other forums, I don't seem to find a code that works on both version of Excel.
