500,000 Dependencies
Oct 5, 2008
My report ran great on small data set, but on large data set the calculation has scrolled to a crawl. (A one hour crawl in 2003 and a 6 minute crawl in 2007.) I read something about dependency limits, and ran the macro provided on Mr.Excel, which calculated close to 500,000 dependencies, which I assume is my problem. Then again, I thought I couldn't have more than 65,000 or so dependencies, and if that's the case why does it run at all?
Sheet1 has about 25 columns, most with sumproduct formulas that check for data returned by queries on 11 or so other sheets. For example:
=SUMPRODUCT(('Job Costs'!M$2:M$14180=C728)*('Job Costs'!F$2:F$14180>=$U$6)*('Job Costs'!F$2:F$14180
View 9 Replies
May 28, 2009
I am working on a user form which is pretty much functional, I have it set up where one combo box controls the results of the next based on user selction. I would like to take this one step further and base all the results on the selection of an option box. I have triplicated my results for the option box in the 'control' worksheet, but can not figure how to filter the results based on the inititial 'user type' selection.
View 9 Replies
View Related
Aug 29, 2012
A line graph is using a data row to plot the line; works fine. The data series ranges have been named for Week1 (H10), Week1_2 (H10:I10), Week1_3 (H10:J10), Week1_4 (H10:K10), Week1_5 (H10:L10), Week1_6 (H10:M10), Week1_7 (H10:N10), ...etc.
This is the data series formula name example to plot the data series from week 1 through week 7; ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7.
The series name formula Q3Wk1_7 for week 1 through week 7 is =$H$10:$N$10.
When this name is manually entered as a Series Value for the line graph, the graph plots exactly the way it is expected. The same is true if the Series Value is manually modified for some other ranges such as Week 1 through Week 9 (='Product Plan Q3 Week 7.xlsx'!Q3Wk1_9).
My challenge is to enter this Series Value based on the manually entered week number elsewhere; i.e., we're in week 7, the Series Value should be ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7. I've been trying to use an HLOOKUP table to select the correct Series Value formula based on the week number. The HLOOKUP works fine resulting in selecting the correct formula. I can also Name the range of data I want to graph and copy the name into the Series Value field with successful results. How do I get such a formula or Named range into the Series Value field without type it in? I want to set that Series Value to a range or name without having to select the line to manually enter the value.
View 3 Replies
View Related
Feb 7, 2012
So, I need to figure out how to make the populated contents of a single dropdown box dependent upon the selected results in two other drop downs.
As there are ~35K lines in the sheet, creating Lists is impractical, and VBA is out due to client security settings.
My data sort order is: Region Name > Area # > Facility Name
I have a drop down to select the Region I want to work in.
The drop-down for Area # populates based on the selection from Region Name.
The hang-up is that the area numbers are 1-4 for each Region.
So, I can't simply populate another drop-down for Facility from the result from Area #, but it has to be based upon the results of both of the former results.
View 4 Replies
View Related
