You can use the INDIRECT function. If you use this function and you delete
let say cell A1 then the cell which takes its place let say A2 (now A1) will
take the place of A1 and you want get a error:

=INDIRECT("A1")

The problem however is that if you copy down this formula it will copied it
down as a Absolute reference so it will always be "A1", what you can do is
take a column enter the row number in each cell, 1,2,3,4... then you can hide
this column (let say column Z) than in the column where you would put the
indirect function enter it with a reference to column Z like this:
=INDIRECT("A"&Z1), like this when you fill it down it will update to the
right cells.

hope this helps, do let me know if you need further clarification
--
A. Ch. Eirinberg


"Michael Koerner" wrote:

I have a shopping list workbook that contains the sheets PrintList, ShoppingList, and ItemList. The PrintList page has all the items I might ever buy listed alphabetically in columns that snake on the page into 8 columns for each item in a column it is proceeded in a column with a checkbox see below

¨ Aero Bars
¨ After Shave

The checkbox is brought in using this formula: =IF(B1="","",ItemList!$B$1)
The items are brought in using the formula: =IF(ItemList!A2="","",ItemList!A2) All items are in column A

My problem is that if I delete a row/Item from the ItemList sheet I receive the following error in the PrintList page: =IF(ItemList!#REF!="","",ItemList!#REF!)

If I just delete the item from the ItemList page, Select all the items in Column A and re-sort them to get rid of the empty space every thing is okay. I am just wondering if there might be an easier way to do this.

I'm using XP(SP3) and Excel 2007(SP1)



--

Regards
Michael Koerner