Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A better way to copy and paste special between worksheets
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A better way to copy and paste special between worksheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Macro to copy & paste-special-values data to selected worksheets | Excel Discussion (Misc queries) | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
Copy Paste Special Value using Code over Several Worksheets | Excel Programming |