# Dynamic Range 2003: Run Calculations On This Range

Nov 5, 2009

I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.

## Excel 2003 :: Dynamic Range For Pivot Table

Jan 21, 2013

How do I create a Named Range that is dynamic (I think using the OFFSET function) that automatically expands and contracts as the rows and columns of my data changes?

Next step, I then want to use that Named Range as the source of the data for a PivotTable.

Yes, I know starting in Excel 2007 you can use Tables and don't need to create a dynamic named range. That is not an option at this time.

## Excel 2003 :: Dynamic Named Range Of Non-contiguous Cells Suitable For Chart Series Reference

Nov 11, 2012

I am running Excel 2003 on a Win7 system.

Here is my situation:

Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.

In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter

=--(AND(\$AX8>\$AA\$4,\$Y8>0))

and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.

Cell \$AA\$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.

Here's the first hard part:

For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.

Here's the 2nd hard part:

Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.

The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.

Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.

I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.

## Excel 2003 :: Auto-populate Columns From Range 1 And Continue With Range 2

Jan 27, 2013

I am trying to automate a process where a series of numbers would get populated according the range values. Also I am trying to get the automation to pick up the next range when finished with first one and continue with the task.

Here's what I have as start info and where I want to get to.

Sheet1
A
B

1
Ranges

[Code] ......

Excel 2003

## Dynamic Named Range Sort Including Cells Outside Defined Range

Apr 3, 2008

Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!\$A\$2,0,0, COUNTA(DataSource!\$A:\$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

## Creating Named Range Taking Avg To Date Of Dynamic Range

Jul 15, 2014

I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.

## Dynamic Range For Pivot Table :: Error : Range Is Invalid

Oct 28, 2009

I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:

=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))

I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.

## Convert Dynamic Range To Static Named Range

Sep 13, 2007

My searches have not produced anything that I could apply to this situation.

I'm trying to write VBA that would:

1. Search a Workbook for Dynamic Ranges.

2. When a Dynamic Range is found the code would:

A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name \$Column\$Row:\$Column\$Row"

3. Save Changes.

4. Close File.

My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.

## Set Range For Calculations Without VBA?

Jul 11, 2012

Is it possible to have an input box for a range of calculations and then in my formulas set the range to anchor + variable ?

Something roughly like:

Range = 20

Product( F13:F & Range_Variable_Cell_Value)

## Dynamic Range Used Named Cell Range

Jul 7, 2014

Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.

Range("DataSummary),(??,??) doesn't work.

## Set Range Variable To Growing Dynamic Range

Mar 6, 2008

I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet �Databaseform� however when I copied the code to my master spreadsheet �Paul_PartLocDBCombo� it does not work, I get the error:

Method � range� of object �_worksheet� failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))

Meaning this part is incorrect but I don�t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.

## Evaluate() For Loop Less Range Calculations

Feb 27, 2010

I'm trying to get my head around using this syntax for EVALUATE() to allow an entire range of values to be changed all at once instead of via loop.

This macro works:

Sub TruncateValuesInSelection()
'JBeaucaire 2/23/2010
'Select a range and then run the macro to truncate values without looping
Dim Cell As Range

With Selection.Cells
.Value = Evaluate("IF(ROW(1:" & Selection.Cells.Count & "),TRUNC(" & .Address & ",2))")
End With.........

## How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

## Locating Next Similar Condition In A Range And Performing Calculations

May 11, 2007

I have a set of data with names in col A, Currency in col B, Amount in Col C. I want to convert all the amounts in NON-USD based on exchange rate I input daily into column D.

How do i search down the row for the next name that is denominated in GBP / YEN etc and keep converting until I dont find any more other currencies in it. I tried to improvise on the finding nth but couldnt get anything out of it. Have attached a spreadsheet.

## Price List Lookups And Additonal Calculations For Out Of Range Values

May 11, 2009

The sheet has a price list (I attached the sheet). its a width x height(drop) format. If width or height <= minimum width/height then use the minimum listed. if width or height > minimum <=maximum then lookup in table next heightest value. here is the complication. any oversized items are priced as roundup((size -biggest size) / (biggest - second biggest size),0) * ( price of biggest-price of second biggest). so if my widths are

4600 4700 4800
10 25 35

and I am pricing 5050 I would do :-

calculate howmuch its oversize

5050-4800 = 250

Calculate the difference in the last 2 sizes

4800-4700 = 100

Calculate the rounded up multiples

250/100=2.5 rounded up = 3...........

## Run VBA Macros In Specific Order, Turn Off Calculations & Force Calculation Of Range

Apr 22, 2009

