copy formula from previos cell to next cell when i enter something

**senk007** · 09-09-2007, 06:21 AM

Hello
I want to copy formula from previous row to next cell when i enter something in perticular cell.
i.e

--Colomn A --- Colomn B -- --------Colomn C
1 01-09-07 ----- John ----------=vlookup(b2,$s$1:$t$10,2,false)
2 01-09-07 ----- Smith -------- =vlookup(b3,$s$1:$t$10,2,false)
3
4
5

Now if i enter date in cell A3 then cell C3 should be automatically filled/copy formula as celll C2. and so on......
then if i enter data to A4 then cell C4 should be automatically filled/copy formula from cell C3.

how can i do this. any idea?

I have also attached example file.

**StephenR** · 09-09-2007, 06:38 AM

Right click on the worksheet tab, view code and paste this. Your VLOOKUP references column B not A though. I haven't looked at your attachment.

Please Login or Register  to view this content.

**sanketgroup** · 09-09-2007, 07:00 AM

StephenR

Awesome. excellet. Appreciate your support.

Thank you.

S.

**royUK** · 09-09-2007, 09:27 AM

You can open the Options Dialog from the Tools menu. In the Edit tab there is an option to extend formulas & formats. Once you have started entering data into a table the formulas will start to extend after 2 or 3 rows.

Excel 2003 has improved Lists which automatically do this, see

http://www.excel-it.com/excel2003_lists.htm

**sanketgroup** · 09-09-2007, 01:18 PM

royUK
thanks for reply
But it does not work every time. If there is a blank row or colomn, then it would not work.

**royUK** · 09-09-2007, 01:53 PM

If you are working with data like this in Excel, then your data should not have blank rows and columns.

**sanketgroup** · 09-14-2007, 10:10 AM

StephenR

when i am entering this line in VB it gives me error for "mmm-yy"

Cells(Target.Row, 5).Formula = "=TEXT(A" & Target.Row & ","mmm-yy")"

how can i resove this.

pls help.

**StephenR** · 09-14-2007, 10:15 AM

Does this work?

Please Login or Register  to view this content.

Because you have quotes in the formula you need to add extra ones, I think.

**sanketgroup** · 09-14-2007, 10:29 AM

no, its not working
it does not give error when i type in VB as per your updated way
but after closing VB when i enter value in excel,it goes to debug.