How to automate a spreadsheet.

Question

Hello.

I am using microsoft excel 2002. What I am wanting to do is say I have some data in column A in sheet 1 and data in column A in sheet 2. What I want to do is when I add data to sheet 1 Column A (lets say some text), I want excel to automatically add a row in sheet 2 column A. So if I have 30 rows in both sheets and I add a string "hello" in sheet 1 Column A (in row 31), I want it to automatically add the same row in sheet 2 Column A (row 31). Is this possible to do from within excel?

Thanks in advance for your help.

Answer 1

you could put an Array formula in Sheet2 Column A. I dont think you can get this to work on the entire column, but you could apply it to say the first 5000 rows. Then when the user enters values on Sheet1 they would be duplicated on Sheet2.

Somthing like: =IF(Sheet1!$A$1:$A$5000="","",Sheet1!$A$1:$A$5000)

Should copy the cells from A1:A5000 and remove the empty ones into the destination range. You will need to select the 5000 cells on Sheet2 and enter the formula and then press Ctrl-Enter to insert it as an array formula.

This is far simpler than attempting to build something either with VBA or VSTO to watch for changes and duplicate them.

Answer 2

You can do this easy in VBA. If getting started is hard for you, record a macro and check out the generated code. You'll have no problem continuing from there.

Good luck!

Answer 3

In column Sheet 2 , column A row 1 enter the formula '='Sheet 1'!A1

Then cntrl C the cell with the formula you just entered, Still in Sheet 2 select cells A2 to A99 or however many columns you expect to enter cntrl V

The values you enter in Sheet1 will magically appear in sheet2