Cross worksheet find and paste

[TheIconoclast]

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

[Crowbar via OfficeKB.com]

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

[TheIconoclast]

looks like that will work for this part, thanks again



- Kevin J

[Crowbar via OfficeKB.com]

Have you tried changing the 5 to a 4?

Send it to me

[email protected]

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

[TheIconoclast]

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

[Tim Williams]

You might be able to use MATCH() and OFFSET()

Tim

--
Tim Williams
Palo Alto, CA


"TheIconoclast" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Crowbar via OfficeKB.com]

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