Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup using contents of clipboard?
Here's the spreadsheet I'm working on:
http://www.myfootsmells.com/excel/copypaste.xls I want to copy from A2:C4 and then put my cursor in C9 do a paste and it'll fill in the hours accordingly. Possible? It doesn't need to be a paste, I'd be find putting my cursor in C9 and pressing a button on the page. Thanks, Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup using contents of clipboard?
You can do this easily without a macro...
In your sample worksheet, enter this formula in C9 and copy down: =SUMPRODUCT(--($A$2:$A$4=A9),--($B$2:$B$4=B9),$C$2:$C$4) If you don't want to see the zeros, you can either hide them or use the following formula instead: =IF(SUMPRODUCT(--($A$2:$A$4=A9),--($B$2:$B$4=B9),$C$2:$C$4)=0,"",SUMPRODUCT(--($A$2:$A$4=A9),--($B$2:$B$4=B9),$C$2:$C$4)) If you are unfamiliar with SUMPRODUCT, here is a good introduction: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps, Hutch " wrote: Here's the spreadsheet I'm working on: http://www.myfootsmells.com/excel/copypaste.xls I want to copy from A2:C4 and then put my cursor in C9 do a paste and it'll fill in the hours accordingly. Possible? It doesn't need to be a paste, I'd be find putting my cursor in C9 and pressing a button on the page. Thanks, Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup using contents of clipboard?
Another common non-macro approach:
Insert a new column A. In it, concatenate the cells in each row of columns B & C (which used to be A & B). Put this formula in A2 =B2&C2 and copy down. Now you can do a normal Vlookup in D9 (which used to be C9): =VLOOKUP(A9,$A$2:$D$4,4,FALSE) Copy down, recalc, and copy/paste in place as values. Then you can delete the new column A and use Edit Replace to replace the #N/A with nothing. To avoid #N/A from the start, use this formula in D9: =IF(ISERROR(VLOOKUP(A9,$A$2:$D$4,4,FALSE)),"",VLOO KUP(A9,$A$2:$D$4,4,FALSE)) Hutch " wrote: Here's the spreadsheet I'm working on: http://www.myfootsmells.com/excel/copypaste.xls I want to copy from A2:C4 and then put my cursor in C9 do a paste and it'll fill in the hours accordingly. Possible? It doesn't need to be a paste, I'd be find putting my cursor in C9 and pressing a button on the page. Thanks, Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The contents of the clipboard | Excel Programming | |||
Clear clipboard contents | Excel Discussion (Misc queries) | |||
Keep contents in clipboard | Excel Programming | |||
Set variable to clipboard contents? | Excel Programming | |||
Clipboard contents | Excel Programming |