Duplicating cell values from one sheet to another

[Yash Ganthe]

Hi,

On Sheet1, I have A1:A500 filled with text strings.

I want to get the same text values into a column on Sheet2 of course
without copy pasting. It should work liek a formula so that if I make
a change on Sheet1, it should reflect on Sheet2.

Next step is to get only distinct values from Sheet1 to Sheet2 so that
Sheet2 does not have duplicates.

What is the best way to do it?

Thanks,
Yash

 

[Claus Busch]

Hi,

Am Tue, 14 May 2013 07:28:54 -0700 (PDT) schrieb Yash Ganthe:

I want to get the same text values into a column on Sheet2 of course
without copy pasting. It should work liek a formula so that if I make
a change on Sheet1, it should reflect on Sheet2.

Next step is to get only distinct values from Sheet1 to Sheet2 so that
Sheet2 does not have duplicates.

for all strings in Sheet2:
=Sheet1!A1
and copy down to A500
For unique strings
eg. in D1 Sheet2:
=Sheet1!A1
in D2:
=INDEX(Sheet1!$A$1:$A$500,MATCH(1,(COUNTIF(D$11,Sheet1!A$1:A$500)=0)*(Sheet1!A$1:A$500<>""),0))
enter the array formula with CTRL+Shift+Enter and vopy down till you get
an error


Regards
Claus Busch

 

[Yash Ganthe]

Any better alternative like using an in-built function?

Thanks,
Yash

 

[Claus Busch]

Hi,

Am Tue, 14 May 2013 08:37:34 -0700 (PDT) schrieb Yash Ganthe:

Any better alternative like using an in-built function?

not if you want to refresh your sheet2 on changing values in sheet1
Look for advanced filter if refreshing is not needed


Regards
Claus Busch