MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Absolute Cell Reference


Posted by Chad Stieb on August 22, 2001 9:44 AM

I have a table that I add data to on a quarterly basis, but have farmulas where I only want to use the last four quarters. When I set up an absolute reference (say to $B$3) then insert a new cloumn at B the refrence changes to $C$3. I want to always have it refrence B3 even after I insert a new column (I will be putting in the new quarter data.

Thanks any help would be appreciated.

cs


Posted by Mark W. on August 22, 2001 9:51 AM

Use INDIRECT("B3") inplace of your cell reference,
$B$3.

Posted by Chad Stieb on August 22, 2001 10:11 AM

I haven't gotten that to work either.

Posted by Aladin Akyurek on August 22, 2001 11:12 AM

Try to use in your formulas:

INDIRECT(ADDRESS(3,2))

However, I don't understand why Mark's suggestion should not work for you.

Posted by Mark W. on August 22, 2001 11:28 AM

Is B3 on the worksheet containing our formula?
If not, use INDIRECT("Sheet1!B3") where B3
resides on the worksheet named Sheet1.