Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Auto Sum A Varying Range


I am trying to auto sum a range of cells, in 3 columns, that varies from sheet to sheet.

I have attached a sample sheet. The columns I am trying to total are columns "E", "F" and "G".


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Sum A Varying Range
I want to sum a range of cell in a macro. The range can change depending on what is imported. For instance, my range is from (Ax:Ay) and sums this amount on the cell below Ay.

View Replies!   View Related
Varying A Range In A Formula
I need some programming help to control the length of the range in the formula below:

View Replies!   View Related
Set Range Of Varying Size
I am try to Set a range to a variable. The problem is the range differs in size. The good thing is there is a blank cell before each group that I am attempting to set the range to. I know it should be possible to set the range to stop at the blank row. I am locating the correct row by the date and want to set the range to include the next three cells to the right and all the cells below them up to the blank row.

View Replies!   View Related
Define Varying Autofilter Range To Print
I have created a button to print an inventory list daily. The size of the list changes each day. The code I'm using works except I only want the Current Region to be defined as only the first 4 columns.

Private Sub CommandButton5_Click()

Sheets("Inventory").Select
Selection.AutoFilter Field:=4, Criteria1:="Inventory"
Set rng = Selection.CurrentRegion
ActiveSheet.PageSetup.PrintArea = rng.Address
ActiveSheet.PrintOut
End Sub

View Replies!   View Related
Loading An Array With A Varying Range Size
I want to load one with data from a worksheet query table that will vary in size each time it's executed. The length will vary, but the range is continuous, and five columns wide. I'd prefer a do while loop, looping until a blank cell is encountered. Can someone get me started with general syntax and setup of the array?

View Replies!   View Related
Auto Name Range
I would like to automaticaly name rows as a range from A:W.

The name should be the value in column A.

View Replies!   View Related
Auto Sort Set Range
I know that I could just click sort, decending, "points" etc but I figure there must be a way of it auto updating.....

Please see the attached file - I have attempted doing it with Ranking Formula but forgot that it would just give me error messages when two have the same points so I got rid of that.

Have tried doing it with other formula found on this website and others, however I appear to be stuck. I might be doing it wrong but as I'm not manually putting in the "points", they are being worked out with =( SUMPRODUCT((H4:H16={"W","D","L"})*({3,1,0}))+58) for example, it does not move their position.

What I would like it to do:

1. People select from drop down list whether they think the team will W, D, L
2. The Table to the right should automatically change the points dependant on the result
3. The Teams position should change automatically in line with their new points total.

View Replies!   View Related
Auto Numbering .. For Each Range
After applying subtotal function based on city, i have blocks of data with subtotals at different places. if i want separate numbering starting at 1 for each range , what i have to da. at present i am doing it manually ...

View Replies!   View Related
Auto Fill From Cell Range
I have a spreadsheet that is downloaded from an accounting system and requires extensive editing to be normalized before it is combined with data in another database. To normalize the data in a simple manner, I use the following macro:

Sub All()
Rows("1:37").Select
Selection.Delete Shift:=xlUp
Sheets("DL").Select
Columns("A:D").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Check"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Benefitor"........................

It works great, however I would like to not limit my cell ranges for the auto fills. I would like to auto fill to the end of the data, which changes with every download (additional rows).


View Replies!   View Related
Auto Filter Method In Range
This error occurs after I wanted to use the Auto-filter function for every row. I used to separate the merged cells that used to be into single cells. After that the compiler reports error. Here is the code:

View Replies!   View Related
Copy Auto Filtered Range
I'm trying to do is copy filtered data. I select my column and copy it, filtered, no problem. But then my code bugs out when it tries to copy the next column.

If Sheets("CT Summary"). Cells(4, 6).Value = "P1264" Then

Sheets("Mam Roll-up").Activate

'Copy CEID's.

Range("L4:L134").Select

Selection.SpecialCells (xlCellTypeVisible)

"Selection.SpecialCells (xlCellTypeVisible)" works in the first block, but bugs out on the second.

View Replies!   View Related
Auto-Extending Graph/Chart Range
I have a Line Graph with 2 lines on it. Is it possible to write a macro to extend the range (Source range) of one of the lines ? I would also like to add a Data Label to the newly added point.

