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.