|
An Easier Way
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 |
An Easier Way
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 |
An Easier Way
Thanks, How would I replace/update this existing formula =IF(ItemList!A2="","",ItemList!A2) with your INDIRECT function
-- Regards Michael Koerner "Howard31" wrote in message ... 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 |
An Easier Way
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. "Michael Koerner" wrote in message ... Thanks, How would I replace/update this existing formula =IF(ItemList!A2="","",ItemList!A2) with your INDIRECT function -- Regards Michael Koerner "Howard31" wrote in message ... 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 |
An Easier Way
Thanks very much, will see how it works out. Really appreciate the help.
-- Regards Michael Koerner "Howard32" wrote in message ... 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. "Michael Koerner" wrote in message ... Thanks, How would I replace/update this existing formula =IF(ItemList!A2="","",ItemList!A2) with your INDIRECT function -- Regards Michael Koerner "Howard31" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com