For e.g. If I have Line 1 graph only till 65, I want the macro to extend the range by one row to include 95 and also display a datalabel ......

View Replies!   View Related
Auto Display Range As Upper Case
I have been trying to get a certain selection of cells to display inputs as capitalized no matter if entered in lower case. I looked through this forum and found some, I did try some of the suggestions, and I am having no luck. I am wondering what I am doing wrong?

View Replies!   View Related
How To Make This Macro Work For Auto Range
I have generated macro, all I want is when this macro runs on different work sheets it should automatically select the active range based upon column A and copy the formula in the inserted new column B, please see the macro which will make it clear. Basically whenever it is copying a value or a formula it should be for the active range.

View Replies!   View Related
Auto Populating Cells From A Date Range
Basically i am trying to auto populate cells using two dates as the parameters, set up is

A1 - Start Date
B1 - Finish Date
C1 - Jan 07
D1 - Feb 07
E1 - Mar 07....and this carries on across till the end of the year.

What i am wanting excel to do is to be able to put in a start date and finish date in to the relevant cells.....this then populates a fixed figure across in to the correct month.

Eg
start date 05/07/07
finish date 17/10/07

This then puts in the figure 0.2 in to each cell that correspond with each month....July 07/Aug 07/Sep 07/Oct 07.

So in effect it is giving a gantt chart but with data in it.

One more thing....the start and finish dates could well be out of the range.....

eg
so start could be 07/08/06 and end could be 18/04/07 which would mean that all of the fields would need populating.

View Replies!   View Related
Copy Auto Filter Range To Another Sheet
I want to filter the data: [the data I have In "Sheet1]"

Sub Makro5()
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="5"
Selection.AutoFilter Field:=2, Criteria1:="6"
Selection.AutoFilter Field:=3, Criteria1:="7"
Selection.AutoFilter Field:=4, Criteria1:="99"
End Sub

and now I want to copy from "Sheet1" to "Sheet2" but only Field:=4, where criteria1:="99"; In "Sheet2" I want to paste my filtered data to:

Range("B1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1," & _
"AB1,AD1,AF1,AH1,AJ1,AL1,AN1,AP1,AR1,AT1,AV1,AX1,AZ1," & _
"BB1,BD1,BF1,BH1,BJ1,BL1,BN1,BP1,BR1,BT1,BV1,BX1,BZ1," & _
"CB1,CD1,CF1,CH1,CJ1,CL1,CN1,CP1,CR1,CT1,CV1,CX1,CZ1," & _
"DB1,DD1,DF1,DH1,DJ1,DL1,DN1,DP1,DR1,DT1,DV1,DX1,DZ1," & _
"EB1,ED1,EF1,EH1,EJ1,EL1,EN1,EP1,ER1,ET1,EV1,EX1,EZ1," & _
"FB1,FD1,FF1,FH1,FJ1,FL1,FN1,FP1,FR1,FT1,FV1,FX1,FZ1," & _
"GB1,GD1,GF1,GH1,GJ1,GL1,GN1,GP1,GR1,GT1,GV1,GX1,GZ1," & _
"HB1,HD1,HF1,HH1,HJ1,HL1,HN1,HP1,HR1,HT1,HV1,HX1,HZ1," & _
"IB1,ID1,IF1,IH1,IJ1,IL1,IN1,IP1,IR1,IT1,IV1")

View Replies!   View Related
Apply Auto Filter To Active Range
I find it to be a little irritating that I have to remove the autofilter, then reapply it whenever I start entering data into a new column. I want a macro that I can assign to a button so all I need to do is hightlight the range of cells I want to apply the autofilter to, and click the button.

View Replies!   View Related
Auto Updating Range Reference In Vlookup Macro
I have the following macro which runs vlookups between two sheets in excel. Whenever i add columns to the range the vlookup column reference is not the correct cell. Is their any way I can adjust the macro so that the column number adjusts in the formula when a new column is added??

Sub template()
Range("C7").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE)="""", """",VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE))"
'category
Range("c9").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE)= """", """",VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE))"
'sub-category
Range("c10").Select...................

View Replies!   View Related
Sum For Varying Date Ranges
What I'm trying to do is sum the daily interest of a loan for any range of dates. Imagine this example:

1) In column A you have dates from 01/01/2009 through 12/31/2059, with each individual day showing, so literally thousands of cells.

