linking two lists in the same workbook? or is there a beter solution

[belerofon1]

Halo everyone

I wanted to ask you if there is a excel function to do this for me:

this is my data:

worksheet1 (list1):
col1 ...col2........ col3..........col4............. col5
a ........ 1.............9....... 1.1.2000.........6.7.2011
b .........2.............8........5.2.2005.........6.8.2011
c .........3.............7........12.9.2004........6.9.2011
d .........4.............6........8.2.2006..........6.10.2011
e .........5.............5........7.14.2001........6.11.2011
f ..........6.............4........5.9.2008..........6.12.2011
g ..........7............3........7.7.2007..........6.13.2011
h .........8.............2........3.5.2004..........6.14.2011 <-- added in ws1
j ..........9.............1........5.9.2001..........6.15.2011 <-- added in ws1


I used 'ctrl+L' to create a list (version 2003) so that I can enter new data and work easy whit it. What i need excel to do for me is TO COPY data from worksheet1 to worksheet2 into another list that is linked to the list in workshhet1


COPY data to worhsheet2(list2):

col1 .....col4 ........col5.......... col2
a ......1.1.2000 ...6.7.2011 ...... 1
b ......5.2.2005... 6.8.2011 .......2
c ......12.9.2004.. 6.9.2011...... 3
d ......8.2.2006 ...6.10.2011..... 4
e ......7.14.2001 .6.11.2011..... 5
f .......5.9.2008 ...6.12.2011 .....6
g ......7.7.2007 ...6.13.2011 .....7
h ......3.5.2004 ...6.14.2011 ......8 <--- copied from list1 automatically
j .......5.9.2001 ...6.15.2011 ......9 <--- copied from list1 automatically


notice that I would need excel to copy new data added to list1 automatically to list2 (FOR EXAMPLE: I add data for 'j' and it is automatically copyed to list2).

PLEASE NOTICE that the copied data needs to be in a different 'column' order (col3 not needed)

So my question is: Is there a function in excel that could do this for me, or do I have to use VBA

Thanks in advance

P.S. I am a noob to excel ....

 

[Dannyh1]

Hi

In worksheet2 try

A2 = IF(worksheet1!A2="","",worksheet1!A2)
B2 = IF(worksheet1!D2="","",worksheet1!D2)
C2 = IF(worksheet1!E2="","",worksheet1!E2)
D2 = IF(worksheet1!B2="","",worksheet1!B2)

Copy down

 

[belerofon1]

yea that is a nice little trick but it doesn't help me because the number of rows is changing daily. today I have 200 but in 50 day it can be 300. so I need is a function which will copy entire row of data that I put in ws1 to ws2.

thanks for the replay.

 

[Dannyh1]

Hi,

Just copy this formula all the way down to the maximum rows you will have (10000/20000+ it doesn't matter)

Whatever you do in ws1 will update in ws2.

The rows in ws2 will show as blank if nothing in ws1.