Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Vba Absolute Cell Reference

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How can I make this vlookup use absolute cell references?

HTML Code:


Thanks for your help.

View Answers     

Similar Excel Tutorials

Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...
Apply Conditional Formatting to Multiple Cells with a Single Formula
How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...
Change Formulas to Absolute or Relative References
This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the colu ...
Top Excel Keyboard Shortcuts to Increase Productivity
I'll show you the top keyboard shortcuts for Excel that are sure to increase your productivity. These shortcuts are ...

Helpful Excel Macros

Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web

Similar Topics

My spreadsheet has a VLOOKUP through 44,000 rows that only needed to be
performed once. The relevant data is in the VLOOKUP cells as I want them and
the lookup values will never change. As long as I keep the other worksheet
intact, can I convert the VLOOKUP formulas to absolute references? My
spreadsheet is running very slow with the VLOOKUP in place, so I'm trying to
speed things up. Would this idea work?

Hi Guys

need some vba help. I'm working on automating a lot of my daily tasks in excel so when im on holiday soon other people in my office can do the tasks i do. Im having a bit of a problem with automating a vlookup.

Im using an inputbox to select the lookup value but the variable seems to be stored as an absolute cell reference which means when the rest of my code kicks in and copies down its always looking at that cell. i need the reference to be row relative and column absolute. is there any way to do this??




How do I mix relative and absolute references? I am trying to insert a cell formula, and then copy it down.


ActiveCell.FormulaR1C1 = "=RC[-1]/N2"

Which is not working - I want the denominator to be an absolute cell reference. I tried a couple of other things, none of which worked.


Hi, just a quick question really, I have a feeling it can probably be resolved quite quickly but I just don't know how.

Basically, I have a formula containing absolute cell references, but whenever I insert a new row into the table that the reference is looking at, the cells bound by the absolute reference change.

I thought the point of using absolute cell references is that they don't change?

Anyway, is there some way around this, to avoid the cell reference changing when a row is inserted?

Thanks in advance for any help.


Hope everyone is well.

Quick query; when I write formulas, I tend to click the cell the formula is referring to instead of typing. This always puts the cell reference as relative, which means that if I need some absolute references, I have to edit the formula after, which can take a while. Is there a button I can press whilst clicking the cell that makes it an absolute cell reference? Or something similar to make things quicker?


Hi All

I have a formula I want to use in conditional formatting, which is:


I want to be able to then drag this formula down so that it applies to the rest of the row. Values would then be red if the result is true. The problem I have is that although B7 and I7 are not absolute references, they do not update as you drag down the formula.

So in row 8, the formula still reads:


when i would like it to read:


Can anyone help me get around this? I have tried using define name for the vlookup formula but that still treats the cell references as absolute.

Thanks everyone!


Does anyone have a Macro to convert vlookup formulas to absolute cell references?

I would like convert this formula to easily audit data (trace dependents / precedents) once the fuction is performed.

I have a workbook with 2 spreadsheets. The first spreadsheet has a
cell which contains the last day of the month. The 2nd spreadsheet
has some heading rows, a row of formulas across the top (Row 5), a
blank row and then monthly table of data values starting in row 7
with the last day of the month in the first column. The most recent
month is in row 7. The formulas across the top are VLOOKUPs which
go out and pickup the last day of the month from the first
spreadsheet using an absolute cell reference $A$1. The range of the
VLOOKUP has mixed cell references (e.g., $A$7:$Z50). It returns the
corresponding cell for the requested date. When I add a new month in
the 7th row, I'm selecting the 7th row and then inserting a new row.
When I do this, my absolute cell references in the formulas are
changing from $A$7:$Z50 to $A$8:$Z51. I thought absolute cells
weren't supposed to change. I expect and want the ending row number
to change but not the starting one. What am I doing wrong and why
is the cell reference changing?

I know how to use F4 to toggle between relative and absolute references in a formula when you click on the single reference. But it would be helpful if I could select a whole range of cells containing formulas and make all the references within the selected cells absolute.

Is there a way? When I search I only get info on the basics of relative vs. absolute and nothing more advanced like this.

I have a row with very simple formulas in each of 5 adjacent cells. The formulas are of the type =b1, =c1 and so on.

What I want to do is to make all of the formulas absolute references - =$b$1, =$c$1 and so on.

How can I write a macro to do this automatically?

In other words, I want the macro to start at the first cell and change the references to absolute, go to the next cell to the right and change the references to absolute and so on.

The macro recorder doesn't do this correctly.

Can anybody help me out? I'm very new at Excel macros so the simpler the better.

I'm probably dating myself but you could do this very easily in Lotus. I'm hoping there is a similarly straightfoward approach in Excel.


Attempting to copy several Absolute cell references at the same time.
When I Copy one cell and Paste Special / Paste link, the cell is successfully copied as an absolute reference. However, when I attempt the same steps to copy more than one cell at a an time, It does not copy as an absolute reference.


Is there a formula (or similar) that can be used to change relative cell reference formulas to absolute cell reference formulas. I have tables that I need to move on a spreedsheet but I need them to keep thier absolute cell references.


I'm trying to find a way to have it so excel does not reference absolute values when linking OUTSIDE the workbook.

Also, Im not sure if it's possible to go back and turn off all absolute references that are already there (besides pressing F4 for each instance). But I'm mainly looking for a way to create formulas and not have them absolute in the first place.

Any ideas?