2) In column B you have just random values corresponding to each date, such as $100,000 on 01/01/2009, $139,000 on 01/02/2009, $52 on 01/03/2009, and so forth. These values occupy as many cells as the dates, i.e. there's a cash flow (value) for every date.

3) I want to run a formula in column C that will sum a date range I specify. For example, if I want to sum all the daily values for each month (i.e. the 1st through the 30th), or if I want to sum all the daily values for the 15th of a month through the 14th of the following month. In other words, the loan can start on any date, and I need to sum the interest for each "monthly anniversary."

4) I'm thinking that the formula will have to be different to sum an actual entire month. For example, formulas to sum from, say, the 15th of one month through the 14th of the next month, or the 10th of one month through the 9th of the next month, might be similar.

However, as there is a varying number of days in most months, that formula might not work (though I might be misunderstanding myself) when trying to sum the values for an entire month, i.e. February 1st through 28th. I think it might be solved by some sort of "if," such as if day = 1, then sum whole month, otherwise some from date through same date of next month less the last date's cash flows?

View Replies!   View Related
Varying Lengths Of Digits
My data is as follows, all in one cell:

10015200 US Government Bonds 18,369,423.52
10012301 US Government Bonds 10,232.21

There are many varying lengths of digits. I am trying split off the numbers
to the right of US Government Bonds into another column. Here is my
formula:
=RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7)))

My result is 9,423.52. Its not pulling the whole number.

What do I need to change in the formula?

View Replies!   View Related
Hyperlink To Varying Web Pages
I have a spreadsheet that has different product codes that I want to link to their corresponding web pages.
At the moment I individually input the hyperlink into each cell using this common address http://www.ace-sales.co.uk/Catalog.aspx?search=25617 it is just the 5 digit code at the end that changes each time
Can I put a common hyperlink in all the product code cells, then just put the product code in the cell and then the hyperlink automatically links to products corresponding page?

View Replies!   View Related
Make The Auto-filter Range Bigger Than 1000 Cells
Is there any way witch i can use to make the auto-filter range bigger than 1000 cells?

View Replies!   View Related
AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS
I RECORDED THIS MACRO BUT I WILL LIKE TO MAKE TO AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS MY CODE

Sub FORMAT_AS_A_TABLE()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$1900"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleDark5"
End Sub

View Replies!   View Related
Checking Subtotals With Varying Entries
Entries in column B indicate a new entry. What I need to do is to check that the sum of values in column G equal the corresponding value in column B. So, Entry B is incorrect for example, as 40,20 & 30 does not equal.

In the real example, here are hundreds of enties. Each entry has varying numbers of rows.

View Replies!   View Related
Extract Varying Lengths Of Text
I have a column of data that contains suburb, state and postcode (zip code). I can extract the postcode quite easily using:

=RIGHT(H2,4)
However, sometimes there is one word for the suburb, and other times there are two or more words. Also, the state abbreviation contains either two or three letters:
MARYBOROUGH QLD 4650
BRAY PARK QLD 4500
TEA TREE GULLY SA 5091
Does anyone know of any code I could use to extract the three pieces of information into three new columns? (I.e. Suburb, State, Postcode)

I imagine the formula would contain some method of counting the number of paces; however, the number of spaces between each word may be more than one space.

View Replies!   View Related
Selecting Varying Number Of Rows
I need to create a line of VBA code to have the macro select several rows to be cut and moved. The problem is, at this stage of the macro,the number of rows vary. Could be 1, could be twenty. I have established DD as the Dim for the number of rows, but I am not sure how to write the command so that 1 through DD are selected, cut, and moved. I may be having a senior moment, but I can't think of how to couch the term.

View Replies!   View Related
Varying Graph From Filtering Data
I've created two large historical tables on separate sheets. The master sheet (Bins) contains the following columns:
Year - Side - Commodity - Variety - Block and then each consecutive column is a day of the year, starting at 4/26 and going through 10/31 (F - GL). Column GM is a sum of all days in each row.

For example, GM15 totals F15:GL15. The second sheet (Percentages) is identical to the first in size and format, however, it calculates daily percentages of totals from the first sheet. For example, cell BA15 contains the formula

