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

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

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

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

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

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

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