I have a VLOOKUP and when I do the pull down formula copy (The black cross on the bottom right of the original cell) method although the formula copies the table array cells also change so that the range of cells.

It isn't what I need, I can't seem to work out a way of keeping the same cell range in the formula's without manually copy and pasting the formula into every cell

Forumla is as follows:

=VLOOKUP(Table1[[#All],[Service Tag]], '[Master record on a new sheet in excel]'!A2:D30,2, FALSE)

However, when I copy down the formula the "A2:D30" increments each time; A3:D31 etc. which is not what i want i want the formula to be exactly the same in each cell; is there a way to accomplish this without manually copy and pasting it in each time?

up vote 4 down vote accepted

You can force a cell reference to be absolute / static / locked.

The following semantics hold:

  • A1 - normal / free
    • Replicating vertically would produce A2, A3, ...
    • Replicating horizontally would produce B1, C1, ...
  • $A1 - lock column
    • Replicating vertically would produce A2, A3, ...
    • Replicating horizontally would produce A1, A1, ...
  • A$1 - lock row
    • Replicating vertically would produce A1, A1, ...
    • Replicating horizontally would produce B1, C1, ...
  • $A$1 - lock absolutely
    • Replicating vertically would produce A1, A1, ...
    • Replicating horizontally would produce A1, A1, ...

These locks can be cycled by pressing F4 while the cursor is on the cell reference.


In your case, it sounds like you want $A$2:$D$30

  • 1
    You sir/madam are a life saver that was puzzling me for hours – T Crumpen Apr 11 at 10:44
  • Happy to help :-) – Attie Apr 11 at 10:56

i think what you may talking about are absolute references. google it. as far as the formula is concerned. you can add $ sign as an absolute reference to cell names and they wont increment, like !$A$2:$D$30,2, FALSE).

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.