Transferring Data Between Two Worksheets

Transferring Data Between Two Worksheets

Didn't find your answer?

I have a master worksheet (Worksheet1) that has two columns titled ID and Route. Each Route has a corresponding ID assigned to it. There are about 1600 rows (records) in this worksheet. I have another worksheet (Worksheet2) that also has an ID column (which is empty) and a Route column (which is populated). I need to fill in the ID numbers in Worksheet2 that correspond to the matching Routes in Worksheet1 without having to do it manually. Can anyone give me some guidance on this. Thanks in advance for any help.

Replies (3)

Please login or register to join the discussion.

avatar
By ElliottRoss
04th Mar 2010 19:42

You can do this......

Hi

If you have the column headers being in cells A1 & B1 on both WorkSheets and you put this formula in cell A2 in WorkSheet2, you can do:

=OFFSET(WorkSheet1!$A$1,MATCH(B2,WorkSheet1!$B$2:$B$1600,0),0)

This is basically a VLOOKUP looking in a column to the left of the table_array.

Any problems, let me know on: [email protected].

Regards

Elliott

www.recenseo.co.uk

 

Thanks (0)
avatar
By Truetide
04th Mar 2010 20:43

Transferring Data Between Two Worksheets

Thanks a bunch for the advise.  I'll try it out.

Thanks (0)
avatar
By kerrym
13th Mar 2010 09:13

Care with offset function

 I use the offset function often and find it really useful.

However, it does make the spreadsheet harder to audit - the cells referenced don't appear in the cells referenced by other formulae in the audit tools.

Cheers, Kerry. 

Thanks (0)