A better way to copy and paste special between worksheets

[seelan_naidoo@yahoo.com]

Hi all..

Fistly this group has taught me alot and I hold my hat out to all you
guys ..

Ok I am here because I have another problem that I cannot find
elsewhere here.

here goes
I have two different worksheets with almost the same information but
in one column there is different data on it which I would want to
replace in the other column in the different worksheet.

In one worksheet I have some user names in a column and their home
directory paths in another.
In the other worksheet I have some of the users which where in the
first worksheet in a column and their new propose Home directory
paths in another column. Copying and pasting special every time has
become a pain .
what I want to do to is to have a function in excel where it matches
the names in one column of the worksheet to the other column of the
next worksheet and if one name is found between the two different
worksheets then it should find the column with the home directory path
and be able to replace it with the new proposed path home directory.

This csv file will be imported to DFS in order to create new directory
paths for the users..

thanks alot to all.

[paul.robinson@it-tallaght.ie]

Hi
Sounds like a bit of a tongue twister, but a formula involving VLookUp
would probably do the job?
On the second sheet in a third column for the first user type
=VLookUp(user, FirstRange,2,False)
where
user = address of first user (A1?)
FirstRange = Range on first sheet containing the two columns of data.
Use absolute referencing like Sheet1!"$A$1:$B$20" after selecting range
with mouse/keys.
2 refers to the second column in the FirstRange with the paths
False just means you don't have to sort.

This will give the new path if the user is already in Firstrange and
#NA if they are not.
Now in a fourth column on the second sheet you might do (in D1?)
=if(ISERROR(C1),B1,C1)

where I'm assuming the first user is in A1 so that his path is in B1
and his VLookUp in C1.
Now copy down this formula and copy/paste special...values only into
the second column. delete columns 3 and 4 and you are done.
Not sure that is exactly what you want, but you should be able to bodge
it to fit.

regards
Paul