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 Repliesi 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?
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.
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 RelatedI'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?
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!
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?
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 RelatedI 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 RelatedI 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 RelatedA 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.
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 RelatedI 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?
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. "
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 RelatedI 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.
I have an existing chart and I want to update the plot utilizing data that has been calculated and stored in 2 arrays X(N,1) and M(N,1) via my VBA macro. How do I do it?
I am just reusing some code that works most of the time to reset the seriescollection of specific charts. Sometimes is will just error when I try to set the .values object to my range of values. It's strange because it will work for 10 seriescollections/identicle ranges on the same chart and then error out; and sometimes it will work perfectly.
I've checked:
The chart reference object
The range I'm setting
The code I've copied from
It's all correct. Here's a short sample.
ValRng = Range("AR7:CY7")
Set usechart = ActiveSheet.ChartObjects(1)
With usechart.Chart.SeriesCollection.NewSeries
.Name = "Prime 2000"
.Values = ValRng ' (also tried it this way) "=" & ActiveSheet.Name & "!" & Range("AR7:CY7").Address
End With
The series is created fine, and the name is set properly.
On my new sheet here it won't set ANY values, which is a real problem. If anyone knows what is going on, please let me know.
I have a large number of files containing thousands of X and Y coordinates. I prepared a macro which loads these X and Y data from all the files into one workbook, and it writes the file names into each row. So, in the end I get a file with this formation:
X Y filename
x1 y1 filename_1
x2 y2 filename_1
x3 y3 filename_1
.
.
.
xi yi filename_2
.
.
xn yn filename_m
It would be great if I could make then an XY Chart which contains the information of "filename" column too. I didn't mean labling, because in the end I'll have hundred thousands of points, so if I added labels to each point it would be impossible to see anything. I would only set that if I move the mouse to any point of the chart, it would show not only the X and Y coordinates, but the related filename too.
Is it possible to set it so in excel?
produce graphs/charts based on inequalities monitoring information - gender, nationality, age etc. I'm capturing info from several events, one sheet per event. Had a look at COUNTIF but not sure it's what I want and looks like I'd need sub-sheet for each? Capture.PNG
View 7 Replies View RelatedI am working on a project at work. I need to track our VIP guests. I only have their arrival and departure date.
Is there a way to graph and track the days between so that I can have a total of all arriving and In-house VIP guests on all dates in the range?
Here's a sample of the data.
10/18/201110/19/2011HOERMAN/SCOTT10/17/201110/21/2011SMITH/JOHN M
10/10/201110/13/2011WARNER/AMY10/18/201110/19/2011GERONDALE/RON
10/11/201110/12/2011BUCHANAN/JEFFREY10/17/201110/18/2011HUND/LARRY
10/20/201110/23/2011HADDOCK/CHRISTOPHER10/9/201110/12/2011
BLOOMER/ROBERT10/20/201110/23/2011COLE/ELIZABETH10/7/201110/8/2011IWASA/KENNETH
I have a line graph which plots the PERCENT_YIELD of units built (UNITS_PASSED / UNITS_BUILT) every week. I want to fill all future week cells with a formula so dragging the formula is not a manual operation every week (my overall goal is to automate a complex spreadsheet and charts as much as possible).
The formula is =IF(Q1080,Q109/Q108,)
Where Q108 is UNITS_BUILT
Where Q109 is UNITS_PASSED
My theory is if units were built (Q108 not equal to zero), plot the ratio. If no units were built (Q108 is zero) I do not want to plot any value on the chart.
The chart plots the correct values for the True conditions; however the chart plots a zero value for the False conditions. How do I not plot a zero for a false condition?
I am having an issue with dynamic charting using named ranges in excel. I quite often create dynamic charts using IF and OFFSET formulas to check conditions in order to create charts of data based on user inputs.
The issue I am having is with linking multiple tables of data in a single named range. In order to make this spreadsheet easy to maintain I am trying to take 3 separate databases and link the spreadsheets through named ranges. The formula I have written is as follows:
=VLOOKUP(VALUE(LEFT(OFFSET(Processed!$B$1,1,0,COUNT(Processed!$C:$C),1),6)),Raw!$F:$O,10,FALSE)
For context this is;
=VLOOKUP(VALUE(LEFT(OFFSET("Serial number I am referencing",1,0,"Qty of rows containing data",1),6)),"Value I want to return from separate table",10,FALSE)
[The formatting of the serial number between the two sheets is slightly different but they share the first 6 digits (hence the wrapped VALUE and LEFT formulas).]
This formula is working perfectly except that it doesn't store the entire data table in a single cell, it only returns a single value. If I enter the formula into a cell and drag it down it returns all the information correctly, however if I create a named range using the formula and try to chart it only the first value returned is charted.
Any way to modify the formula to store the entire data array in the single cell value so it will chart when used as a named range?
So I was looking for reassurance or validation more than anything. From what I can tell you can in order to build a chart that is dynamic throughout a range, you use the offset and count or counta function - 1. That part isn't a problem. My question is once you created that for your charts do you just normally plot your chart range or do you have to reference the named range directly into the chart range?
View 2 Replies View RelatedI have a simple sheet to record and graph my bloodpressure . The list of values is as follows:
col A is always todays date
cols B and C user input - integer
col D is a constant value 120
col E is also a constant value 80
Currently I am manually charting the range A1:Exx each day after I have added a new line for my daily readings. Whilst this works fine, I'd like to automate the charting process as currently if I select a range full of blank cells then my chart displays loads of blank values.
So I could use a Button on the graph which works out how big my cell range is ie; A1:E66 and graphs it for me or alternatively a background macro that does the same after I have entered the C col entry for each day
I have a workbook with Sheet1 and Sheet2. I’ve programmed information from to be calculated and results inserted into sheet2. A new result every Row. I plan to do a graph taking the X-axis and Y-axis values from Columns A and B respectively in Sheet2. What is the VBA code for dynamic updating of graph? Meaning, I want the graph (a curve) that will have points added to the curve, extending it, whenever a new row is added into sheet2 – Columns A (X-axis) and B (Y-axis). The rows are not defined. It can have 100 to 200 rows or more..
View 6 Replies View RelatedI need to create a simple Talk Time Chart displaying our call center's talk time in 30 min increments.
TimeATT
08:00:00:02:03
08:30:00:04:06
09:00:00:03:56
09:30:00:02:57
10:00:00:04:57
10:30:00:03:29
11:00:00:04:41
[Code] .....
I have a spreadsheet with 4 columns. The first column is a Date/Time object and the next three columns represent the R, G, and B values of a color. The color values are 8-bit numbers(0-255). What I'd like to do is create an area or bar chart where the x-axis are the dates and the y-axis is the overall brightness of the color (a simple average of R,G,B would suffice). However, I'd ideally like the color of the bar to be displayed by the R, G, and B values from the cells. Is this possible? I've attached a dummy file for reference.
View 6 Replies View RelatedI have several years worth of monthly data that I would like to keep hidden so it is printer friendly but I also want to have a chart of all that data... Is this possible? Only idea I have is to basically recreate the data sheet but I don't really like that solution since it adds steps to the process and increases the file size...
View 2 Replies View RelatedI'm having difficulty figuring out how to line graph two separate dates. I also want to show a trend.
I was first thinking I would need two graphs, but is there a way to merge into one?