Setting Constant Lookup Cells

  • Thread starter Thread starter Ron Achin
  • Start date Start date
R

Ron Achin

I have developed a formula with lookup cells to other
worksheets within a workbook. When the formula is copied
to another row or column within a worksheet, all cell
references change relative to the new formula location.
I would to keep the lookup cells constant but allow other
cell references (not lookup cells) to change relative to
the new formula location.

Is this possible?

Example: Formula in WORKSHEET #1 cell A1=WORKSHEET #1
cell B1*(WORKSHEET #2 cell C1)

Move example formula in WORKSHEET #1 cell A1 to WORKSHEET
#1 cell A2. Result is cell A2=WORKSHEET #1 cell B2*
(WORKSHEET #2 cell C2)

Desired formula is WORKSHEET #1 cell A2=WORKSHEET #1 cell
B2*(WORKSHEET #2 cell C1)
 
Solution is to use absolute referencing for cells that you
want to retain the same value whenever you copy & paste
the formula elsewhere. Thus, in your example; for
WORKSHEET#2 cell C1 use absolute refernce which is

WORKSHEET#2!$C$1

basically prefix the row indicator by "$" and col
indicator by "$". Same solution for range; thus if you
are referencing range C1:E8 then it becomes $C$1:$E$8


good luck
 
Back
Top