Dynamic Cell Reference Under VBA
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...
If Shape = "Freeform 30" Then 'AR
Receiver = "kim.randy@companyX.com"
I want it to point to something like =Receivers!C13
How would I do this without hardcoding the data above?
View Complete Thread with Replies
Related Forum Messages:
Dynamic Cell Reference?
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?
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.
Dynamic Reference To A Cell
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
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.
Reference Last Cell In Dynamic Named Range
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:
My question is what formula do I use to reference the sum column for each row on the Det Est Sheet.
Dynamic Range Reference
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.
Udf Vb Looking To Define A Dynamic Absolute Reference
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.
myfield1 = ActiveCell.Column
Dim1 = Columns(myfield1).Range("a1").Value
SRCref2 = Dim1
myfield2 = ActiveCell.Row
Dim2 = Rows(myfield2).Range("a1").Value
SRCref3 = Dim2
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
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 -
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 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 Charting Via Dropdown Reference And Offset Function
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
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.
Circular Reference With Dynamic Named Range Used In Formula
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.
Cell Reference :: Formula To Reference New Cells
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.
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.
Cell Reference- Able To Reference Two Cells To The Left Even As More Cells Are Added
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.
Copy The Dynamic Auto-Sum Cell And Paste It To Another Cell
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.
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...
How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)
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?
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 Starting Point For Dynamic Named Ranges
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 can’t 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 doesn’t.
Look At Data From One Cell Reference Another Cell Then Display The Results From The Cell Next To It
I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.
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)
Tab 2 (Dynamic, changes each week)
So it would work as follows.
Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.
Weekly league rank table that shows position movements week by week
Can Excel calculate/show the actual movements of league positions?
Dynamic Data Validation :: Dynamic Lists
I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
Dynamic Cell Select
I created a very small macro in excel but it is not working exactly as I want to. I have few buttons which have a comment assigned to them. When I select a random cell and click the macro button it should add the comment that assigned by the button.
What I was able to create is when I click the button, it asks me for the cell number, this was the only workaround I could do.
Dynamic Input Cell
I am making a basic inventory tracking worksheet which consists of 2 sections. The first section has all the unassembled parts and quantities and the second shows the total assembled parts. I am trying to track unassembled parts and assembled parts simultaneously. I would like to have a cell that I can put a number in (which would represent the number of assembled products manufactured that day) and adjust the totals in both parts accordingly as they are made. For example, if I start the day off with 5 units of all the parts unassembled and manufacture 2 that day, I want to be able to put the number 2 in a cell, hit enter, and then have the unassembled inventory decrease by 2 (per part) and have the assembled inventory increase by 2.
Cell To Reference Just The Month From Another Date-formatted Cell
I'm trying to link two different budgets into a third spreadsheet. The final step I'm attempting to accomplish is have an if-and statement reference just the month from a date-formatted cell. One of my departments tracks their budget on a daily basis, i.e.
11/2/2009, 11/3/2009, etc. rather than just 11/2009. I want the if-and statement to return the budget from all days in a given month so i can apply the formula to the whole year, broken down monthly too. I saw a question similar to this one but didn't see any responses so I'm not sure if it can be done.
Obtain Cell Reference/address Base On Cell Value
I have this certain value "Y" in all the sheets. In the summary sheet, I would like to get the cell address for this value in a given sheet. I know that you can get cell address using CELL worksheet function. However, for that you need to know the reference to the cell, which is what I am interested in.
I tried Hlookup to get column #. But couldn't figure out how to convert the number to letter so that I can use MATCH to get row # and then finally, index or address function to what I am looking for.
If someone knows how to convert column # to column letter OR knows a better/easier way to obtain cell reference/address based on cell value,
Convert Cell Reference Text To Cell Formula
I am wanting to convert a cell reference text
to an actual cell reference
Manually I can go through each cell and click F2 + Enter and Excel automatically changes it.
I have tried recording a macro whereby I click through each cell with F2 + Enter but the VBA writes the actual formula "=$A$1" rather than the process. This does not work as the cell reference is variable.
I'm NOT wanting an external cell to convert it for me
because I am wanting to copy the answer to another independent spreadsheet
I'm NOT wanting to paste values
i.e. return the answer from cell $A$1
because I want the cell reference to remain within the cell.
Reference Cell X Rows/Columns From Static Cell
I have a worksheet where everything in column A links to a different workbook. Does anyone know if there is a formula that can then look at the cell reference in column A and take the value from a set row in that same column? For example:
The cell in column A2 is set to cell Q1 in a different workbook. I need a formula in cell C2 that will then bring in the value of cell Q64 in that same workbook. Therefore always bringing in the value of row 64 for whichever column the cell in column A is et to.
Reference Cell In Macro Using Row & Column Numbers From Cell
I'm trying to reference a location on a spreadsheet in a macro by using the values in two cells (the idea is to then paste to this location):
D2 is 17 (the row)
B4 is 2 (the column)
therefore the location is B17 (R17C2), but can I get this to work...? The values in these cells can change depending on selections made which is why I want to use the cell references rather than the absolute!
Dynamic Web Query Using Values In Cell
Hey guys i really need your help with this one.
Basically I have a web query. In this web query the url has a part which changes to direct it to a different page with similar formatted data.
These variables that change are all in column A of my excel sheet.
So say it was http://www.google.com/search?hl=en&q=*VARIABLE*
and Variable changes according to the cell value in A1, A2 etc etc.
Right so say i set up a macro to get the data from the website, and i do some stuff to this data e.g. cop and paste it to B1. I need it to automatically change the urls variable to that of the cells below and then do the copy and pasting to the cell below (B2) it copy and pasted before(B1). Then again it change (the variable at the end of the url) to the value of Cell A3 and after its obtained that data copy and paste it below B3
Im not great with vb, like i can understand it if its infront of me but at this stage i cant write it.
Im guessing though i have to use like a counter that increases makes the variable pick it from the cell below.
Oh and i need it to stop at say when the variable reaches cell A360.
Dynamic Linking To A Cell On Sheet
inthe above quoted formula last part "Sheet2!$A$2:$A$11" is the data for Jan in sheet2, if I need Feb results I need to change it to "Sheet2!$B$2:$B$11"
Is there a way to make it dynamic by linking this to a cell on sheet1? Example if sheet1 A1 cell contains name of month, by changing that the results should also change?
Using Cell Contents As Dynamic Value (sheet Name)
I have a multi-worksheet workbook.
In the first worksheet, called "Overall Totals", I have only two cells utilised (A1 and B1).
The first cell contains the name of one of the other worksheets. This may change, in order to reference other local worksheets.
The second cell should use the value contained in the first cell to go and get a particular piece of data from the other worksheets.
So, for example, if the first cell (A1) in "Overall Totals" contained the data "SheetName 1", and the data we wanted to get from that sheet would be cell F12 from that sheet, then I would expect the second cell (B1) in "Overall Totals" to say something like.
if I was hardcoding it. But I don't want to hardcode it, as the value in A1 could change, and I'd have to re-change lots of hardcoding.
So can I do something like....
Dynamic Path Based On Cell Value
I have VBA code with array formula that looks up some values from series of spreadsheets. Which spreadsheet to use depends on the values in another column.
How do I make the formula the same for the whole range as it will substitute the necessary path into itself?
here is an example:
here is a code for VBA:
Selection.FormulaArray = _
Selection.AutoFill Destination:=Range("I2:I535"), Type:=xlFillDefault
Dynamic Range When Starting Cell Not Known
I found dynamic range name across columns:
which expands across Columns in Row 1.
What if we don’t know range to be named in which row or column starts?
What will be a formula (assume range starts on cell named “BegRng”)?
Dynamic Range. Start & End Cell Not Known
I would like to create a dynamic range of numeric type in a single column. Only the precise position of one middle cell is known, let's say $A$20, but the range is supposed to extend above and below $A$20 and to finally include all adjunctive, positive and negative numbers in that very column.
Having read the ozgrid-resources on dynamic ranges let me experiment with the following line, however, it seems to include all entries in the column disregarding the format type or the fact that they should all be adjunctive (meaning that wherever there's a text entry it should be seen as the natural end of it): ....
Dynamic Hyperlink Address From Cell Value
I am hitting a brick wall on this one. I am trying to assign hyperlinks to objects based on the cell value. Here is my code that colors the objects that have the same names as in Column AO. I am trying to also add a hyperlink to those same objects with the cell value in the list. The problem is while a hyperlink is added, I can not get the value of the cell to show up in the hyperlink address.
Dim i As Integer
On Error Resume Next
LastRowa = Range("AO65536").End(xlUp).Row
For i = 2 To LastRowa
Labl1 = Range("AO" & i).Value
ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:=Labl1
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Visible = msoTrue
Cell Retains Value Derived From Dynamic Function.
Let's say I have three cells; A1, B1, C1. A1 = a random number (RandBetween 0 and 100) B1 = If(A1>99,1,0). I want to increment C1 everytime I refresh the randbetween via f9 so that the first time A1 returns a value of over 99, C1 picks up a value of 1 from B1 and then the next time I get a value of A1 over 99, C1 adds the value of 1 from B1 to its existing total, giving a total of 2 etc etc etc.
Essentially, what I want is: If A1>99,C1=C1+1. I can't work out how to get C1 to hold its value when I refresh the sheet. I realise it's probably pretty simple, but it would appear that so am I.
Formula To Return Values In A Dynamic Cell
I basically have this worksheet which has Cell D3 Being Dynamic. It puts the lowest cost supplier from columns H:W. If i change the prices in H:W it will bring up the lowest cost supplier in D3. Once D3 is chosen i want it to self populate F3 and G3 based off of what is in D3. This data should be pulled off from what is in the H:W columnns corresponding to the supplier in D3.
Clear Cell In Variable Dynamic Range
In my macro, NextRow is defined as the first empty row in a set of data (NextRow = Range("A1000").End(xlUp).Row + 1). How would I clear the contents of Columns A:H in NextRow in VBA, keeping in mind that NextRow is always different for each worksheet I'm working with?
Dynamic Named Ranges Based On Cell
I have just started diving into the vast world of dynamic ranges and it's a bit overwelming. I have a dB of sorts that a listbox in a userform fills off of. The dB will be driven by column A and B. Column B are names that are displayed in the listbox. Column A is the class that these names belong to (3 classes). I need to develop dynamically named ranges of Column B based off of Column A. In other words, I need a dynamically named range for all of class1, class2, and class3 in column A to display column B in the listbox on the userform. I have attached an example to clarify the example.
Formula Reference Cell Address In Cell
I have 2 sheets. In the first sheet, I have 3 columns. The first column contains transaction codes. The 2nd column contains the destination code (like a mapping cell where the value of the 3rd column would go but in a different sheet/2nd sheet) and the 3rd column contains the values for it. Would it be possible to reference a destination in a cell itself? Or should I just code it individually? I wouldnt want to reference each data one by one because the total would be 480 reference cells. Is there an easier way?