View Replies!   View Related
Summarise A Varying Quantity Of Worksheets?
I have monthly workbooks which each have 5 weekly sheets and one summary sheet. I intend to copy the summary sheets to a new "total period analysis" workbook.

In this second annual workbook I intend to total all the monthly sheets together then analyse it. BUT as the books for the months to come haven't been written yet, I can't include them in my formulas. How can I write formulas to include the sheets that WILL be included without getting #ref errors? Can it be done. Or, is there a better way to do this? Some VBA to add the new sheets to the formulas maybe? I don't know VBA.

View Replies!   View Related
Varying A Cell Reference In A Formula
I am trying to identify some combination of formulas or functions that will allow me to adjust the cell reference within a function based on a value in another cell. For example, I have a series of values listed from cell C50 to cell W50. I want to use a function to calculate the internal rate of return over a range of the cells: =IRR(C50:W50)
So far, so good.

However, I want the size of the range to be determined by a value entered in another cell, let's say B2. If the value in cell B2 is "5", I would want the formula to consider only the first five columns: =IRR(C50:G50). If the value in cell B2 is "10", I would want the formula to consider only the first ten columns: =IRR(C50:L50).

View Replies!   View Related
Loop To Open Spreadsheet With Varying Name
I'm especially having trouble with loops. I'm a little dense in that department. Here is what I am trying to do. I open 3 spreadsheets. I want to consolidate them into one of the spreadsheets. The first obstacle I face is that one of the spreadsheets has a name which varies. It is always called either "PLR.xls" or "PendingLoans.xls". Also, it will have a number at the end depending on how many excel books have been opened so far in the day, i.e. "PLR (2).xls" or "PLR (5).xls". At this point, all I want to do is be able to select this file consistently. I currently have something like this (excuse my syntax - its saved at work and like I said, I'm still learning VBA).

View Replies!   View Related
Sum Total For Varying Number Of Cells
----------A----------B---------C
1-------------------100
2--------11/29----150-------250
3--------11/29----300-------300
4-------------------200
5-------------------300
6-------------------150
7--------11/30----100-------750

Have column A with date, column B with subtotal, column C with Total. The Total (C) is made up of one to five subtotals (B). Subtotals to be included in the Total are those that come after the previous Total up to the row with a date. Only a row with a date has a Total. Is there a formula that can do this?

View Replies!   View Related
Macro For Rows Of Varying Lengths
I paste in information in Columns A-E with varying lengths for each report that I need to generate (They generally range anywhere from 10-500 rows). I have formulas already in place in the first row of Columns F-L that need to be copied down as far as columns A-E go. I recorded a macro for this when I try to run the macro for another report with more or less row entries it copies down as far as the original recording was set. How do I make it so the rows will copy down just as many as I need and not some arbitrary number of rows? This is my first post so if any clarification is needed that's no problem.

View Replies!   View Related
Extract Date From Varying Text
I am trying to extract dates from text strings of varying lengths. The date may appear anywhere in the string. The dates are all 8 characters, in the mm/dd/yy format.

For example:

12/12/2007 is the date in this string.
There is a date of 12/12/2007 in this string.
The date in this string is 12/12/2007.

View Replies!   View Related
Linking Rows With Varying Increments
I have a set of number in worksheet IN:

A B C D E Columns
41.0141.0241.0341.0441.0
42.142.242.342.442.
43.0143.0243.0343.0443.0
44.144.244.344.444.
45.0145.0245.0345.0445.0
46.146.246.346.446.


Datarows increment by 1. I need to map these to another workbook worksheet named OUT
to rows that increment by various amounts 1,2,3 ....10) i.e..............................

View Replies!   View Related
Sort: Multiple Varying Size Ranges

name data1 data2 total
chris a b 100
a a 100
a a 100
steve b b 90
b a 90
b a 91
jane b a 89
a a 90
a a 92

its hard to line things up in here..... anyway... each name has two more lines of data associated with it. i want to take the 4th column last line for each and sort from greatest to smallest and rearrange all the data but keeping all three lines associated with each person together. one added complication. the number of ppl in this will vary although i can do a count of every three lines and tell exactly how many there will be. so for this purpose we can just keep it at three.

in this case, all three lines associated with jane should be moved up above the three lines of steve but stay below the three lines of chris. and the three lines for each person stay in the same order they are now

