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
Receiver = "kim.randy@companyX.com"
End If
I want it to point to something like =Receivers!C13

How would I do this without hardcoding the data above?

ADVERTISEMENT

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

View 2 Replies View Related

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.

View 5 Replies View Related

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.

View 3 Replies View Related

Dynamic Cell Reference

Jul 21, 2006

I have a question about dynamic cell references that I cant 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 dont know the syntax for doing this or if it is even possible.

View 4 Replies View Related

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.

View 1 Replies View Related

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.

View 2 Replies View Related

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.

View 4 Replies View Related

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

View 1 Replies View Related

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.

View 10 Replies View Related

VBA / Dynamic Workbook Name Reference

Feb 6, 2014

I am using:

Code:
Dim Aname As String
Aname = ActiveWorkbook.ActiveSheet.Range("D2").Value & "Pricing"
Workbooks.Add
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

View 7 Replies View Related

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

View 1 Replies View Related

Macro For Dynamic Sheet Name Reference?

Jun 23, 2012

I've got the following code:

VB:
Application.DisplayAlerts = False
Worksheets("WildCard").Delete
Application.DisplayAlerts = True

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.

View 7 Replies View Related

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

View 3 Replies View Related

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.

View 8 Replies View Related

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.

View 6 Replies View Related

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.

View 2 Replies View Related

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

View 9 Replies View Related

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.

View 9 Replies View Related

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.

View 2 Replies View Related

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.

View 9 Replies View Related

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.

View 1 Replies View Related

Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

View 4 Replies View Related

Absolute Reference (cell Reference Behind The Table)

Mar 11, 2009

I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:

View 2 Replies View Related

Cell Reference :: Formula To Reference New Cells

Feb 15, 2010

I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.

EXAMPLE:

12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

View 14 Replies View Related

Convert Cell Reference To Worksheet Reference

Jul 20, 2014

I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:

Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say

AAB
ABC
CDE

And have a formula in column B that converts this to

=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)

I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?

View 2 Replies View Related

Reference To Cell Column Letter By Reference To Another Cell Value

Apr 25, 2014

Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.

View 9 Replies View Related

Autoupdate Cell Comment Based On Cell Input / Dynamic Cell Comments?

Aug 16, 2013

I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?

For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?

I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;

View 4 Replies View Related

Cell Reference- Able To Reference Two Cells To The Left Even As More Cells Are Added

Jun 11, 2008

I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C

As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.

I also need E (which has also shifted over one) to sum A,B,C, and C2.

Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.

View 9 Replies View Related

Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference

Aug 14, 2006

I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

View 3 Replies View Related

How To Match A Cell Data With A Range Of Cells And Return Cell Reference In Another Cell

Dec 12, 2012

i want to match a cell data with a range of cells and if matches return the cell reference in another cell

View 3 Replies View Related

Use Dynamic Formula Driven Text From Cell To Identify And Extract From Specific Cell?

Dec 21, 2013

I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).

So I made a formula to identify the exact cell # every month.

Example:

This month the exact cell is F255 in the "Refi" sheet.

So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have

VB:
="F" & (A1)

This outputs "F255" in B1, successfully identifying the target cell.

Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)

View 7 Replies View Related

Assign Active Cell In Macro With Dynamic Cell Choice

Dec 29, 2009

Before unloading a userform the range to select the active cell is set to

View 3 Replies View Related

Dynamic Range Based On Cell Between A Header And Footer Cell

Sep 2, 2008

I need to create a dynamic range based on cell between a header and footer cell. The header cell ( A8 ) will remain static, however, the footer cell starts at A10 and then will move down as rows are added.

View 9 Replies View Related

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

View 9 Replies View Related

Dynamic Cell Contents Based On An Other Numeric Cell Value.

Dec 3, 2009

cell A1 is equals to B1 because the cell C1 contains the number 1.
Now if i change the value of the C1 to 2 how can i make the A1 shows the value of the B2 ?

Just like. [A1]=B(C1-content)

View 3 Replies View Related

Copy The Dynamic Auto-Sum Cell And Paste It To Another Cell

Aug 23, 2009

2 Different worksheets to work with

The "Nursery" Worksheet
I already have code that puts the Auto-Sum amount below the last data row in the column named "Nursery Grand Total" in the Nursery Worksheet.

This Auto-Sum amount, however, will always be in a different row because the amount of rows generated from the report is always different as well, therefore, the Auto-Sum cell/row changes with that to be right below the last data row in the "Nursery Grand Total" Column.

I would like to copy (values only) the amount from this dynamically changing Auto-Sum cell and paste it into another worksheet named "Totals".

The "Totals" Worksheet
In my "Totals" worksheet, I have two columns.
"Master Total Description" and "Master Grand Totals".

In the "Master Total Description" column, I have a cell named "Nursery Grand Total" which is exactly the same name as the header row in the "Nursery" worksheet.

So,

In the "Nursery" worksheet/"Nursery Grand Total" column, I would like to copy the auto-sum amount

and paste it into....

the "Totals" worksheet/"Nursery Grand Total" row/"Master Grand Totals" column

Here are some pictures for reference...

View 9 Replies View Related

Look At Data From One Cell Reference Another Cell Then Display The Results From The Cell Next To It

Jun 19, 2008

I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.

Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:

Tab 1 (Never changes)
AB
Bob1
Jon2
Fred3

Tab 2 (Dynamic, changes each week)
AB
Jon
Fred
Bob

So it would work as follows.
Tab2 column B will take Tab2 column As data check Tab1 column A and display Tab1 column Bs result.

Problem 2

Weekly league rank table that shows position movements week by week
Example.

Week1
1Jon
2Bob
3Fred

Week2
1FredUp 2
2BobNot Moved
3JonDown 2

Can Excel calculate/show the actual movements of league positions?

View 9 Replies View Related

Dynamic Starting Point For Dynamic Named Ranges

Jun 21, 2009

I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I cant seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesnt.

View 2 Replies View Related

Looking For A Solution On How To Convert A Returned Cell Address To A Cell Reference

Dec 2, 2009

I have a formula (AL15) to determine the address of the last column with a value greater than 1. =COUNTIF(C16:AC16,">=1")+2. I then use this column value to aquire the Absolute row "15" and the determined column=(ADDRESS(15,AL15,4)). This returns to me a Column value and row value that I need to use as a refference. How can I convert this returned value to a reference.

View 2 Replies View Related

Change A Macro To From And Absolute Cell Reference To Selected Cell.

Feb 5, 2010

i am currently using the macro below to import text files into a spreadsheet. Currently, it begins the import in cell A1 which is what I recorded it to do. how do I change the code to begin the import on the active cell?

View 2 Replies View Related

ADVERTISEMENT