0

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?

2 Answers 2

4

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

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .