Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross worksheet find and paste
I have a workbook with multiple worksheets in it. I have to consolidate an updated value into the primary worksheet from new versions of the other worksheets daily. The primary sheet is formated as such ID Name OldValue NewValue idnum Jhon 402 340 the other worksheets have a corresponding ID column and a value colum, I just need to loop through all the ID's in the id column in sheet A, find that idnum in worksheet B, select the value from worksheet B, and paste it into NewValue on worksheet A. I imagine its pretty straightforward, I apoligize in advance for my ignorance. Thanks for your help - Kevin J -- TheIconoclast ------------------------------------------------------------------------ TheIconoclast's Profile: http://www.excelforum.com/member.php...o&userid=30809 View this thread: http://www.excelforum.com/showthread...hreadid=504706 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross worksheet find and paste
You can use vlookup which will update the sheet in real time rather than
running a macro to update it This is what I would do: Go to sheet B, lets say that column A is your ID numbers and column B is the value that you want put into sheet A i.e. A B id 71454 1000 Highlight column A and B together Just above column A is a white box that should say A1 when you have highlighted the columns Click this box and type: idvalue now go to sheet A and click on a cell that you want o update the value then enter =vlookup(A1,idvalue,2,false) You may need to change this to suit your form What it does is lookup whats in A1 on your sheet Then it looks for the range we have called "idvalue" and searches for the same value in their Then it moves to the cell to the right of it (thats what the 2 is for, it might be the cell after that you want if it is then change this number to 3 etc) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross worksheet find and paste
i've tried to implement this for the past 45 minutes or so, however it not liking something. I have the range on sheet B, which includes only the cells that hav the ID numbers (in G7:G65) and the cells that have the values (i K7:K65), named. The Values are 4 columns to the right of the I numbers. On sheet A i have the formula =VLOOKUP(E13,rangeB,5,FALSE) in the cells that i want the values from K to pull into, and I kee getting a N/A error. I've tried all sorts of changes and i can't figure out why its no working. Probably missing something obvious. Thoughts? - Kevin -- TheIconoclas ----------------------------------------------------------------------- TheIconoclast's Profile: http://www.excelforum.com/member.php...fo&userid=3080 View this thread: http://www.excelforum.com/showthread.php?threadid=50470 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross worksheet find and paste
Have you tried changing the 5 to a 4?
Send it to me I will sort the code so it works and you can take it from there -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross worksheet find and paste
no need, its working fine, thanks though. as an aside, is there any way to use vlookup in conjunction with something else to do a lookup in a table that has the values in a column that is to the left of the idnumber (i.e. values are in B and id number is in H)? - Kevin J -- TheIconoclast ------------------------------------------------------------------------ TheIconoclast's Profile: http://www.excelforum.com/member.php...o&userid=30809 View this thread: http://www.excelforum.com/showthread...hreadid=504706 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross worksheet find and paste
You might be able to use MATCH() and OFFSET()
Tim -- Tim Williams Palo Alto, CA "TheIconoclast" wrote in message news:TheIconoclast.227fuz_1138216503.1255@excelfor um-nospam.com... no need, its working fine, thanks though. as an aside, is there any way to use vlookup in conjunction with something else to do a lookup in a table that has the values in a column that is to the left of the idnumber (i.e. values are in B and id number is in H)? - Kevin J -- TheIconoclast ------------------------------------------------------------------------ TheIconoclast's Profile: http://www.excelforum.com/member.php...o&userid=30809 View this thread: http://www.excelforum.com/showthread...hreadid=504706 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cross worksheet find and paste
Yes
I would put the idnumber in column A, Column B & C are the data you want to lookup highlight ALL three columns together and give then a range go to the other sheet that also contains the idnumber information (not range) also in column A if you want vlookup to find the column b data then =vlookup(a1,range,2,false) if it was column C you would use =vlookup(a1,range,3,false) HTH -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200601/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find values by cross columns and rows | Excel Discussion (Misc queries) | |||
What is cross totalling and where do i find it? | Excel Discussion (Misc queries) | |||
Find matching date in another worksheet, copy and paste data | Excel Discussion (Misc queries) | |||
cannot find cross and tick box just below the toolbars | Setting up and Configuration of Excel | |||
How do I find a cross reference value | Excel Discussion (Misc queries) |