View Replies!   View Related
Macro, Autofill On Varying Numbers Of Rows
I am putting together a macro to import a CSV file onto excel. I will be manipulating columns of data (concatinating some, dividing by 100 etc.)

What I am having trouble with is that each file imported will have different number of rows.
I tried, in recording a macro, to use autofill (entered formula and double-click fill handle to drop the fourmula into each cell) hoping this would show me how to do it but alas, it just coded the range as the starting and stopping cells (didn't show a count formula or some such.


View Replies!   View Related
Identifying Varying Data And Rank Formula
i am trying to work out how to use the rank formula to rank numbers in column B and keep them in unison with Column A.

So Column A has say 5 1's with column B having different scores then continuing under 1 in A is 2 and so on is there a way to continue the ranking formula without manually changing the cell ranges?

so =rank(B1,$B$1:$B$7,1) but can i do that if A =1 and then A=2 etc ?
so if A=1,rank(B1,$B$1:$B$7,1)

View Replies!   View Related
Macro For Summing Columns Of Varying Lengths
I have a worksheet (downloaded from Paypal) and the columns I need to sum are of varying length (sometimes 12 rows and maybe 200). I am having trouble finding a way to find the end plus one of the column to write the formula in.


View Replies!   View Related
Computing With Last 252 Rows Of Varying Length Column
Using Excel 2003, Column E has a varying and unpredictable number of rows, which only the final 252 rows are of significance. I need to compute the following formulas, here written in English as I can't determine the proper terminology to accomplish this task in Excel:

Final Row with a number in Column E/average (final 252 rows with numbers in column E)+

Final Row with a number in Column E/average (final 126 rows with numbers in column E)+

Final Row with a number in Column E/average (final 63 rows with numbers in column E)

Once again, what makes this not straightforward for me is the column may have wildly varying numbers of rows.

View Replies!   View Related
Transpose Macro Varying Rows From 1 Column
I've trawled a few forums for a while now trying to find a macro that matches my specifications, or one I can adapt but so far have been unsuccessful, so here goes:

I have one column of data (col. A) of customer names and addresses each with an empty row in between them. The lines of data relating to each customer vary from 6 rows to 14 rows, then an empty row, then the next customer and so on - for several thousand rows.

I am wanting to create a macro that will transpose each customer to either their own row in sheet2, or to column B and then delete column A at the end.

All I can find is macros for a specific number of rows rather than varying. One way would be to have a code that 'pads' out those customers with fewer than 14 lines by inserting rows to make them 14, and then a code to transpose 15 rows at a time.

View Replies!   View Related
Copy 3 Columns And Paste With Varying Row Offsets
I am trying to copy three columns of information from sheet1 into sheet2.
When pasted I need the information to be spaced or offset as per below sample.

Sheet1
HTML ABCDEF
1#AnimalDescription
21Dogfour legged fur ball
32Catother four legged
43Horsegsdfgdfgdbfb
54Snakedfgegergeg
65Ratlkjlmlbnm,
76Birdtshgjmgjkk

Sheet2
HTML ABCDEF
1#AnimalDescription
21
3Dog
4four legged fur ball
52
6Cat
7other four legged ...................

View Replies!   View Related
Copying Contents Of Columns Of Varying Lengths
I export data from another programme into excel and I wish to write a macro to copy the contents of columns G, H, I and J and paste it into columns A, B, C, D but starting at row 4. I then carry out various formatting tasks. I have written a macro that works for the copy and paste and the formatting but it only works when the data is the same length. The length of the data exported varies each time and so I am looking for some code that stops at a blank cell and copies the range above. I believe this uses xlup or xldown.

View Replies!   View Related
Summing/averaging A Column Of Varying Number Of Rows
how to code VB to where when data is sorted a formula can be placed at the bottom a column to sum or average the above data?

I have tried relational positioning e.g

each time a row is created, integer n5 = n5 + 1

Application.CutCopyMode = False
ActiveCell.Offset(rowoffset:=1, columnOffset:=12).Select
ActiveCell.FormulaR1C1 = "=sum(r[" - " & n5]c:r[-1]c)"
ActiveCell.Offset(rowoffset:=0, columnOffset:=7).Select
ActiveCell.FormulaR1C1 = "=average(r[" - " & n5]c:r[-1]c)"
Application.CutCopyMode = False
n2 = ActiveCell.Offset(rowoffset:=1, columnOffset:=-19).Address
n5 = 0

Where n5 is augmented each time a row is created then set to 0 to count the next list

View Replies!   View Related
Produce A Report In Specific Layout From Varying Data
My last post here was brilliant, I was very close to figuring the problem out myself (I'm learning) and the help I got here really was useful to help me finally get what i wanted to work.
Unfortunately, this request isn't so simple, as I haven't a clue what to even search the forum for to help me with this.

I have attached a simplified example to try and explain what i would like to happen.

Sheet1 holds information which is regularly updated, so the number of rows can vary, as the number of customers vary.

I want to produce a management report from this data, with a specific layout.
I have put the layout into Sheet2 - the cells highlighted in yellow are there simply as a reference to show the cell from which column would go where.

I produced the top report, by filling the cells with =and then clicking on the cell on sheet1 that I wanted displayed there.
I thought that if I then selected the table and filling down, it would produce the same layout table for all the other rows - but I was wrong.

Another problem is that, this report is needed to be entirely automatic, so I don't want to fettle filling down cells for the number of rows. I will need it to count the number of rows in Sheet1 and produce a report in the specific layout for each company in sheet 2.

It may be an option to use a button create the report, to save the report into a new workbook with automatic file name containing the date and also print a copy out automatically on the default printer.

I don't know how to go about this task at all, so I would really appreciate some help, even if it is pointers for what i should be searching the forum for!

View Replies!   View Related
SUMIF- Column Of Contract Numbers Of Varying Length
I have a column of contract numbers of varying length. I want to run a sumif of all of the values on my reference sheet that have the contract number beginning with the contract number in my listing. All of the the contract values in my reference sheet are very long. Since the numbers I am working with vary in length, i don't know how to match this string in the sumif function

I want something like this

=SUMIF('Reference Sheet!$A$5:$A$13410,LEFT("", LEN('Select Contracts'!$C6))='Select Contracts'!$C6,'FINALIZED DATA'!$H$5:$H$13410)

Is there a way to refer to the range in the sumif function,

View Replies!   View Related
Spread Annual Number By Month With Varying Inputs
I'm almost done with the budget at work and now I need to spread the annual figure by month. Initially I was thinking of spreading it evenly by 12 months, i.e. $12k annual budget = $1k per month.

However the boss doesn't want it that way. So I received inputs from certain managers as to when the bulk of the budget will be spent. Now I need to develop some type of formula so that once I input that amount let's say in June, then the rest of the month's will adjust accordingly.

For example, using the example above, if I manually input $6k in June, then the remaining $6k from the budget will be spread out evenly through the other 11 months. Is there some clever way to do this or is just a matter of multiple IF statements?

View Replies!   View Related
DSUM Formula With Varying Criteria Across Report Matrix
I currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).

View Replies!   View Related
Exclamation Collate Data From Varying Number Of Sheets To One Sheet
Have some code I have found from this site shown below. It works great however I need some help in editing it to change what it does. Basically the code currently takes all the data from set cells from all sheets after a set point and adds a new sheet and copies data from each of the sheets between this point and the new sheet and then pastes it to this final sheet.

What I wish to change is I already have a sheet which I will be using to analyse data from so how would the code look to be able to point to such a sheet within the same workbook? The sheet will be within the first 2 sheets of the workbook so from the code below you can see I have already set it to not include this sheet. I wont be needing a new sheet to be created at the end, I just want the data to paste to my analysis sheet which we can call "analysis".

View Replies!   View Related
Macro Code To Average Multiple List Of Varying Column Ranges
I have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following:
1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell.
2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data).
I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet.
So far, I am working on code to try to " find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated.
Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it.
I have attached a version of the file that displays how I want it to look.
The code I have thus far is:

Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

View Replies!   View Related
Flexibility- Allowing My Macro To Run In A More Global Environment With Varying Data-set Lengths
I'm having difficulty allowing my macro to run in a more global environment with varying data-set lengths.

For example:

Though the following "copy" scenario works for me when my data set is contained within cells B2:B35:
Range("A2:A35").Select
Selection.Copy

It does not allow me to work with a set of data that extends beyond this--say to B50 or B60. Is there any way to allow this command, and others, to be a bit more flexible with my data set lengths?

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved