Prevent Cell Reference Incrementing

[gibbsak]

I am working in a multi sheet workbook. I have a "master" table created on one sheet with subsequent sheets referencing the "master" using a formula series along the lines of 'Sheet1'!A5='Master!'A5

If I insert a row, above A5 in the 'master' sheet, the formula series in 'Sheet1' automatically increments the references for ever formula in the series for a5 and below so that is now reads: 'Sheet1'!A5='Master!'A6

I would like to avoid this. No matter what, I would like 'Sheet1'!A5='Master!'A5 to always appear no matter how many rows I insert above
'Master!'A5. I also have a similar Formula series in another sheet: 'Sheet2'!A5='Master!'A105; 'Sheet3'!A5='Master!'A205; and so on.

Is there a way to prevent the formula series from incrementing automatically? Using $A$5 would not be practical as there are so many cells.

Thank you for any help.

 

[scottylad2]

Use absolute refeerencing, or hard code it into the Address function, then use indirect to refer to that

Excel Workbook
	A
5	Total
6
7	Total
8	Total
9	Total
10	Total
11	Total
12	Total
13	Total
14	Total
Sheet2

Excel 2010

Cell Formulas
Range	Formula
A7	=INDIRECT(ADDRESS(5,1))
A8	=INDIRECT(ADDRESS(5,1))
A9	=INDIRECT(ADDRESS(5,1))
A10	=INDIRECT(ADDRESS(5,1))
A11	=INDIRECT(ADDRESS(5,1))
A12	=INDIRECT(ADDRESS(5,1))
A13	=INDIRECT(ADDRESS(5,1))
A14	=INDIRECT(ADDRESS(5,1))

Dragging down or inserting a blank row won't change the cell thats being referenced

 

[gibbsak]

How do I reference a differnt sheet with that method? Also, I would like to be able to make it a formula series:

=INDIRECT(ADDRESS(3,1))
=INDIRECT(ADDRESS(4,1))