Referencing A Dynamic Range

Feb 16, 2010

1. I have a worksheet with subtotal and total row above all columns

I currently use SUBTOTAL(9, A6:A6000) and SUM(A6:A6000)

however the no of rows is dynamic based on imported data so I want the range end (i.e. A6000) to also be dynamic i.e. refer to the last cell with data in the column.

Something like SUBTOTAL(9, A6:LastCell in A)

2. I also use SUMPRODUCT to do calculations

e.g. SUMPRODUCT ((\$B\$6:\$B\$6000 = "ABC") * (\$A\$6:\$A\$6000))

again how can I make the sumproduct dynamically use all rows to the last active row.

3. I have tried just setting the second part of the range reference to a very large number but am worried that this slows down the calculation. Does it?

e.g. SUMPRODUCT((\$B\$6:\$B\$100000 = "ABC") * (\$A\$6:\$A\$100000))

Dynamic Referencing

Dec 1, 2009

I have a worksheet titled "Systems Estimate" that will dictate values on a second worksheet called "CSI Estimate". They're the same values in each cell and column, just in a different order row wise. I would like the values in each row in the A column in the "CSI Estimate" sheet to dictate columns B through W, with the values being referenced from the "Systems Estimate" sheet. I tried using an exact match with vlookup since the data isn't sorted, however it just returns the formula I enter, not a value.

Dynamic Table Referencing?

Mar 16, 2014

I am trying to pull data from different Tables within a workbook by typing the name of the referenced Table in a cell rather than leaving it static withing a VLookup formula.

So far I have this:
=VLOOKUP(\$B6,OctTable,MATCH(D\$4,\$D\$4:\$V\$4,0),0)

What would I do if I wanted to be able to dynamically change the "OctTable" portion with the name of another table which I type in call A6? I have researched as best possible and think that it might have something to do with INDEX, but I have failed to figure it out.

Referencing Cells With Dynamic Row Values?

Mar 2, 2014

I'm trying to reference text from one worksheet to another. The data is all in column A on the "Template" worksheet, however the row varies (ie starts at row 5, then 7 the 9 etc). The cell row is always +2 rows on the previous row, and column A.

What can I use to pull across the cells with the information that I want, onto the "Report" worksheet?

Gathering / Referencing Data (dynamic Table)

Jan 3, 2014

I'm attempting to list my data... I have a dynamic table that has the following columns:

number of countries
country 1
country 2
country 3

1
Egypt

3
USA
Egypt
Scotland

2
Scotland
USA

As you may have guessed, I want trends on these countries. That would be easier to do if I had a single "Country" columns but I have to work that way.

I would have wanted to work with a pivottable (because they're so "user-friendly" (not always though^^)) but I didn't find a way to do it.

The reason why I want to work with a pivottable is to be able to link my countries results to the rest of the table.. If that's not possible, I'd still want to be able to reference them and say "USA, egypt and scotland pop up 2 times"

I found the following formula (in E2):

Code:
=INDEX(Table1[country 1]:Table1[country 3];MOD(ROWS(E\$2:E2)-1;ROWS(Table1[country 1]:Table1[country 3]))+1;INT((ROWS(E\$2:E2)-1)/ROWS(Table1[country 1]:Table1[country 3]]))+1)

Which works but I have to manually click-drag that cell down to complete my list. I'd rather have it grow automatically if I have to work that way.

Excel 2010 :: Referencing Cells Containing Dynamic Data In Another Worksheet?

Jun 15, 2012

I have a a spreadsheet that pulls user information from our corporate address book. I would then like to copy that data to another worksheet for additional data scrubbing. The problem I am facing is that, I can get the reference to work the first time but if I pull new data down, the destination spreadsheet now shows #REF!

I've tried using the below formulas but they all wind up the same way. ' Results' is the name of the spreadsheet I am making the reference to which has the dynamic data in it.

