Automatically insert row and copy formula

[Guest]

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

 

[Roger Govier]

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.

 

[Guest]

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]

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
Tools>Options>View 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 Insert>Module 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 Tools>Macro>Macros (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.

 

[Guest]

Hello Roger,
Thanks very much for your help. It works fine now.
Cheers,
Wendy

 

[Roger Govier]

Hi Wendy
You are more then welcome. Thanks for the feedback.