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" <[email protected]> wrote in message
news:[email protected]...
> 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
Bookmarks