# Absolute Reference Using Variables In Formula

Dec 1, 2006
I have written some VBA code that is functional, but yields a formula that is somewhat confusing to the user.

I have two string variables called TippityTop and Anchor, both of which refer to specific cell addresses in the R1C1 style.

I attempted to define other variables that reference cells that are offset from TippityTop and Anchor without using the ActiveCell route. Try as I might, I could not produce the cell addresses that I wanted these new variables to reference.

I consulted many Excel Help screens and referred to a book on Excel VBA, but I finally took another route.

I now have:

ActiveCell.FormulaR1C1 = _

"=IF(AND(ISNA(MATCH(RC[-2],OFFSET(" & TippityTop & ",1,-2):OFFSET(" & Anchor & ",-2,-2),0)>0),ROUND(RC[-1],2)0),""DOV not in Treasury"","""")"

The problem is that this code yields this formula in Excel:

=IF(AND(ISNA(MATCH(L587,OFFSET($H$585,1,-2):OFFSET($H$798,-2,-2),0)>0),ROUND(M587,2)0),"DOV not in Treasury","")

Rather than OFFSET($H$585,1,-2):OFFSET($H$798,-2,-2), I would like the formula to say F586:F796, so that the end user can easily understand the logic.

View 9 Replies
ADVERTISEMENT
Sep 30, 2013

Is there a way that formula links in spreadsheets can use environment variables to reference formulas in other spreadsheets/addins?

For example, I have a spreadsheet that imports templates which call functions in an addin. The spreadsheet, templates and the addin could be installed into either the 'Program Files' or the 'Program Files (x86)' folders.

I am experiencing issues with the formula references when moved between 32 and 64 bit computers since Excel seems to store the full path of the addin in the Excel formula (even though it hides it when the reference resolves its path) in my templates. I would like to update the references in my templates so that they use an environment variable (set by my software installer) to always know the correct path of the addin.

In Excel 2003, spreadsheet links are edited via: 'Edit'->'Links'

In Excel 2007 and higher,'Data'->'Edit Links'

View 1 Replies
View Related
Aug 13, 2013

I am migrating a bookkeepping spreadsheet from Quattro v12 (c2001) to Excel 2007 and have hit a stone wall over relative 3-D references.

In this workbook each sheet holds data for one month, and this includes some lagging-12-month calculations -- that is, summing a range on a single cell for the preceding 12 sheets (including this one).

Unexpectedly, this formula:

=ISUM('201206:201305'!M6)

gives absolute references, even though I put in no dollar signs after the sheet names. This means it gives the wrong answer when copied to the next sheet to start a new month.

I can find no reference at all to converting the above formula from absolute to relative reference.

View 5 Replies
View Related
Mar 25, 2009

I am creating a spreadsheet which creates a bulk of data from a front sheet.

The question is:

Is there a way to automatically copy a fixed formula for 100 cells using one fixed cell reference such as $A$1 and then automatically after 100 cells replace $A$1 with $A$2, after another 100 with $A$3 and so on?

Explanation with Example:

For example, Sheet1 contains the words "Green Tree" in Cell A1 and Sheet2 will then place "Green Tree" into 100 different sentences such as:

Plant a Green Tree

Grow a Green Tree today

This would be created with the formula ="Grow a "&'Sheet1'!$A$1& " today"

After 100 different variations using the formula I want to change that formula to reference cell A2 on Sheet1.

I know if I place "Red Tree" in Cell A2 and use the formula ="Grow a "&'Sheet1'!$A$2& " today" I can do this manually using find and replace for the 100 cells, but I want to do this for 100 different variations of Green Tree to create a 10,000 different sentences so I'd need to find and replace 100 times!

View 7 Replies
View Related
Feb 4, 2014

I have encountered some difficulty in modifying a macro I wrote into what I need. I created a macro that searches a column (Column C) for a cell value of, "stop", and then it copies everything above that cell and pastes it onto another sheet. In the sample data set that I was using, "stop" first occurred in cell C541, so the macro copies C1:C540 and pastes it onto another sheet. The problem is that the macro created an absolute reference to C540. What I desire is for the macro to use the 'Find' function to locate the first occurrence of, "stop", offset one cell above that cell, and then reference the active cell (which was positioned by these last two steps) in the range that should be copied. Basically, I'm hoping to have cells C1 through the active cell copied and then pasted onto another sheet.

Code below.

Sub FAIL()

'

' FAIL Macro

'

'

Sheets("Reformatted").Select

Columns("C:C").Select

Selection.Find(What:="stop", After:=ActiveCell, LookIn:=xlValues, LookAt _

[Code] .......

View 4 Replies
View Related
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
Jul 18, 2007

column headers are people in my department spread verticall goin down column A on my lead sheet. I am trying to report metrics for each person in their row.

I am trying to link the metrics for reporting from 5 seperate weekly batch reports that represent 5 tabs within the same workbook.

The problem is that I have the total information for each metic in column H of all 5 tabs, but all metrics for individuals are reported vertically. (H6, H7, H8, H9)

Thus how do I get the following: =SUM('Week 1'!H4)+('Week 2'!H4)+('Week 3'!H4)+('Week 4'!H4)+('Week 5'!H4)

To Equal: =SUM('Week 1'!H5)+('Week 2'!H5)+('Week 3'!H5)+('Week 4'!H5)+('Week 5'!H5) When I copy the formula across the row for each individual

My column to stay constant, but my row to increase by 1 as I fill the formula across my spreadsheet.

View 9 Replies
View Related
Oct 14, 2013

I am using name manager to identify variables in formulas in order that I can be consistent with calculations. The problem is each line item have different variable amounts. In other words if I am calculating cubic yards of concrete I used (Length*Width*Depth)/27. Line Item number one is (100*3*2)/27 based on my dimensions given. Line Item two may be (50*2*1)/27 based on different dimensions. The Name Manager has Length, Depth, etc. as absolute references. What I am trying to do is copy each formula down the sheet but each time I do the new line item always calculates from the reference cell in absolute mode. Is there a way to copy this formula all the way down the sheet so it will pick up the different dimensions keyed in each time using the Namae Manager Variables set up?

Line 1 (100 x 3 x 2)/27 = 22.22 Cubic Yards

Line 2 (50 x 3 x 1)/27 = 5.55 Cubic Yards

View 5 Replies
View Related
Jun 1, 2006

explain with an example.

Cell A1 =A2/MAX(A2:A5)

Cell A2 1

Cell A3 2

Cell A4 3

Cell A5 4

Cell A1 is 0.25 right? Right. Now insert a cell (or row) between cell A1 and A2. Then cell A1 changes to =A3/MAX(A3:A6). I don't want this to change. I still want to formula in A! to be =A2/MAX(A2:A5). I've tried using the "$" absolute character and I've tried using the absolute reference in the R1C1 reference style both to no avail.

View 9 Replies
View Related
Jul 13, 2009

Is there any way to check a worksheet/workbook to ensure that all formulas contain absolute references?

View 9 Replies
View Related
Jan 30, 2014

I know how to turn a column reference in a structured data table into an absolute reference:

=Table1[A] becomes =Table1[[A]:[A]]

However, how to make a cell reference, like this one, absolute to that it still locks on this row, column A when I drag it across. I don't want to use copy & paste, as I have other cell references that i need to leave dynamic.

=Table1[[#This Row],[A]]

I've seen that @ can lock rows, but I don't seem to be able to lock an individual cell.

View 2 Replies
View Related
Mar 30, 2014

I am taking an online computer class and when i enter a formula (=c5/c11) and then press f4, to make the cell c11 absolute, this does not work. Instead it brings me the "Project" right side mini screen. Is there a way to program the f4 key to return absolute results?

View 5 Replies
View Related
Apr 14, 2007

This is what I need to be doing:

Write cell K5 as a relative reference, absolute reference, mixed reference with the row varying, and mixed reference with the column varying.

How do I do that?

View 8 Replies
View Related
Dec 6, 2006

How do I copy an absolute cell reference from say a1 to a2 -100 so that the row reference increments with each row. Without the absolute cell reference '$' it works Ok. But with it every cell is the same as a1?

View 9 Replies
View Related
Apr 4, 2014

I have an Excel workbook with multiple worksheets. One of the worksheets has a live data feed for stock updates. I extract data from the live feed (it's in multiple sentences) and pull it into 6 columns. In another worksheet, I pull those 6 columns into the sheet through the use of Index / Match. That second sheet has additional formulas that essentially analyze the data. As the data refreshes, the existing data moves down the column.

What I didn't think about was the fact that at times, a stock may be listed several times because different brokers are offering updates. Because I used Index / Match, it will look for the first match from the top and display that data in the relevant columns. Unfortunately, some of the data needs to stay with the matching row (and stock ticker) but because of the way I wrote the formulas in Index / Match, it just finds the first matching target and displays that data.

I can correct this by using an absolute reference ($) but I can't find a way to "copy down" formulas using absolute references. I also have one column of cells that contain 1 absolute reference and one relative reference.

I'll re-write this manually if I have to but I have 1,800 cells to change.

View 2 Replies
View Related
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
May 18, 2007

I have a number in cell A2. I have hundreds of numbers (dividends) spanning across row 1. I want that number in A2 to be the divisor in my formular all across row 2.

In cell B2... if I type the formula "=B1/A2" i get the quotient I want. If I copy and paste this formula into cell C2 the divisor changes as well as the dividend. How can I "copy and paste" this so that the divisor stays the same and only the dividend is variable.

View 4 Replies
View Related
Jul 12, 2013

I have a workbook with many tabs and within those tabs there are many hyperlinks to other areas within the same workbook. When I add or delete lines or rows, the corresponding hyperlinks do not stay with the changed cell. Is there any way to make the cell reference in a hyperlink an absolute similar to how you do with a formula?

View 2 Replies
View Related
Jan 14, 2009

I'm creating a chart that will show a running average of a set of values. First column has the values and the second is a running average. Here's my sample data:

55.0

65.5

8732.7

425.5

521.4

8832.5

528.6

826.0

423.6

My formula for the second column is as follows, =AVERAGE($G$1:G1), =AVERAGE($G$1:G2)... and so on.'

My problem is when I try to run the same formula on a different worksheet to put on the current worksheet, I get an error from Excel that the formula is invalid. Here's the formula I use, =AVERAGE('Team Stats'!$A$I1:AI1), =AVERAGE('Team Stats'!$A$I1:AI2), ...

Is this a bug with Excel? Is the format wrong? I've tried just using the function wizard and it tells me that the absolute reference is invalid.

View 4 Replies
View Related
Feb 17, 2009

I have a spreadsheet that gets updated from the top. Is there any way for me to mod my formulae so that they always begin with the topmost cell (row 2 in this case, and in many columns) but still extend downward?

More practical example:

I have an Average formula in column X which (at the moment) averages X2:X75. I would like, even when adding new rows at the top (in the row 2 position) the forumla to not need to be manually extended with every new entry. So, if I add three more entrie4s, it will then average X2:X78. I tried all manner of absolutes.

View 9 Replies
View Related
Mar 24, 2007

I've been testing the countif function and for some reason it isn't working for me.

I have the following text in cells A1:A3

"apple"

"banana"

"peach"

I have the following text in cells C1:C3

"apricot"

"banana"

"grape"

I put the following formula in B1 and copied down into B2 and B3.

=COUNTIF($C$1:$C$3,A1)

It is meant to count how many of each fruit in A1:A3 is also in C1:C3.

The banana is the only one that is in both lists so the formula in B2 should return 1.

But all three formula's return zero.

If I change the range in the countif formula to relative it works. eg., =COUNTIF(C1:C3,A1)

View 3 Replies
View Related
Jun 18, 2014

I have read meanwhile dozends of articles and comments about absolute and relative cell references.

But I found nowhere an example on how to make a the column part of a cell reference variable and the row absolute.

Is

K$10

a valid expression?

In opposite to $K10 is must work this way

View 3 Replies
View Related
Nov 3, 2009

I've got a worksheet here that i need to update every week. The problem i'm having is that i have a table of values that's drawing values in from another larger table. For example in B9 the cell is referenceing the I2 cell in another sheet. When i pull down the formula in B9 i want the cell below to reference J2 and so on. In other words, I only want the alpha characters to increment. Tried messing around with dollar signs but it doesn't seem to be doing what i want.

View 2 Replies
View Related
Apr 28, 2014

I have a workbook that contains a column, G, which includes a formula that calculates distances (based on lat, long coordinates) to a training site ("TS" going forward) from other sites within the same district as that TS. There are numerous TS's in the company, usually 1 per district (but some districts do not have a training site), and I am trying to find a way to change my distance formula that will automatically detect which site is a TS, and then calculate the distances of those other sites in that same district to their respective TS's.

As it stands, I have to manually change an absolute cell reference every time I encounter one of these TS locations in the file (designated by a "TS" in Col F), and rerun the distance formula down the column. I would hate to have to do this several hundred times.

Attached is a sample file. Again, Col G contains the formula for distance calculation, and you will notice that the absolute cell references change each time a TS is encountered -- I have been doing this manually. Additionally, I would be curious to know if there is a way to spot any Districts that lack a TS, as in District 493, and specify that in Col G.

View 5 Replies
View Related
Oct 28, 2009

I need a macro to do this small task for me. Preferably it should execute when I have selected a cell and press a command button. Here's the way it should work:

Copy the cell I have selected

View 3 Replies
View Related
Apr 28, 2014

I am currently using a color count function to both count by color and count by color and cells containing certain characters, such as # or %. This works great.

However, I need to modify the UDF to count all the cells in a range in columns C:Z that contain alpha, AND IF the Interior.ColorIndex of a cell in column B within the same row of the counting formula equals the Interior.ColorIndex of a cell in column B within the same row of the selected cell in the range.

Basically, Column B is a header row, and I want to count the cells in a range in each column C:Z if they contain a name AND their corresponding header cell's color in column B matches the color of the header cell in the row containing the formula.

View 1 Replies
View Related
Feb 4, 2008

I am having an error when I try to run it and it appears not to like my destination (line bolded) - WHAT AM I DOING WRONG.?? Numrows = Worksheets("File1"). Range("A65536").End(xlUp).Row 'get the Row count

' LOOKUP Looklist! (Col B)

'----------------------------

For d = 1 To 228

' looks in Looklist B2(2,2), check against G1(1,7)

If Worksheets("Looklist").Cells(d + 1, 2).Value = Worksheets("File1").Cells(1, d + 6).Value Then

' If matches then copies G-x(lastrow,7) to looklist L2(2,12)

Worksheets("File1").Cell(Numrows, d + 6).Copy _

Destination:=Worksheets("Looklist").Cells(d + 1, 12)

End If

Next d

View 4 Replies
View Related
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
Feb 21, 2008

I'm wondering if it is possible to declare a constant in an add in, and then reference that constant from workbooks that use that add in?

Basically I want to set a version number in the add in, and check it from the workbook.

View 3 Replies
View Related
Jun 13, 2008

I have the following macro, which works fine (extraneous code not shown)...

For I = Start_ROW To Last_ROW_Z

Range("A" & I & " : " & "O" & I).Copy

But I want to make the "O" column identifier dynamic. So I've defined the column with the dimension...

Last_COL_Tracking = Sheets(" Tracking").Range("IV" & Start_ROW).End(xlToLeft).Column - 1

But this returns a column number, not the letter, so I can't use it in my original formula.

I tried converting to R1C1 with the formula...

Range ("R[" & I & "]C[1]:R[" & I & "]C[" & Last_COL_Tracking & "]")

...but this errors out.

View 3 Replies
View Related
Dec 12, 2011

I want to add a formula with absolute row to a cell.

The desired formula in the cell is something like "=SUM(B$5:B11)"

However, when i use the following code I keep getting error 1004 'Application-defined or object-defined error'

Code:

topCell.Cells(CurrentRow, 3)FormulaR1C1 = _

"=SUM(R-" & CurrentRow - 1 & "C[-1]:RC[-1])"

It works when the bracket [] is used for row, but it does not give me absolute row in the formula.

Code:

topCell.Cells(CurrentRow, 3)FormulaR1C1 = _

"=SUM(R[-" & CurrentRow - 1 & "]C[-1]:RC[-1])"

View 2 Replies
View Related
May 21, 2008

I am trying to use VB to vlookup between to workbooks

1. Make active workbook WBK1

2. Make workbook being open WBK2

3. Copy and Paste between WBK1 and WBK2

4. Have a vlookup in WBK1 and bring in the values from WBK2

5. Close WKB2

6. Copy, Paste, and transpose values in wkb1 within wkb1

The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.

View 9 Replies
View Related
Jan 17, 2010

I am using the following forumla, it gathers the net sum of a column of $ amounts.

I need to have it changed to gather the Absolute ABS total instead.

=SUMIFS($B$23:$B$500,$A$23:$A$500,">="&$V23,$A$23:$A$500,"<"&EDATE($V23,1))

V23 has a date "Month" value.

View 3 Replies
View Related
Feb 20, 2009

I'm working on my first real macro - so I'm a greenhorn. I've spent a few hours trying to research a solution, but most threads are too technical for me so I'm hoping someone can really help me out.

I am trying to use a formula that references a cell that changes day to day from when I use the macro. I need to use a formula which grabs from a cell in a non concrete location. Let me try and show you what I mean.

From I4:I10, I need a formula like this:

View 13 Replies
View Related
Feb 2, 2012

Formula is as follows:

=IF(ISERROR(VLOOKUP($A$4,Day2,2,FALSE))," ",(VLOOKUP($A$4,Day2,2,FALSE)))

However I need the formula to stay the same but the DAYS to change to DAY 3, Day 4, etc as I drag it along..

View 8 Replies
View Related
Dec 8, 2006

I am doing percentages (very simple formula)

The formula looks like this =B7/B5

Now when I drag it to the box below I get =B8/B6

But I need the numerator to increment and

the denominator to stay the same.

So that it would look like this =B8/B5

View 2 Replies
View Related
Jan 14, 2009

I have a simple count formula which is suppose to be absolute.

=COUNT($L5:$V5) and works till I add a column via a macro.

Macro adds a new column at column M then inserts data.

My problem is when I check the formula after macro it changes to

=COUNT($M5:$W5) I thought using the $ sign means Don't change?

View 9 Replies
View Related
Mar 15, 2014

I have a below formula which extracts the v and number attached to it I would like to copy this across all columns and change row for each column. So for example 1st column reference will be B4, the second column would have a reference B5 and so on, it's probably worth to mention that cells I am referencing to are in other sheet hence why there's Ref! before B4 is this why absolute column and relative row not working?

View 5 Replies
View Related
Aug 15, 2014

For some reason Absolute $ or INDIRECT is not working inside my formula when I add a row to my chart. Formula keeps adjusting.

Here is my formula...=IFERROR(LARGE(IF($B$5:$B$50=$K108,$A$5:$A$50),1),"")

$B$5:$B$50 goes to $B$6:$B$51 AND $A$5:$A$50 goes to $A$6:$A$51..... it can't do that, throws my counts out, needs to stay in this range B5:B50 and A5:A50 when I add a row to chart

Also when I use INDIRECT

=IFERROR(INDIRECT(LARGE(IF("B5:B50"=$K108),INDIRECT("A5:A50"),1),""))))

I keep getting a error with =$K108, which is a cell reference in my formula which the formula needs to look at

So what do I do, or what formula do I need to use to stop this from adjusting as I add rows to a chart to stop formula from adjusting?

View 8 Replies
View Related
Jan 24, 2009

I am using this formula to calculate a column of numbers that are both negative and positive numbers. =SUMPRODUCT(--($A$5:$A$9647<=TODAY()),--($A$5:$A$9647>TODAY()-365),$C$5:$C$9647). I need to keep this formula the same where it will calcuate on a rolling 365 day but I need the total to be only the absolute value (abs).

View 2 Replies
View Related