|
Automatically insert row and copy formula
Hello,
I am trying to send up a template to record sales data. Here is my sample data: Worksheet 2 A B 1 Item Price 2 Ipod ='worksheet1'A1 3 Gameboy ='worksheet2'A2 4 What function can I use so that when I type an item name in A4, Excel will auto insert a row below and auto copy the formula from B3 to B4? Any help appreciated. Thanks, Wendy |
Hi Wendy
I think you mean 2 Ipod ='worksheet1'!A1 3 Gameboy ='worksheet1'!A2 That being the case, you do not need to insert rows or copy formulae. Simply put the following formula in cell B2 of Worksheet2 =IF(A2="","",'Worksheet1'!A1) Copy down column B as afr as you wish. The cells will remain blank in column B, until you insert something into column A. However, I rather suspect that you really mean you want a look up table. If on Sheet1 you had A B 1 Item Price 2 Ipod 100 3 Gameboy 55 etc. say down to row 100 then on Sheet2 in cell B2 =IF(A2="","",VLOOKUP(A2,'Sheet1'!$A$2:$B$100,2,0)) and copy down column B as far as you wish. Then, whenever you enter on Sheet2 an item from your list on Sheet1, it will automatically insert the price for you. If you enter an item on Sheet2 that does not exist on Sheet1, you will see a #N/A error message. This can be trapped if you require. Post back if I have not understood your requirements correctly. -- Regards Roger Govier "Wendy" wrote in message ... Hello, I am trying to send up a template to record sales data. Here is my sample data: Worksheet 2 A B 1 Item Price 2 Ipod ='worksheet1'A1 3 Gameboy ='worksheet2'A2 4 What function can I use so that when I type an item name in A4, Excel will auto insert a row below and auto copy the formula from B3 to B4? Any help appreciated. Thanks, Wendy |
Hi Roger,
Thanks for your reply. You are correct. I have a lookup formula in place. Here is my problem: Sheet 1 A B 1 Item Price 2 Ipod 100 3 Gameboy 55 .. .. 100 Sheet 2 A B C D 1 Item Price Sale Price G/L 2 Ipod =if(a2=" ", " ", Vlookup(a2,sheet1$a$1:$b$100,2,0)) XXX =C2-B2 3 Gameboy =if(a3=" ", " ", Vlookup(a3,sheet1$a$1:$b$100,2,0)) XXX =C3-B3 4 5 Total XXX Sheet 2 only works fine if i have item names in column A. If I leave column A blank, column B returns with 'N/A#' which is no good to me. Because I have need to work out the gains and losses for each item. 'N/A#' affect my 'sum' formula. Also, I wonder if there is a function that would auto insert a line between row 4 and 5, and copy all formulas from row 3 to 4, when I type in the item name in A4. Sorry to trouble you. Hope I have made myself clear this time around. Thanks a lot, Wendy "Roger Govier" wrote: Hi Wendy I think you mean 2 Ipod ='worksheet1'!A1 3 Gameboy ='worksheet1'!A2 That being the case, you do not need to insert rows or copy formulae. Simply put the following formula in cell B2 of Worksheet2 =IF(A2="","",'Worksheet1'!A1) Copy down column B as afr as you wish. The cells will remain blank in column B, until you insert something into column A. However, I rather suspect that you really mean you want a look up table. If on Sheet1 you had A B 1 Item Price 2 Ipod 100 3 Gameboy 55 etc. say down to row 100 then on Sheet2 in cell B2 =IF(A2="","",VLOOKUP(A2,'Sheet1'!$A$2:$B$100,2,0)) and copy down column B as far as you wish. Then, whenever you enter on Sheet2 an item from your list on Sheet1, it will automatically insert the price for you. If you enter an item on Sheet2 that does not exist on Sheet1, you will see a #N/A error message. This can be trapped if you require. Post back if I have not understood your requirements correctly. -- Regards Roger Govier "Wendy" wrote in message ... Hello, I am trying to send up a template to record sales data. Here is my sample data: Worksheet 2 A B 1 Item Price 2 Ipod ='worksheet1'A1 3 Gameboy ='worksheet2'A2 4 What function can I use so that when I type an item name in A4, Excel will auto insert a row below and auto copy the formula from B3 to B4? Any help appreciated. Thanks, Wendy |
Hi Wendy
Leaving column A blank on Sheet2 will not return #N/A if you have the formula as =if(a2="", "", Vlookup(a2,sheet1$a$1:$b$100,2,0)) i.e. do not have a space between either sets of double quotes "" not " ". However, your formula in D2, which is =C2-D2 will return a #VALUE because of the null value in B2 Amend your formula to =if(a2="",0, Vlookup(a2,sheet1$a$1:$b$100,2,0)) and this will remedy the problem. If you do not wish to see a column of zeros down the page, choose ToolsOptionsView and uncheck Zero values. I'm not sure why you want to insert a row before you enter data, unless you are trying to keep all Ipod's and Gameboy's etc. together in the list. If that is the case, I wouldn't bother, I would enter the data in any order, then mark the whole block of data and sort by column B. However, to do what you want can't be done via a function, but it can be done via a macro. I quickly recorded the following (which is not the most efficient code) but it does achieve what you want. Sub Insertrow() ActiveCell.Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(0, -3).Range("A1").Select End Sub Open the Visual Basic Editor by typing Alt + F11 key, choose InsertModule and copy the code above into the new Module1 that is created. Click on the Excel symbol at the top left of the VBE to return to your Excel sheet. Place your cursor in any cell in column A where you wish to insert a row and choose ToolsMacroMacros (or presss Alt +F8 key) and choose Run, your new line will be created with the appropriate formulae. You can make a shortcut to this by pressing Alt + F8 key, and choosing Options and put a "q" (without the quotes) in the small cell afte Ctrl+. Close the dialogue box. Now when you want your new row, just place your cursor in the appropriate cell, press Ctrl + q and bingo!!! Hope this provides what you want. -- Regards Roger Govier "Wendy" wrote in message ... Hi Roger, Thanks for your reply. You are correct. I have a lookup formula in place. Here is my problem: Sheet 1 A B 1 Item Price 2 Ipod 100 3 Gameboy 55 . . 100 Sheet 2 A B C D 1 Item Price Sale Price G/L 2 Ipod =if(a2=" ", " ", Vlookup(a2,sheet1$a$1:$b$100,2,0)) XXX =C2-B2 3 Gameboy =if(a3=" ", " ", Vlookup(a3,sheet1$a$1:$b$100,2,0)) XXX =C3-B3 4 5 Total XXX Sheet 2 only works fine if i have item names in column A. If I leave column A blank, column B returns with 'N/A#' which is no good to me. Because I have need to work out the gains and losses for each item. 'N/A#' affect my 'sum' formula. Also, I wonder if there is a function that would auto insert a line between row 4 and 5, and copy all formulas from row 3 to 4, when I type in the item name in A4. Sorry to trouble you. Hope I have made myself clear this time around. Thanks a lot, Wendy "Roger Govier" wrote: Hi Wendy I think you mean 2 Ipod ='worksheet1'!A1 3 Gameboy ='worksheet1'!A2 That being the case, you do not need to insert rows or copy formulae. Simply put the following formula in cell B2 of Worksheet2 =IF(A2="","",'Worksheet1'!A1) Copy down column B as afr as you wish. The cells will remain blank in column B, until you insert something into column A. However, I rather suspect that you really mean you want a look up table. If on Sheet1 you had A B 1 Item Price 2 Ipod 100 3 Gameboy 55 etc. say down to row 100 then on Sheet2 in cell B2 =IF(A2="","",VLOOKUP(A2,'Sheet1'!$A$2:$B$100,2,0)) and copy down column B as far as you wish. Then, whenever you enter on Sheet2 an item from your list on Sheet1, it will automatically insert the price for you. If you enter an item on Sheet2 that does not exist on Sheet1, you will see a #N/A error message. This can be trapped if you require. Post back if I have not understood your requirements correctly. -- Regards Roger Govier "Wendy" wrote in message ... Hello, I am trying to send up a template to record sales data. Here is my sample data: Worksheet 2 A B 1 Item Price 2 Ipod ='worksheet1'A1 3 Gameboy ='worksheet2'A2 4 What function can I use so that when I type an item name in A4, Excel will auto insert a row below and auto copy the formula from B3 to B4? Any help appreciated. Thanks, Wendy |
Hello Roger,
Thanks very much for your help. It works fine now. Cheers, Wendy "Roger Govier" wrote: Hi Wendy Leaving column A blank on Sheet2 will not return #N/A if you have the formula as =if(a2="", "", Vlookup(a2,sheet1$a$1:$b$100,2,0)) i.e. do not have a space between either sets of double quotes "" not " ". However, your formula in D2, which is =C2-D2 will return a #VALUE because of the null value in B2 Amend your formula to =if(a2="",0, Vlookup(a2,sheet1$a$1:$b$100,2,0)) and this will remedy the problem. If you do not wish to see a column of zeros down the page, choose ToolsOptionsView and uncheck Zero values. I'm not sure why you want to insert a row before you enter data, unless you are trying to keep all Ipod's and Gameboy's etc. together in the list. If that is the case, I wouldn't bother, I would enter the data in any order, then mark the whole block of data and sort by column B. However, to do what you want can't be done via a function, but it can be done via a macro. I quickly recorded the following (which is not the most efficient code) but it does achieve what you want. Sub Insertrow() ActiveCell.Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(0, -3).Range("A1").Select End Sub Open the Visual Basic Editor by typing Alt + F11 key, choose InsertModule and copy the code above into the new Module1 that is created. Click on the Excel symbol at the top left of the VBE to return to your Excel sheet. Place your cursor in any cell in column A where you wish to insert a row and choose ToolsMacroMacros (or presss Alt +F8 key) and choose Run, your new line will be created with the appropriate formulae. You can make a shortcut to this by pressing Alt + F8 key, and choosing Options and put a "q" (without the quotes) in the small cell afte Ctrl+. Close the dialogue box. Now when you want your new row, just place your cursor in the appropriate cell, press Ctrl + q and bingo!!! Hope this provides what you want. -- Regards Roger Govier "Wendy" wrote in message ... Hi Roger, Thanks for your reply. You are correct. I have a lookup formula in place. Here is my problem: Sheet 1 A B 1 Item Price 2 Ipod 100 3 Gameboy 55 . . 100 Sheet 2 A B C D 1 Item Price Sale Price G/L 2 Ipod =if(a2=" ", " ", Vlookup(a2,sheet1$a$1:$b$100,2,0)) XXX =C2-B2 3 Gameboy =if(a3=" ", " ", Vlookup(a3,sheet1$a$1:$b$100,2,0)) XXX =C3-B3 4 5 Total XXX Sheet 2 only works fine if i have item names in column A. If I leave column A blank, column B returns with 'N/A#' which is no good to me. Because I have need to work out the gains and losses for each item. 'N/A#' affect my 'sum' formula. Also, I wonder if there is a function that would auto insert a line between row 4 and 5, and copy all formulas from row 3 to 4, when I type in the item name in A4. Sorry to trouble you. Hope I have made myself clear this time around. Thanks a lot, Wendy "Roger Govier" wrote: Hi Wendy I think you mean 2 Ipod ='worksheet1'!A1 3 Gameboy ='worksheet1'!A2 That being the case, you do not need to insert rows or copy formulae. Simply put the following formula in cell B2 of Worksheet2 =IF(A2="","",'Worksheet1'!A1) Copy down column B as afr as you wish. The cells will remain blank in column B, until you insert something into column A. However, I rather suspect that you really mean you want a look up table. If on Sheet1 you had A B 1 Item Price 2 Ipod 100 3 Gameboy 55 etc. say down to row 100 then on Sheet2 in cell B2 =IF(A2="","",VLOOKUP(A2,'Sheet1'!$A$2:$B$100,2,0)) and copy down column B as far as you wish. Then, whenever you enter on Sheet2 an item from your list on Sheet1, it will automatically insert the price for you. If you enter an item on Sheet2 that does not exist on Sheet1, you will see a #N/A error message. This can be trapped if you require. Post back if I have not understood your requirements correctly. -- Regards Roger Govier "Wendy" wrote in message ... Hello, I am trying to send up a template to record sales data. Here is my sample data: Worksheet 2 A B 1 Item Price 2 Ipod ='worksheet1'A1 3 Gameboy ='worksheet2'A2 4 What function can I use so that when I type an item name in A4, Excel will auto insert a row below and auto copy the formula from B3 to B4? Any help appreciated. Thanks, Wendy |
Hi Wendy
You are more then welcome. Thanks for the feedback. -- Regards Roger Govier "Wendy" wrote in message ... Hello Roger, Thanks very much for your help. It works fine now. Cheers, Wendy "Roger Govier" wrote: Hi Wendy Leaving column A blank on Sheet2 will not return #N/A if you have the formula as =if(a2="", "", Vlookup(a2,sheet1$a$1:$b$100,2,0)) i.e. do not have a space between either sets of double quotes "" not " ". However, your formula in D2, which is =C2-D2 will return a #VALUE because of the null value in B2 Amend your formula to =if(a2="",0, Vlookup(a2,sheet1$a$1:$b$100,2,0)) and this will remedy the problem. If you do not wish to see a column of zeros down the page, choose ToolsOptionsView and uncheck Zero values. I'm not sure why you want to insert a row before you enter data, unless you are trying to keep all Ipod's and Gameboy's etc. together in the list. If that is the case, I wouldn't bother, I would enter the data in any order, then mark the whole block of data and sort by column B. However, to do what you want can't be done via a function, but it can be done via a macro. I quickly recorded the following (which is not the most efficient code) but it does achieve what you want. Sub Insertrow() ActiveCell.Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(0, -3).Range("A1").Select End Sub Open the Visual Basic Editor by typing Alt + F11 key, choose InsertModule and copy the code above into the new Module1 that is created. Click on the Excel symbol at the top left of the VBE to return to your Excel sheet. Place your cursor in any cell in column A where you wish to insert a row and choose ToolsMacroMacros (or presss Alt +F8 key) and choose Run, your new line will be created with the appropriate formulae. You can make a shortcut to this by pressing Alt + F8 key, and choosing Options and put a "q" (without the quotes) in the small cell afte Ctrl+. Close the dialogue box. Now when you want your new row, just place your cursor in the appropriate cell, press Ctrl + q and bingo!!! Hope this provides what you want. -- Regards Roger Govier "Wendy" wrote in message ... Hi Roger, Thanks for your reply. You are correct. I have a lookup formula in place. Here is my problem: Sheet 1 A B 1 Item Price 2 Ipod 100 3 Gameboy 55 . . 100 Sheet 2 A B C D 1 Item Price Sale Price G/L 2 Ipod =if(a2=" ", " ", Vlookup(a2,sheet1$a$1:$b$100,2,0)) XXX =C2-B2 3 Gameboy =if(a3=" ", " ", Vlookup(a3,sheet1$a$1:$b$100,2,0)) XXX =C3-B3 4 5 Total XXX Sheet 2 only works fine if i have item names in column A. If I leave column A blank, column B returns with 'N/A#' which is no good to me. Because I have need to work out the gains and losses for each item. 'N/A#' affect my 'sum' formula. Also, I wonder if there is a function that would auto insert a line between row 4 and 5, and copy all formulas from row 3 to 4, when I type in the item name in A4. Sorry to trouble you. Hope I have made myself clear this time around. Thanks a lot, Wendy "Roger Govier" wrote: Hi Wendy I think you mean 2 Ipod ='worksheet1'!A1 3 Gameboy ='worksheet1'!A2 That being the case, you do not need to insert rows or copy formulae. Simply put the following formula in cell B2 of Worksheet2 =IF(A2="","",'Worksheet1'!A1) Copy down column B as afr as you wish. The cells will remain blank in column B, until you insert something into column A. However, I rather suspect that you really mean you want a look up table. If on Sheet1 you had A B 1 Item Price 2 Ipod 100 3 Gameboy 55 etc. say down to row 100 then on Sheet2 in cell B2 =IF(A2="","",VLOOKUP(A2,'Sheet1'!$A$2:$B$100,2,0)) and copy down column B as far as you wish. Then, whenever you enter on Sheet2 an item from your list on Sheet1, it will automatically insert the price for you. If you enter an item on Sheet2 that does not exist on Sheet1, you will see a #N/A error message. This can be trapped if you require. Post back if I have not understood your requirements correctly. -- Regards Roger Govier "Wendy" wrote in message ... Hello, I am trying to send up a template to record sales data. Here is my sample data: Worksheet 2 A B 1 Item Price 2 Ipod ='worksheet1'A1 3 Gameboy ='worksheet2'A2 4 What function can I use so that when I type an item name in A4, Excel will auto insert a row below and auto copy the formula from B3 to B4? Any help appreciated. Thanks, Wendy |
| All times are GMT +1. The time now is 06:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com