Dynamic Range Reference

Apr 22, 2008

I have a sumproduct function that is aimed at a big block of data. Is there a way that I can write the function to where it will always find the last row of the data set and adjust accordingly?

For example if right now the range is A1:G2000 and next month I add 20 rows of data I want the sumproduct ranges to automatically adjust to A1:G2020.

How To Count Range Relative To Dynamic Reference

Apr 1, 2014

I am trying to create a formula to count a range relative to a dynamic reference (at least I think that's you would phrase it).

I thought I was on to a winner with this:

Formula:

[Code] .....

But it doesn't seem to accept the : as a legal operator.

I suspect the answer is to use SUMPRODUCT somehow.

Reference Last Cell In Dynamic Named Range

Nov 14, 2006

In the attached spreadsheet I am trying to always reference the sum column from Qty in Det Est.

This is because the number of roads changes for each project.

I created a dynamic range name surfacing with the following formula:
=offset(qty!\$a\$1,0,0,counta(qty!\$A\$A), counta(qty!\$1\$1)).

My question is what formula do I use to reference the sum column for each row on the Det Est Sheet.

Circular Reference With Dynamic Named Range Used In Formula

Aug 13, 2008

I have a problem creating a circular reference with dynamic ranges. I have searched already but can't find the specifc problem. I have a spreadsheet where I use vba code to insert extra columns. Within the worksheet there are sum calculations at the end of each row. When I add a new column within the table I would like the sum calculation to expand to account for this extra column.

Now, I thought that dynamic ranges would be the way to go but the only way I can see to do it creates a circular reference as the CountA function trys to count the cell with the sum function in it.

Dynamic Range For Pivot Table :: Error : Reference Not Valid

Apr 26, 2009

I am trying to create a pivot table on a new sheet names as 4x4

I am getting following error:

Error : 1004
Reference Not Valid

I am not sure where is the problem. I have generated this code by using macro recorder and just changed the source and destination of the pivot generation code.

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.

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

Dynamic Cell Reference?

Apr 17, 2014

I'm trying to build a dynamic formula where a cell reference shifts one column over when a certain percentage changes. It's hard to explain, but the attached workbook should make it clear what I'm trying to accomplish.

SampleWorkbook.xlsx

Dynamic Reference To A Cell

Mar 3, 2009

I need a dynamic formula which if hard-coded would be something like =(tabname!A7).
But instead of A, I want a calculated value such as (\$C\$2*12+1). My columns are numbered, so I want to say "the 26th column along", "the 37th along" etc, as C changes. I tried the index formula but couldn't get it to work, assuming it is even the right one.

Dynamic Cell Reference?

Oct 19, 2009

Q: How can I point excel to the begining value cell if I know the ending value cell and the nubmer of cells to move to get there?

More details:

I have an investing sheet that I need to calculate the CAGR (compound annual growth rate). The formula is (ending value/begining value)^(1/number of years)-1

The number of years can vary but the ending value will aways be in the same cell. I am using a count to see how many years have been entered.

VBA / Dynamic Workbook Name Reference

Feb 6, 2014

I am using:

Code:
Dim Aname As String
Aname = ActiveWorkbook.ActiveSheet.Range("D2").Value & "Pricing"
ActiveWorkbook.SaveAs Filename:=Aname & ".xls"

to create a new workbook. I need to copy 2 columns from the original workbook/worksheet to the one being created though. I can get the the original easy enough, but then getting back to the newly created book is proving difficult.

(the 2 columns being copied are filtered to what is needed and I have used the below to copy it, yes I know that is 3 rows but I couldnt get it to copy just E and G at the same time to the lastrow without including F)

Code:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("E2:G" & LastRow)
.Select.copy
End With

Sum Of Values With Dynamic Reference

Mar 26, 2014

The values in Cells D1, D2 and D3 are user inputs.

A formula in B16 should sum values in database (ie sum of B6 to B11)

Example :

A
B
C
D
E

1
Enter the column no
B

2
Enter the start row no
6

[code]......

Result
149

Dynamic Cell Reference Under VBA

Feb 26, 2007

The original worksheet will have a sheet of data, but I want the user to be able to insert a row of data into this sheet and have the original values still referenced in the macro.

I do not believe the range function will work, because the relative cell positions are what are changing. Also, the offset command would not work, because the positions are not absolute to the active cell. Correct me if I am wrong...

Snippet of
If Shape = "Freeform 30" Then 'AR
End If
I want it to point to something like =Receivers!C13

How would I do this without hardcoding the data above?

Dynamic Cell Reference

Jul 21, 2006

I have a question about dynamic cell references that I canīt find any info about anywhere else. This is what I want to do: I need to create a sentence that changes depending on user choices. Example:

Cell A1 = Sheet!AX
Displayed text Dear Mr. "name"

Where the X is a number that will change depending on other things. I would love to do something like: =Sheet!A(E1). Where the value in E1 is for example 4. The result would then be =Sheet!A4 and if I changed the value in E1 the reference =Sheet!A(E1) would also change. I donīt know the syntax for doing this or if it is even possible.

Macro For Dynamic Sheet Name Reference?

Jun 23, 2012

I've got the following code:

VB:
Worksheets("WildCard").Delete

I'd like the "WildCard" to pull from a value in a cell whereby I can run a button macro that, when pushed, deletes the tab/worksheet named "WildCard". For instance, if I wanted to delete "Sheet3" I would type "Sheet3" into, say, cell C3 and then press the button to run the macro and "Sheet3" would be deleted. How to I refer the macro to cell C3? I currently have a macro that inserts/names a sheet based upon a cell reference and macro button. I'd like one that can delete in the same way.

Dynamic Reference Text Within Formula

Dec 18, 2013

I would like to be able to write a formula which references text within a cell and the formula automatically changes when the text changes. Example is attached.

Current formula is

=IF(E2="A",F2-A2,IF(E2="B",F2-B2,IF(E2="C",F2-C2,F2-D2)))

Is it possible to write the formula so if I change "A" in A1 to "Z" the formula will change as well? Or the formula will at least always reference the text within the cell, not necessarily the text that exists at the formula's conception?

Example.xlsx

VBA Code To Reference Dynamic Cell Value

Dec 22, 2011

I am looking to start writing a code that will a row # from a dynamic cell within the workbook and use that row # to select the row and delete it.

Example:
In Cell G5=8

I would like the VBA code to see 8 as row 8 and delete row 8.

Row 8 is determined by looking up a name and finding which row it is in and returning the row number.

Udf Vb Looking To Define A Dynamic Absolute Reference

Mar 13, 2007

I'm trying to create a user defined function to drop into dblookup functions that will always reference the first cell in an associated column and the first cell in the row of an associated column.

I came up with the following, however the problem is that the udf always returns the values for the active cell's, row and columns.

Function SRCref2()
Application.Volatile

myfield1 = ActiveCell.Column
Dim1 = Columns(myfield1).Range("a1").Value
SRCref2 = Dim1
End Function

Function SRCref3()
Application.Volatile

myfield2 = ActiveCell.Row
Dim2 = Rows(myfield2).Range("a1").Value
SRCref3 = Dim2

End Function

I would nest these in a formula such as "=srcsum(srcref2(), srcref3())".
Ideally I could then use this same funtion anywhere instead of having to use and worry about the accuracy of "=srcsum(\$a\$2,\$b\$1)"."=srcsum(\$a\$3,\$b\$1)"."=srcsum(\$a\$4,\$b\$5)".etc.

It seems like there should be a current cell method or something but I can't find it and I'm not smart enough it seems to use vb to designate some kind of floating intersect as a base for a row and column reference.

Copying Formula - Dynamic Worksheet Reference

Jan 22, 2010

Is it possible to copy a formula and have the worksheet name increase incrementally instead of the cell reference? Example: In column A, I have the following -

=TDI_SR_F_1!\$G\$1
=TDI_SR_F_1!\$G\$2
=TDI_SR_F_1!\$G\$3

I want to copy this across to column B, but instead of just copying that exact foruma across I am wanting a formula that will update the worksheet reference to:

=TDI_SR_F_2!\$G\$1
=TDI_SR_F_2!\$G\$2
=TDI_SR_F_2!\$G\$3

TDI_SR_F_2 is the worksheet after TDI_SR_F_1. NB - I cant use find and replace function as it will change the '1' which is part of the cell reference (\$G\$1) to a 2 as well. I know I can use TDI_SR_F_3 as a search string, but the process is going to be extremely tedious, i.e. there are literally over 100 rows I need to do the find and replace on.

Dynamic Cell Reference In Indirect Formula

Oct 11, 2011

I need making a dynamic cell reference in an indirect formula, which looks at a different workbook.

The other workbook is called: ESF.xlsx
The sheet name in the other workbook is defined in Cell B2
The first cell i need to pull across is B115.

In cell B8, This is what i have so far:

=INDIRECT("[ESF.xlsx]"&B2&"!"&"B115")

How do i make it so that i can copy this formula quickly, but so only the row and column reference change?

Like in C8 i would want it to look at cell C115, In C9, id want it to look at C116 etc.

External Workbook Link With Dynamic Cell Reference

Feb 24, 2014

I currently have a file that is linked to an external workbook that is referencing a certain cell from a certain tab. The below is what Column A & B look like right now. What I'm wanting to know is is there a way to have the formula in Column B automatically reference the name in Column A (which is the tab name in the external workbook) instead of having to manually change the formula when the value in Column A changes?

In other words, I want everything in the external reference formula to remain the same except have the person's name change dynamically...

Mary
='X:Anderson CorporationEmployees[Work Hours]Mary'!\$F\$42

Joe
='X:Anderson CorporationEmployees[Work Hours]Joe'!\$F\$42

Frank
='X:Anderson CorporationEmployees[Work Hours]Frank'!\$F\$42

Michelle
='X:Anderson CorporationEmployees[Work Hours]Michelle'!\$F\$42

Sam
='X:Anderson CorporationEmployees[Work Hours]Sam'!\$F\$42

Dynamic Charting Via Dropdown Reference And Offset Function

May 18, 2007

i have 2 worksheets. one with portfolio stock data, and a second that i would like to use for dynamic charting. the chart worksheet has a drop down menu that chages a refernce cell to a number corresponding to the collumn in the portfolio worksheet for the chosen ticker

i would like to make a dynamic chart that updates the data ranges based on something like this: ....

Dynamic SumIF Formulas That Reference Changing Codes

Dec 1, 2007

I have a list of codes from which I'm trying to extract all unique entries.

For example, col A has 001, 001, 002, 003, 003, 003, 004, ...

I'm trying to create a report template with formulas that reference col A and return a single unique entry for each code so that the new column has only 001, 002, 003, 004.

Essentially, the codes reference customers. A customer can have multiple purchases -- but I'm trying to create a report in which I can utilize SumIf formulas to Sum the purchases for each customer and represent those purchases on a single line per customer code.

The catch is that the purchases change monthly (i.e., next month the purchases may be 001, 002, 002, 002, 004) so my report template needs to have the flexibility to adapt without me rewriting the SumIf formulas and return SumIf results for only customers 001, 002, 004. Whereas in the prior month, the report returned SumIf results for customers 001, 002, 003, 004.

How To Reference Cells Value To Define Range Reference

Jul 14, 2014

Is it possible to reference a cells value to define a range reference?

[Code] ......

I am trying to define the row value in the range reference with a value in a secondary cell?

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.

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.