Vlookup And Lack Of Resources
Feb 26, 2009
I have a (few) spreadsheets that I am using to gather data. One spreadsheet has over 9000 entries with 20 items for each entry (9000 rows, 20 columns). I use this as my source. In another spreadsheet I have the same column headers but, except for the one column that I enter manually, the rest of the columns are loaded from the source spreadsheet via a VLOOKUP function. As you can see there is a lot of data and a lot of action going on. Needless to say I have run into a conflect with the amount of resources I have available. I keep getting the message "Excel cannot complete this task with available resources. Choose less data or close other applications."
Since I have no other applications open, and I can't use less data, how can I increase my resources. I have been told that Excel allocates resources to a set limit regardless of the amount of ram or other memory you have. Is there any way to increase this?
View 2 Replies
ADVERTISEMENT
Jun 2, 2009
I'm trying to nest if statements that also include "and" and "isblank" factors. The following formula isn't working, and I'm not sure if it's because of my use of isblank or lack/placement of parentheses.
View 3 Replies
View Related
Feb 3, 2009
I find the lack of contrasting colors in the "theme" portion of the color palette useless. For instance--- I TYPICALLY do not need 5 shades of similar blue that I cannot visually differentiate within the color pallette of my working area.
I also need colors that more visually contrast one another and pop out (closer to the row of standard colors that is given or those of prior versions of excel).
Is there a way to add a custom row of colors with a custom title in the color pallette like the "theme" colors have or the "Standard" or the "Recent Colors" ?
If that can't be done...is anyone privy to the syntax for changing the Recent Colors area?
I'd like to add for instance the below three colors to the default recent colors of each new workbook...
Selection.Interior.ColorIndex = 4 '(Bright Green)
Selection.Interior.ColorIndex = 7 '(Bright Magenta)
Selection.Interior.ColorIndex = 37 '(Bright Blue)
View 9 Replies
View Related
Apr 7, 2012
I create excel file to collect and process huge data , it contains a lot of macros , when i run it for more than 2 times error message appears " Excel cannot complete this task with available resources , Choose less data or close other applications''
And after i press ok it continue working but charts not updated until i restart excel , and if i restart excel every time i run this macro this error not appears.
How I can release excel resources when my macro finish it's work.
View 3 Replies
View Related
Jul 2, 2007
I currently have a macro that opens up a specific workbook, copies data from that workbook, and then pastes the data into the control workbook. I am using this macro for a few workbooks but for this specific file I am getting the "Excel lacks resources..." error. Even when I don't run the file and just try to open both files up at the same time I get this error. I realize the best solution would be to split up the file I am copying from but I don't wish to turn to that yet.
View 9 Replies
View Related
Mar 10, 2008
I'm having a few issues with a macro I have written (something I am relatively new to, I might add). Whenever I run it, I get an error message of "cannot complete this task with available resources" - I am currently using Excel 2003
What I am trying to do is cycle though a list of 401 vehicle numbers (the WBSs), using each individual WBS to filter data in another worksheet (sheet "MC01"), and then copy a range of this filtered data into a separate, vehicle specific worksheet (starting at sheet "P1" and continuing to sheet "P401").
The code would run fine when I kept the Field 9 Filter Criteria1 value a constant WBS number, but when I introduced the phrase to use the different values on the WBS sheet (Criteria1:="=" & WBS.Value), the macro would run perfectly for half the vehicles before giving the error message and bugging out.
I thought this could be solved by dumping the memory on each loop by using Set WBS = Nothing but this doesn't make any difference.
Code is as follows:
Sub Update_MC01()
Dim I As Integer
Application.ScreenUpdating = False
Sheets("WBS").Select
Range("B2").Select
Set WBS = ActiveCell
Sheets("MC01").Select
Rows("1:1").Select
Selection.AutoFilter
Sheets("P1").Select
I understand that 401+ worksheets is a lot for any workbook to handle, but the fact that if I set Criteria1 as being a static WBS vehicle number Excel has no issues with it, and everything cycles fine. It is the addition of the phrase "=" & WBS.Value that causes the hiccoughs. From my very basic knowledge of VB, all I can assume is that Excel is storing this "WBS.Value" in it's memory on each loop. Am I right in thinking that the phrase Set WBS = Nothing is the right thing to use to empty this memory?
View 9 Replies
View Related
Nov 26, 2008
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
View 4 Replies
View Related
Mar 19, 2013
I need to pull a data from 3 different SharePoint lists and present it in a new data sheet. So far I know how to create data connections and I am able to see everything in 3 different worksheets but in the same workbook. Also if we have the same Project and IT number we should pull information just from the SharePoint 1 list.
View 1 Replies
View Related
Mar 24, 2009
I would like to know where can I find a good resource to learn about VBA syntax. I have VBA books and there are some examples of code in there but when I'm trying to read someoneelse's code I just get into commands or syntax I don't understand. For example I would like to be able to go some site and decifer what this line (or it's parts) means: If(cnt < MAXTEST, sDigSep & String(MAXTEST - cnt, "9"), "")
View 4 Replies
View Related
Nov 9, 2006
i would like to find out about:The different essential building elements for different chart types (like 3DBubble requires XValue, Value, Name and BubbleSize, for example, but what about clusteredColumn and the others?)How to address the building elementsThe optional building elements (changing background color and stuff)
View 2 Replies
View Related
Dec 22, 2006
I need to work with a spreadsheet with 15000 rows of data. By the time I link this file with my final file and add formulas, my file has reached a massive size and the following pop up error message appears.
View 3 Replies
View Related
Sep 11, 2007
A pop-up window with the next message: "insufficient resources to show all" is displayed when I try to use a combobox in a worksheet.
Details:
I have been using excel 2003 in a Pentium IV 1 GB Ram to run a large workbook (at least 40 worksheets) that uses a combobox to find a price from a database. The workbook contains lot of images and some single macros.
The error appears after I opened the workbook and I tried to use the combobox. Finally, after various clicks, I get the value requested but the pop-up window doesn’t disappear.
If I do not use the combobox I do not receive the error.
Done:
I have done unsuccessfully all the suggestion that I found on the web (Google, msn and AltaVista), and of course the Microsoft forums. I tried to clean memory, erase temporary files, increase memory values and so on.. but nothing seems to fix the issue.
I also tried to run the workbook in 3 different PCs – and 3 different RAM sizes - getting the same error.
View 3 Replies
View Related
Sep 29, 2003
I've been working with links the past few days and have been experiencing the subject error message. What do I need to do to avoid the error?
View 9 Replies
View Related
Jun 21, 2006
I have a large spreadsheet that uses the lookup() to return data from two other large sheets.
The exact formula I am using is:
=IF(B1<>"",IF(ISERROR(MATCH(B1,'X:Store Users Data FilesPMMJim.FosterDATA[shop sheet data.xls]shop sheet data'!$A$1:$A$16374,0)),"NA",LOOKUP(B1,'X:Store Users Data FilesPMMJim.FosterDATA[shop sheet data.xls]shop sheet data'!$A$1:$A$16374,'X:Store Users Data FilesPMMJim.FosterDATA[shop sheet data.xls]shop sheet data'!$C$1:$C$16374)),"")
I have this same (or very similar) formula repeated about 3300 times in my main workbook. When I try to update links I get the following error. "Excel cannot complete this task with available resources. Choose less data or close other applications." If I "Open Source" it will update the links but when I close the "Source" I get the same error..."Excel cannot complete this task with available resources. Choose less data or close other applications." The source will close eventually and my links have updated but I can not save the work book. Much smaller versions of the same sheet work fine. What is the limit number of such links?
View 5 Replies
View Related
Feb 3, 2011
Excel 2007
Trying to insert a row and window popped out -Large Operation. " The operation you are about to perform effects a large number of cells and may take a significant amount of time to complete. Are you sure you want to continue?" I pressed ok , then, says "Excel cannot complete thsi task with available resources. Choose less data or close other applications. "
View 9 Replies
View Related
Jul 10, 2007
I am trying to copy 8 adjacent cells in a sheet and paste them on another sheet as a picture (using copy picture method). I could do that 3/4 times, but after that I started getting the insufficient system resources error.
View 7 Replies
View Related
Oct 8, 2009
i am looking to create a small table from the attached worksheet that will show how many users there are for 3 different locations, the user names are in column a and the locations in column c. the thing is, the actual worksheet i am using in work contains almost 1000 lines, and is being updated daily, there can be multiple entries for a person for projects etc.. and someone can even be in the availability and pto area's without being in the main project area. each resource can only be on one location so that will not change.
also, there can be blank cells for resource which should not be counted. i was wondering if there was a formula or a macro that would count the distinct number of names and reference them to the location and give a count of resources by location?
View 2 Replies
View Related
Jul 11, 2012
I have VBA code that attempts to delete an entire row from my worksheet:
Code:
Cells(3,1).Select
Selection.EntireRow.Delete
This works fine on small data sets, but on larger data sets it gives me the error message, "Excel cannot complete this task with available resources". This happens even when I try to do the deletion manually (without VBA code). Clearly, the code itself is not the problem.
My document has about 250,000 rows and 2,500 columns. While this is big, it is significantly smaller than Excel's documented limit of 1,048,576 rows and 16,384 columns.
I am using Excel 2007. My computer has 2GB of RAM but even when I try it on a computer which has 8GB of RAM it gives me the same error. If I "ClearContents" instead of "Delete" it works fine. For my purposes, however, deletion is entirely necessary.
View 9 Replies
View Related
Apr 19, 2006
I have the following code for a sheet in my workbook that has 3 charts:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculation = xlCalculationManual
ActiveSheet.ChartObjects("RdteObs").Chart.SetSourceData ThisWorkbook.Names("GSumRdteObs").RefersToRange
ActiveSheet.ChartObjects("RdteWip").Chart.SetSourceData ThisWorkbook.Names("GSumRdteWip").RefersToRange
ActiveSheet.ChartObjects("RdteExp").Chart.SetSourceData ThisWorkbook.Names("GSumRdteExp").RefersToRange
Application.Calculation = xlCalculationAutomatic
End Sub
but whenever the sub runs, I get this error message: "Excel cannot complete this task with available resources. Choose less data or close other applications." Does anyone have an idea what's going on?
View 3 Replies
View Related
Apr 15, 2008
I have a spread sheet with 4 columns and 90,000 rows utilizing 4 "IF/THEN" logic formulas. I am getting a "not enough resources" message in Excel even though I have a top end laptop with all the ram and the biggest HD available.
View 3 Replies
View Related
Aug 18, 2009
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
View 3 Replies
View Related
Oct 5, 2009
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
View 5 Replies
View Related
Oct 29, 2012
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
View 4 Replies
View Related
Jul 13, 2009
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies
View Related
Jun 9, 2009
I’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
View 14 Replies
View Related
Jul 24, 2009
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:
View 5 Replies
View Related
Sep 17, 2008
I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.
This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.
I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:
View 14 Replies
View Related
Jan 8, 2009
I am receiving a run-time error with following code. The error message is "unable to get the VLookup property of the WorksheetFunction class". I only receive the message when the lookup value is not found in the table.
I thought adding the "False" command at the end would return an "N/A" but it didn't. Is there anything I can add to avoid this error?
View 3 Replies
View Related
Jul 15, 2008
I'm taking a spreadsheet that I produce each month and creating a year to date spreadsheet in the same format. I'm using a vlookup to find the campaign name in each sheet and add up the totals. This works fine but sometimes a camapign ends and so the vlookup for that month will produce an #n/a value so will reduce the whole sum to #n/a.
The VLOOKUP + VLOOKUP + VLOOKUP I was using that produced an #n/a is shown below.
=VLOOKUP($A6,'[Margin by Site Net April 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net May 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net June 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)
To get round it I've added in an IF statement combined with ISERROR as shown below. It works but is looking quite messy. Is there an easier way to do this ? (the formula below is from the cell below the one above so the look up value is one cell down)
View 9 Replies
View Related
Dec 12, 2013
I am trying to use Vlookup to find out the min and max sales of the products shown in the spreadsheet. I have used Vlookup before but only by itself and with 2 columns.
View 5 Replies
View Related