=Results!#REF!
=INDIRECT("Results!\$B3")
=indirect (cell("results",B3)

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

Referencing Last Cell >0 In Range

Jul 10, 2009

I have a row with month titles. I manually fill this with data as the year progresses. I need to automatically use the last month updated in the row in a formula. i.e. theformula is in one cell and calculates on the last figure in the row e,g, data is in january (say C3) - when I input Feb data in C4, I want the formula to automatically reference the last complete cell (ignore january data and calculate on Feb data), and so on through the year.

Referencing Row Number From A Range?

Jun 6, 2014

i'm trying to set up a way to automatically add data labels to several series on a line chart with each series having data labels from a different row. i have managed to accomplish all of this except the last part. i can't find a way to reference different rows within a range.

let's say your code looked like this

Dim DataRange As Range
Set DataRange = Range("A1" , "Z50")

is there any way to reference a specific row from that?

[Code] .....

What I'm trying to do is expand the range from b1:d1 to something like b1:d100 but then when i get to "For Each SingleCell In FilmList" i can get it so that it's only doing referencing a specific row from that list that i can controlupdate with RowCounter .

Referencing Range With Variable

Sep 11, 2009

need to solve the problems in this

Referencing Named Range Without Using Worksheet Name

Aug 13, 2014

I want to export data from a set of named ranges in workbook x to another set of ranges in workbook y. The names of the ranges to be exported from and to are spesified in a table "Table_Export". Problem is I do not want to spesify in what sheets these respective ranges are located, seeing that I do not know the actual sheet names. But since the ranges are global it should somehow be possible to reference them in vba without referencing the respective worksheet?

The relevant code can be found below where either of the two 'commented lines within the if statement are what I want to accomplish.

Dim x As Workbook
Dim y As Workbook
Set x = ThisWorkbook
Set y = Workbooks.Open(Range("Export_to").Value)
Dim export_control As ListObject

[Code] ....

Referencing Range In Specific Worksheet?

Mar 19, 2014

I've tested and I'm getting errors trying to reference a range in a specific sheet.

I want to sum a range for further calculation and can get this to work perfectly if I don't specify a sheet - but it sums the range in the wrong sheet as the code is within a With statement range:

[Code].....

I've checked that wsSumm contains the 'Summary' worksheet value and that's all fine, I've also tested the following:

[Code] ....

I've also tried replacing wsSumm with Worksheets("Summary") and Worksheets(1), but get exactly the same results, so no it's not a fault with the worksheet variable. Also tried using Application.WorksheetFunction.Sum, but again exactly the same results.

So, why I can't sum the range on the specified worksheet? Is is something to do with being inside a With statement for another worksheet - though I've not come across problems with this before...

Referencing Range With Cells Property?

Jan 8, 2014

I have been stuck in this piece of code. Depending on what I try, I get instances of 1004 run time error.

When this code is running, the sheet is not the active one. I tried to activate as well, with no success.

Code:
Sub WriteRankData(Region As String)
Dim i As Integer, z As Long
With shCurrentYear
For i = 9 To 41 Step 4
If .Cells(1, i) = Region Then

[code]....

Referencing A Cell's Contents As Range()

May 14, 2007

I'm trying to figure out the best way to reference a cell's contents as a range in VBA code. So essentially it would be simple like this (except of course this doesn't work)... Range("cell(contents,A1)").Select

I always look in my CD of old forum posts, Mr. Excel articles, Mr. Excel books I have etc. before I break down and ask.

Subscript Out Of Range Referencing A Workbook

Jul 30, 2007

I've written somewhat complex code (well complex by my standards) on my own computer and I have tried to put the project onto another computer so that they can use it. The code involves opening a workbook and then saving it under the variable name strName1. Then it activates another workbook and puts information into that workbook from the strName1 workbook. The strName1 workbook is open this whole time. However, I get an out of range error when trying to do this part (the code is shown below).

ActiveCell = Workbooks(strName1). Sheets("Basic Overview").Range("B1").Value

I am confused because this works alright on my computer, I have copied the root directories for the file exactly as it is on my computer and when debugging the code I can see that it has picked up the variable strName1 correctly (so this isn't the problem). I've also tried writing the entire root directory in insetad of just Workbooks(strName1) but it still says its out of range.

Referencing Range Of Cells Based On Value In Another Cell?

Jun 28, 2014

I have multiple sets of data that i need to reference based on the value in another cell. In this instance I am importing data to Excel from an outside source, this data is not consistent each time I import it and therefore based on the values that are imported I need to reference different cell ranges. Here is an example:

Import Data
A B
Alex 1
Ben 2
April 2
Harry 3

Data Sets
Alex Data Range
1 2 3
Refine Help Token
Cover Safe Coin
Simple Aware Change

Ben Data Range
1 2 3
Sorry Away Candy
Happy Home Soda

My problem is that based on the name in column 1 of the import data I need to reference that data range so I obtain the correct column of Alex's data range with a random chance of any item in the column to be selected. Currently my formula looks like this: =INDEX(CELL("contents",A1),RANDBETWEEN(1,3),B1) and it is returning #VALUE, however when I put the Range Name "ALEX" in in place of the "CELL" formula it works properly the problem is I am not able to put the correct name in for each row.

Referencing A Named Range Or Sheet Dynamically?

Apr 19, 2013

I have a challenge I'm trying to surmount. I have an excel sheet in which I want to be able to select Zone codes from a drop down, click on a link "search" which takes me to a place in the document that has been named according to the Zone code i pick in the drop-down. I want to achieve this using only formulas as i'm distributing the file to a wide audience who are not versed enough with computers to know how to enable macros.

Referencing Rows Using Variable And Intersect Range

Jan 18, 2009

I am trying to clear the contents in a rows reference by a variable (rownum) using the

Referencing Sheet Using Cell Notation In Range

Aug 12, 2012

repTot = Application.WorksheetFunction.Sum(Worksheets("Revenue").Range(Cells(xx, 65), Cells(xx, 65 + moNum)))

I am trying to debug the above line of code (moNum is just a variable the tells me how many months to extend the range to the right inorder to get year to date sums).

I have gotten to the point that it will run error free if "Revenue" is the Active worksheet when I run the line, but if I am on any other sheet it throws an "Run=time '1004' Application-defined or object-defined error" error. And if I change the range from cell notation to Column-Row, the issue goes away as well.

Sumif Referencing Range Based On Choice From Dropdown?

Mar 8, 2014

I have several different named ranges that I want to sum and the user chooses the range from a drop down box. I want to select the range based on that choice but when I reference the cell that contains the reference to the selected range I get a result ** 0, but no error. I realize I could use several nested "If" statements but I was looking for a more streamlined approach.

here is the formula:

=SUMIF(Regions,B4,worksheet!K13) K13 = the name ** the selected range.

if I put the name ** the range directly into the formula I get the correct result.

Conditional Formatting With Formula Referencing Range In Different Cell

Oct 21, 2008

I want to set a conditional format to cell A1 with a value in cell P1 When the value in P1 is between 0 and 10 the conditional value in P1 should be set.

Note

cell A1 is also set with a conditional format to put a border around it when there's a value in A1 (cellvalue is not equal to " ")
So the formula referencing P1 would be a second condition.

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).....

Macro Referencing Named Range In Worksheets Private Module

Sep 12, 2006

I'm having trouble calling a defined range within a VLOOKUP function in VBA. If the named range is located on the same sheet within which you are running the macro, everything runs fine and all is well in the world.

However, after I relocated the range to a separate sheet (a 'SourceData' sheet to tidy up the user interface sheet), I was getting the following error message:

Method 'Range' of object '_Worksheet' failed

The name is correctly defined - Range("DaysInYear").Select still picks up the correct selection - it's just the VLOOKUP will no longer function correctly.

Here is part of the macro's
For I = 1 To NumberOfDays
Range("A1").Value = DateAdd("d", -(I - 1), EndDate)
If Application.VLookup(Range("A1"), Range("DaysInYear"), 3, False) = 1 Then
If Application.VLookup(Range("A1"), Range("DaysInYear"), 4, False) = 0 Then
ActualNumber = ActualNumber + 1
End If
End If
Next I

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.

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.

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.

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?