I have a very large spreadsheet that included a large number of SUMPRODUCT formulas. Many of them are running formulas that have SUMPRODUCT layered.
I have most of the SUMPRODUCT lines running 2 arrays to check for the date field. It sees if the date is >= the start date cell and <= the end date cell.
It runs fine, and works great, from a data standpoint. However, I am already running into slowdown when altering the date fields. The workbook is still in the starting stages and is going to get MUCH larger.
I have attached a sample sheet showing a little bit of what I am talking about. The SUMPRODUCT functions on sheet 3 all point to the start and end date fields, and the data updates as they are changed.
I need to find a way to condense the SUMPRODUCT lines. Is there a way that I could run one SUMPRODUCT line in a cell and then reference that cell for all of the other lines? Or is this just the best way to do this?
I have a some data where I need to sum some data based on date range, and dept id's. But the dept ids are parts of various groups. For ex, one group covers about 300+ dept id's. Some of them have ranges (e.g. dept 1000 - 1050) but for whatever reason some dept id's that belong to other groups sometimes fall into the same ranges.
My first hope was one big range, but the overlap issue nixed that. As I mentioned there are some ranges so maybe 100 of them could be grouped into a range but I'd still have 200+ independent dept id's.
I read for 2003 the max criteria for sumproduct is 30 and for 2007+ it's 255. So maybe 2007 might cut it but this doesn't seem like the right way.
Btw, I'm doing this in VBA but I mention sumproduct because I've used it a few times within code via evaluate. Otherwise do I just have a massive string in vba (If deptid = #### or deptid = #### or deptid = #### etc)?
Again, it seems like there should be a better way. I thought about making a master list of all the dept's and sumproduct each one individually and then sum from the ones that fall under group but even then I have to identify the deptids I need for a particular group.
I am having a problem with a sumproduct strand. I've written 4 different ways to no avail. What I'm trying to accomplish is to select specific the sum of various lines of data that fit within 3 parameters
I am encountering a problem with worksheets.copy that occurs after the copy method is called a certain amount of times. My issue is similar (exactly the same?) to what is described in these threads:
- Worksheet Copy: Method 'Copy' of Object 'Worksheet' Failed - Copy Method of Worksheet Class Failed in Loop Code
In my case, after running this code 40 times, it fails.
If loc_after Is Nothing Then .COPY after:=Worksheets(sht_name_orig) 'throws error here after being called 40 times Else .COPY after:=loc_after End If
I would like this code to be able to run more than 40 times. I have read Dave's suggested solution regarding creating a worksheet template: http://www.ozgrid.com/Excel/excel-wo...-templates.htm
However, in my situation the client has specifically asked that the report come as a single stand alone excel file with no add ins. In addition, I am copying several different worksheets, not just one single template that is being used over and over.
I have hundreds of .dbf files that I am manually converting to .csv using Excel. Unfortunately, my .dbf collection is growing faster than I can convert them. Is there a way to convert all the .dbf files in the current folder to .csv? All the .dbf files are in the same folder.
The path to them would be the same as the macro file.
ive got table consisting of about 4000 entries. Below is a small example.
I want to be able to make a button through macro's, where when pressed, it shows only certain postcodes - the way i've been doing it so far is using a combination of If, And, and Or functions to return true or false if a postcode begins with either 'AH4, AH1' and has an order number beginning with 'W', for example. Then I'd record a macro where it shows the ones showing true, via data filter. The problem I've got is I need to search for about 50 different combinations of postcode, out of a list of maybe 300 types of postcode, and using my current IF,AND,OR functions, I simply run out of room in the formula bar! For example, I need find a postcode beginning with CB1-25 (i.e. CB1, CB2, CB3) etc..etc.. the only problem is if i use my current LEFT function, and i want to search for say, CB1, CB14, CB15, out of a list of 'CB1,CB14,CB15,CB16', it will still return a postcode beginning with CB16, due to the CB1.
In the link I've highlighted the postcodes I want to search for out of that small list, and hopefully any solution I'm giving here I could expand to up to 100 combinations out of 500 combinations of postcode, for example.
How do I get it to search for a massive combination of criteria?
I have over 500 countries with unique shipping costs associated with each... each of them has the same mark-up price... 2.25. I am hoping to be able to insert a formula that will cover each country... i am currently entering each one indivdually (E10*2.25), then the next one (E11*2.25), etc.
I have over 800+ pages of chart that only takes up 6 columns and around like 9000+ rows.
I wanted to print this chart on paper and need hardcopies. However, the chart in its current setup prints only on the left half of the page leaving the right half empty.
How do i make use of the full space properly? Each chart has a "page number" on it so I want the chart to print continuously from one half of the page onto the next half and then the second page, third, etc.
Here is a visual demonstration of how things currently are and how i'd like to get them to be:
As you can see, This is the first of many charts and its numbered Page 9 and next one is page 10.
How this looks when i try to print, it's only on the left side. right is all blank Pic2
How i want it to look like upon printing Pic3
As you can see in the last picture, once page 14 chart has no space it automatically continues chart on right side of page and then moves on to print rest.
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
For each column with a header A, B (these are people), 1 in their column represents they works that day on a project. The day columns are days of the week, and people cost different amounts on different days. I need to work out the total cost for each person for the working period.
The cost for the first day for person A is =if(b2=1,vlookup(a2, f2:h8, 2),0)
How do I sum the costs for each day in a single formula without creating a cost per day column for each person i.e. sum each line of vlookups?
I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :
To reach the conclusion I need to build six such functions: =1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66)) =1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66)) =1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66)) =1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66)) =1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66)) =1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))
The result is 4
Is there a single formula that will give the result ?
Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?
In Column B I have some measurements. These are measurements. I chart them in a line graph. I also have my max and min over in some other cells. These aren't the min and max of column, but these are what I have set the min and max at. To make this clearer, my max is 27, but the data in Column B could be anything....20, 28, 35, 15, etc.
I want to plot my data in Column B just as I am currently doing. However I also want to plot the min and max on that same line graph and show them has horizontal lines going across. That way, if one of my numbers is above the max (or below the min), you will be able to see it on the line graph.
Here's what my data looks like. The Viscosity (column b) is what I'm charting on the line graph and you can see the min and max data that I want to plot as well.
I have a file that contains at least 35 sheets. All of the sheets have some lines that are hidden. I tried selecting all the sheets and unhiding everything but it didn't work because some sheets actually have filters in them and the filter line is also hidden.
I like to create a float chart in excel. for this purpose I need to have a candle chart and add lines into the same chart. I can create a candle chart in excel. But as soon I add lines to it (additional data rows) the candles are distorted. The principle I'm interested in applies as well to: how do I add moving averages into an excel candlestick chart? As soon as I add the data for the MAs, the candles are distorted.
In short: how do I combine candles and lines in one chart?
I have an excel document that looks all nice and pretty except for the little white grids all over it with unused cells. Is there anyway to hide the lines? i dont care that the cells are there just the lines make the form look kinda crappy.