PDA

View Full Version : Solved: Linking Again?



drums4monty
07-23-2007, 12:45 PM
Hi All

I hope that someone out there can help me!

I have a workbook with two work sheets, the first worksheet has three columns, ID Num, Price and Type, and the second worksheet has two columns, ID Num and Price. I need to link the two sheets so that Price of sheet two links via the ID nums and puts it in sheet one in a new column.

I hope that this makes sence.

Thanks

Alan

mvidas
07-23-2007, 12:57 PM
Hi Alan,

You can use vlookup on Sheet1 to pull the prices (if there) from sheet2 for the corresponding ID number. Change the range on sheet2 to suit, putting this in D2 (or row 2 of whatever column you want sheet2's prices to be in on sheet1):

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$2:$B$1000,2,FALSE)),"",VLOOKUP(A2,Sheet2!$A$2:$B$1000,2,FALSE))

And then fill down through your Sheet1 data. Make sure sheet2's range is absolute ($ before the column and row identifiers) so that it won't change when copied.

drums4monty
07-23-2007, 01:08 PM
Hi Matt

I tried your solution but it tried to open an external sheet, probably my fault. The two sheets are actually tabs in the same file.

Alan

Bob Phillips
07-23-2007, 01:28 PM
Where Matt uses Sheet2, you need to change that to the actual sheet name.

mvidas
07-23-2007, 01:30 PM
I probably should have added that you'll need to change "Sheet2" to what your sheet is actually named that you're looking up. For instance, if your second sheet is called "new prices" then you'll want to use something like:

=IF(ISERROR(VLOOKUP(A2,'new prices'!$A$2:$B$1000,2,FALSE)),"",VLOOKUP(A2,'new prices'!$A$2:$B$1000,2,FALSE))

Matt

drums4monty
07-23-2007, 01:39 PM
Sorry but it don't work form me, it is probably me as I dont know much about Excel programming. Someone wrote this before for a similar problem I had, could something like this be made to work? The link to this page is here:

http://www.vbaexpress.com/forum/showthread.php?t=11577&goto=newpost (http://www.vbaexpress.com/forum/showthread.php?t=11577&goto=newpost)

Alan

drums4monty
07-23-2007, 01:40 PM
Sorry, the code is here:

Sub move_it()
Dim lrow As Long, lrow2 As Long
Dim cell As Range, result As Range, rngsource As Range
Dim rngtocopyfrom As Range
Dim counting As Long
lrow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
lrow2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
Set rngsource = Sheets(1).Range("A100:A" & lrow)
Set rngtocopyfrom = Sheets(2).Range("A1:A" & lrow2)
For Each cell In rngsource
Set result = rngtocopyfrom.Find(cell.Value, LookIn:=xlValues)
If Not result Is Nothing Then
result.offset(, 1).Copy Worksheets(1).Range("C" & cell.Row)
End If
Next cell

mvidas
07-23-2007, 01:46 PM
Hi Alan,

I did not write any code, as what you are asking for is easier with just a simple built-in function.

I'm attaching an example workbook based on your description using the formula I provided, hopefully it should give you the basis of what you'll need to adapt it yourself to your workbook.

drums4monty
07-23-2007, 01:56 PM
Im not sure thst is it Matt, could you tell me how to upload a sheet so you can see what I mean?

Alan

mvidas
07-23-2007, 02:13 PM
When you go to reply, click the "Go Advanced" button underneath the reply text box. That will look like the original screen you posted this question from; beneath the box there will be a button labelled "Manage Attachments" -- there you can select the file to attach.
As a word of warning, I likely won't be around for the next 16 hours or so. Hopefully someone else can help in the meantime, otherwise I will take a look in the morning (for me)

drums4monty
07-23-2007, 02:47 PM
File attached

geekgirlau
07-23-2007, 09:03 PM
=IF(ISERROR(VLOOKUP(A2,'Data 2'!$A:$B,2,FALSE)),"",VLOOKUP(A2,'Data 2'!$A:$B,2,FALSE))

drums4monty
07-24-2007, 12:01 AM
I just cannot get it to work, I copy the formula to my spreadsheet, it tries to open an external file so I cancel and the fromula appears in the spreadsheet. I fill down but nothing happens, what am I doing wrong?

Alan

daniel_d_n_r
07-24-2007, 12:42 AM
Why not use access for this?

geekgirlau
07-24-2007, 12:54 AM
Can you attach the workbook with your formulas?

Bob Phillips
07-24-2007, 01:08 AM
The formula works fine for me, but I don't get why you have 288 on row 4 of Data-1?

drums4monty
07-26-2007, 10:41 AM
Hi All

Many thanks for trying to help me, I learn a lot from you. I was able to adapt my original code and got it working.

Regards

Alan