Getting data from another workbook 1

[Paulreg]

Hi
I have a Excel spreadsheet that needs to obtain pricing from a worksheet in another workbook.
I need to take a reference value from column A in the first spreadsheet (eg X20393) and lookup this value in another workbook and read a price in the same row 2 columns over.
I have tried VLookup but I am going wrong somewhere.
=VLOOKUP(A16,'[Intranet_PL-V12_For_Presales_280807.xls]xls_price_list_admin.asp produc'!$A$1:$C$65536,3,FALSE)
The second workbook is called
Intranet_PL-V12_For_Presales_280807.xls
The worksheet in that workbook is called xls_price_list_admin.asp produc
Any ideas please ?

 

[GlennUK]

What is going wrong with the VLOOKUP? An error? An unexpected value? Or something else?

I use VLOOKUPs in external workbooks a lot, and have no problem. I find that when people try to type external references they often mess up, whereas if you have the external workbook open you can build formula by pointing at the required references during the creation of the formula. ( like typing =VLOOKUP(A16, and then navigating to the data workbook and sheet and selecting the appropraite reference before typing the rest of the formula )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Paulreg]

The latest position is that Excel converts the VLookup I posted earlier to
=VLOOKUP(A16,'[Intranet_PL-V12_For_Presales_280807.xls]xls_price_list_admin.asp produc'!$A:$C,3,FALSE)
The result is #N/A
I did try having the other workbook open and building the formula but somehow I always got a syntax error. I'll give it another try though.
Thanks
Paulreg

 

[GlennUK]

If the result is #N/A it kind of points to there not being a matching value. Have you compared the values that you think match?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

I'm guessing spaces at the end of one of the sets of references...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Paulreg]

Great - it was spaces on the end of the references.
Trouble is the sheet has thousands of rows all with spaces at the end of each reference.
I have tried Trim but VLookup does not work with an entry like =Trim(D1). VLookup needs the actual value X20393.
Thanks
Paulreg

 

[xlbo]

I fthe spaces are in the data you are looking up you can use:

=vlookup(trim(A16),'[Intranet_PL-V12_For_Presales_280807.xls]xls_price_list_admin.asp produc'!$A:$C,3,FALSE)

If the spaces are at the end of your reference data then you will need to clean that up before doing the vlookup.

Add a reference column that simply trims the contents of the offending cells and then copy/paste special values over the top of the original data


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Paulreg]

The spaces are at the end so I used Trim then coped and pasted the values of Trim results.
Trouble is, the copied and pasted values have a space at the end !

Thanks
Paulreg

 

[Paulreg]

I don't think the blank characters at the end of each cell were spaces after all.
I used SUBSTITUTE and copied a blank character from a cell as the find and then replaced with "".
Then I copied and pasted the values and everything worked !!
Thanks
Paulreg