I have a VBA method that goes thru and calculates a bunch of stuff and puts this calculated info into a static data structure. This method is called 'setProjectInfo()'. This method is reliant on values on my worksheets, So as the user makes changes to the worksheet, I want setProjectInfo() to get called so it refreshes the data structure with updated information. I then have a bunch of helper methods to access different parts of this data structure. These helper methods are used in the formulae of a bunch of cells on a worksheet (ie '=getPercentComplete(period)', etc, etc).

This is all working well except for one thing. Sometimes the helper methods get called BEFORE setProjectInfo() gets called.. So, the helper methods return stale information. I can only get around this by manually recalculating the spreadsheet again. setProjectInfo() does enough stuff and takes a long enough time that I don't want to have to call it at the top of each helper method.

I've put the call to setProjectInfo() in a cell formula at the top of the my spreadsheet in hopes that Excel would know enough to call it first.. But that does not seem to work. Net, how can I get VBA to always call setProjectInfo() BEFORE it calls any other VBA methods?

## Adding Columns With Manual Calculations To Dynamic Pivot Table

May 8, 2013

I have a problem as I need to present data with different data sources. I'd like the calculations to be as "automatic" as possible. The data consists of countries and regions and their sales pipe. The pivot table doesn't give me all the data that I need to present so I need to add extra columns with calculations and data from other data sources.

The problem I meet is that the 'total row' within each group needs to be reflected in my columns as well (F:G) It works as long as the data I've imported has the same amount of States within each group of Contry but if the number of states differs with the newly imported data - my "manual" columns are wrong.

(A;B;C;D;E;F;G;H)
Country;State; Red; Amber; Green; Quota; Total PV; Coverage;

I've created a Pivot table on the first 5 columns [Country; ...; Risk] (A:E)

The following columns are manually entered based on the pivot (F:H)

Quota (data source from another sheet)
Total PV (a sum of Red; Amber; Green in the Pivot)
Coverage (returns % of TotalPV/Quota)

For each group Quota and Total PV needs to summarize the above data within the group. I've 'hard coded it' today (SUM(F3:F5)) - F3:F5 can as well be F3:F8 or F3:F4 depending on the imported data. I have 8 different Countries (groups) with different amounts of States and 3 different sheets for each Region of countries so I need this summary to be automatic based on what group (Country) it belongs to. How can I make the calculation different so that it's dynamic as well as the Pivot table? Today it's not dynamic and it needs my 'hands on'.

Quota =IF(ISNUMBER(SEARCH("total";A6)); SUM(F3:F5); IF(ISNA(VLOOKUP(B6;Quota!B:F;3; FALSE));" ";
IF(VLOOKUP(B6;Quota!B:F;3; FALSE)=0; " ";(VLOOKUP(B6;Quota!B:F;3; FALSE))))).

## Copying Range Of Cells Under A Dynamic Range Of Cells

Jul 30, 2006

I got a range of data on sheet2, size changes everyday (dynamic) And in sheet1. I got a range of data and the size changes everyday as well. I need to copy the range in sheet2 to sheet1. The position would be at the cell after the last data in sheet1. e.g.

sheet1 got 105 data
I need to paste data of sheet2 start of row106 in sheet1

## Dynamic Name Range

Jun 19, 2009

I have this macro named ABC to bring the name ranges to another worksheet. The name ranges are already saved in the workbook with the below name range. I used "***" so that this formular can go to the last cell that has "***" on A column and it worked untill someone typed "(xxx)" and the range now stops right before the "(xxx)"

=OFFSET(Storage_Team2!\$A\$1,0,0,MATCH("***",Storage_Team2!\$A:\$A,-1),37)

What would be the best to create the range according to the specific words in a column?

## Dynamic Range Within VBA

Jul 9, 2006

i am coloring cells:

Sheets("MySheet").Range("a1:f200").Interior.ColorIndex = 5

i want a dynamic range, so something that would look like this as an excel function:

=OFFSET('MySheet'!\$A\$1,0,0, COUNTA('MySheet'!\$A:\$A),6)

i want the equivalent of this programmed (hard coded) into vba

i don't want to reference to a defined name with vba, because the user can delete that.

## Sum Dynamic Range

Jul 15, 2007

I wonder if there is a way to Sum the total of a Dynamic Range so that the summary will be presented in the upper Cell of the summed column?

The reason is to anable FREEZING of the first row (display the totals all the time even when scrolling down) as the list will get very long after a while.

## SUM Dynamic Range ...

Jun 24, 2008

I am looking to sum a dynamic range and can't quite figure it out. The data expands down rows every week, and I want the sum formula (the final row) at the bottom to include the newly updated rows. I can't dynamically name the numbers to be summed because the dynamic named range will include the sum number and create a circular reference. For example:

