An Easier Way

[Michael Koerner]

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)

 

[Howard31]

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

 

[Michael Koerner]

Thanks, How would I replace/update this existing formula =IF(ItemList!A2="","",ItemList!A2) with your INDIRECT function

--

Regards
Michael Koerner


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

 

[Howard32]

Put the following formula in the first cell and then copied it down

=IF(INDIRECT("ItemList!A" &ItemList!Z1)="","",INDIRECT("ItemList!A" &ItemList!Z1))

IMPORTANT: If when you delete items in the ItemList you delete entire rows then you'll have the same problem with this method, what you should do in this case is put the row numbers that the INDITRECT will use, in a another sheet

=IF(INDIRECT("ItemList!A" &AnotherSheet!Z1)="","",INDIRECT("ItemList!A" &AnotherSheet!Z1))

Hope this helps

By the way I am Howard31 the same as Hward32 just I posted this from my Windows Mail because I had trouble with the website.
Thanks, How would I replace/update this existing formula =IF(ItemList!A2="","",ItemList!A2) with your INDIRECT function

--

Regards
Michael Koerner


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

 

[Michael Koerner]

Thanks very much, will see how it works out. Really appreciate the help.

--

Regards
Michael Koerner


Put the following formula in the first cell and then copied it down

=IF(INDIRECT("ItemList!A" &ItemList!Z1)="","",INDIRECT("ItemList!A" &ItemList!Z1))

IMPORTANT: If when you delete items in the ItemList you delete entire rows then you'll have the same problem with this method, what you should do in this case is put the row numbers that the INDITRECT will use, in a another sheet

=IF(INDIRECT("ItemList!A" &AnotherSheet!Z1)="","",INDIRECT("ItemList!A" &AnotherSheet!Z1))

Hope this helps

By the way I am Howard31 the same as Hward32 just I posted this from my Windows Mail because I had trouble with the website.
Thanks, How would I replace/update this existing formula =IF(ItemList!A2="","",ItemList!A2) with your INDIRECT function

--

Regards
Michael Koerner


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