I have a spread sheet with mulitple cell references in it.
How can I globally change all of the cell references to be absolute references without manually changing each one?

I am writing some formulas referencing another table in my workbook.

When using structured table references in my formulas like this table_name [header]... compared to actual cell range names like H:H or H1:H1000..

How do I make these structured table references absolute?

When I'm copying these formulas across columns in my worksheet they are moving the table headers relative to the new column position. In other words I want to use structured table references the same way an absolute cell range like this $H:$H would function.

Any advice would be appreciated!

Hi there,

I know to toggle between relative and absolute value references to use F4.
However, I have copied and pasted a relative formula across several columns
and down several hundred rows.

Can I select the entire range and change all the formulas to absolute
references without clicking in every single cell? (Cell range is BB3:BQ400)



Just a general question:
Is there any way to either:

1) Paste-special -> Paste Link so the cells are then filled with absolute
references ($x$#) instead of standard reference format (x#)
2) Apply absolute reference format to all cell-references in a selected array?

Essentially, I need to be able to paste-link in a way that the information
referenced is the same even if something should be inserted above it in the
referenced sheet. Any ideas or solutions would be greatly appreciated.

Hello there,

I have a problem. I am trying to get a vlookup formula working using a defined range as the lookup field. But, it is an absoulute range. Does anyone know how to turn this absolute range into a relative range so that I can do an autofill down my column.

If this sounds a little confusing I'll elaborate a little mo


Dim shookup as Range

shookup = ActiveCell.offset(0, -12).Address    ' defines the lookup field
    ActiveCell = "=VLOOKUP(" & shookup & ",'wipaus" & lastmonth2 & ".xls'!" & BbgVZreturn & ",12,FALSE)"     'vlookup old BBG & VZ
    selection.AutoFill Destination:=Range(ActiveCell, BBGVZcurrent)

In the above code my shookup value is returning $A$1. But I need it to return simply A1, so that when I do an autofill the value changes relatively (e.g to B1,C1,D1 ...). Can anyone see what I'm missing? Or, need to put in? Thanks,


I construct speadsheets using formulas that contain both relative and mixed cell references. When I have completed a worksheet, I need to change all of the formulas to have all absolute cell references. I must do this in order to be able to filter and sort properly.

How do I change all formulas at one time to absolute without having to select each individual cell one at a time and toggle using the F4 key to change the reference?



I am not an Excel expert, I use it maily as a database whith no macros and so on. I have an Excel sheet where I want to use references from a cell in one row to a cell in another row, for example two rows above (row 13). But when I then sort the table, the references point to a wrong cell. I have tried to use absolute and relative references, and that does not help. With absolute references, the referring cell stays at the same row as it was previously (row 13). With a relative reference, the cell refers to a cell two rows above, but with a different sorting that cell has changed to another row.

What should I do?


Hi All,

Is it possible to use both relative and absolute cell references in a subtotal macro?

We're trying to create a subtotal within our macro that uses cell E3 as the absolute and the cell above the active cell as the relative reference. I just can't work out the syntax (if it's even possible!!)

I get a compile error when I use the following:-
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,"E3":R[-1]C)"

Can anybody point me in the right direction? And apologies if this has been posted previously - none of my searches returned a hit.

When selecting a cell to enter into a formula, there is a key or key
combination that will automatically make the cell an absolute cell reference
when you point-and-click. You can also highlight the cell reference in the
formula and use the shortcut to make it an absolute cell reference. This
saves typing the "$" sign. Help! I can't remember it.

I need to transpose a table and it's quiute large. Is there an easy way to make all of the cell references absolute? Or do I really have to press F4 for each one?


I've got a worksheet with several tables, each of which I've assigned a defined name. At the top of my worksheet I have several cells containing lookup functions, and these are repeated for each defined table on the sheet.

Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:


                      A                       B                      C                      D
2                    10                      10                     10                     10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table2,2) =VLOOKUP(C$2,Table3,2) =VLOOKUP(D$2,Table4,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table2,3) =VLOOKUP(C$2,Table3,3) =VLOOKUP(D$2,Table4,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table2,4) =VLOOKUP(C$2,Table3,4) =VLOOKUP(D$2,Table4,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table2,5) =VLOOKUP(C$2,Table3,5) =VLOOKUP(D$2,Table4,5)

To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns B-D, but doing that gives me the result below:

                      A                       B                      C                      D
                      A                       B                      C                      D
2                    10                      10                     10                     10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table1,2) =VLOOKUP(C$2,Table1,2) =VLOOKUP(D$2,Table1,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table1,3) =VLOOKUP(C$2,Table1,3) =VLOOKUP(D$2,Table1,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table1,4) =VLOOKUP(C$2,Table1,4) =VLOOKUP(D$2,Table1,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table1,5) =VLOOKUP(C$2,Table1,5) =VLOOKUP(D$2,Table1,5)

As you can see, I still have to go in to Columns B-D and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?

I set up a very large workbook. I have linked the formulas of one sheet to
another, however, I need the columns to be rows and the rows to be columns.
I have linked the values, but I need to make all the cells in the sheet
absolute before I can transpose them. There are over 20,000 cells and I
really don't want to hit the F4 key 20,000 times in the sheet. A replace
would not work for this, as the data is too big. I have tried a copy and
then paste special- paste link, but it only makes the reference absolute when
one cell is copied at a time. Any ideas on making many cell references
absolute at once?