5
4
3
2
14

the bottom row (14) is the sum. Next week, my numbers are

5
4
3
2
5
19

I have a macro that will automatically put the sum formula in the correct row and column, but can't quite figure out the formula.

## Loop Through Of Sheets And Execute Calculations Based On Dynamic Sheet References

Jun 30, 2014

Is it possible to loop through a list of sheets and execute some calculations that have sheet references from a different list of worksheets? For example, you have a list (list 1) of your worksheets, which will be the destinations of the calculations, and you have a second list (list 2) of worksheets that the calculations are based on. So, lets say there are sheet1 and sheet2 in list 1, and sheetA and sheetB in list 2. The calculations based on sheetA would appear in sheet1, and calculations based on sheetB would appear in sheet2. I thought the code would look something like this:

Code:
Sub LoopthroughWorksheets()
Dim sheet_name As Range
Dim sheet_name2 As Range
Set sheet_name2 = Sheets("WS").Range("F:F")

[Code] ......

I'm getting a "Run-time error '1004: Application-defined or object defined error" at this line:

Code:
.Range("K1") = .Range("sheet_name2.Value!A14").Value

## Excel 2003 :: Copying Range Of Cells From One XLS File To Another

Nov 5, 2012

I'm using Excel 2003. I've got two different .XLS files, each with multiple sheets.

I'm trying to create a macro which will copy a range of cells from one sheet on one .XLS file (which is closed) to a specific place on a specific sheet on the current .XLS file (which is open).

So for the sake of argument:

I've got two Excel files: C:ApplesOldFile.xls and C:OrangesNewFile.xls

OldFile.xls is closed -- NewFile.xls is open and in front of me.

I'm trying to copy the data in ranges B6:C41 and F6:F41 from Sheet2 in OldFile.xls to the same ranges on Sheet6 in NewFile.xls. There are no formulas in these cells -- just data (numbers).

I keep getting error messages, failures to copy to clipboard, etc.

## Excel 2003 :: How To Use Countif For Multiple Range And Criteria

Jan 1, 2014

How to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs

## Excel 2003 :: Adding Values If Column Next To It Is In Certain Range

Sep 9, 2013

I have this code working fine in excel 2007 but I need to get it working in excel 2003.

=0.02*(_xlfn.SUMIFS(\$D\$2:\$D\$98,\$C\$2:\$C\$98,">="&I5,\$C\$2:\$C\$98,"<="&I4))

I have attached a test document to show what I'd like to do. It is basically adding values if the column next to it is in a certain range.

counting columns in Excel 2003.xls

## Excel 2003 :: How To Pass A Parameter In The Input Range

Mar 21, 2014

I have a combobox in a excell sheet. It is possible to pass a parameter in the input range instead of Parm!\$B\$1:\$B\$10

## Excel 2003 :: VBA To Add Worksheets Named For Items In Range?

Jan 17, 2012

I some code that reduces a long list of dates from one source of data (in its own worksheet) down to a unique list of dates (on another worksheet). I need to create a new worksheet for each date in the range (named for the date), and then allocate each line item for that date in the source data into its repsective worksheet.

I'm using Excel 2003.

## Excel 2003 :: Count Where Value In One Cell Range But Blank In Another?

Nov 21, 2013

I am trying to perform a count against two ranges of data.Both ranges contain contain values (dates in my case).I am trying to perform a count where cells in column A have data but cells in column B do not.

Is there a way this can be done in Excel 2003? I have tried numerous COUNT and SUMPRODUCT queries but have not found a way to get this to work.

## Excel 2003 :: Sum Data Depending On Week Range

Jun 26, 2014

Objective: I have a financial spreadsheet, which i want to bring to my front sheet the sum of data in between 2 week ranges. For example, if I select week 26 and 52 then all the relevant data will be summed into the relevant cell on my front sheet.

Current Technique: I have on my "data" spreadsheet my columns in B1 down listing Week 1-52 and then the cell headings in B2 onwards. I have created a summary table which currently shows the 4 quarters of the year (1-13, etc) and I am using helper columns to pull this data onto my "front sheet", depending what selection is made on the drop down e.g. 1-13, then all summary data will be dragged onto my front sheet.

On the front sheet, in each cell I need information to be displayed I use the following formula;

"=IF(ISERROR(INDEX(Data!\$C\$56:\$BF\$60,Data!\$BI56,COLUMNS('Front Sheet'!I13:J13)))"

As you can see the quarter data is useful, but a more flexible date range would be more useful to sum data between any selected week.