LOOKUP from separate sheet

[NickPDC]

Hi there,

First post on here so Hi!! to everyone

I feel as if I am banging my head against a wall with Excel right now, as I cannot seem to get this function to work!

Basically, I need to be able to type a value into one of the fields, in this case a part number, and the rest of the details will have to be looked up from a seperate table and enter into the remainder of the fields.

For this case, I have put the worksheet that I wish to copy the data from into the same workbook as the sheet I wish to copy the data to, to make things easier for now, ha!

I have attached an image to show what I mean!

Part number is top left of the screen. The blank fields are the ones I require to be filled in!

This is the sheet I am copying data to.

\1

This is the sheet I am copying data from.

\1

Any help would be greatly appreciated!!!

Thanks,

Nick

[NBVC]

Try:

=Vlookup(C2,Export!A:L,4,FALSE)

Have a look at this function in Excel help for more details.

[NickPDC]

No such luck

Still getting the #N/A error!

[NBVC]

Check that you don't have extra spaces at the end of either C2 or column A of Export sheet.

Also, is one formatted as number (or general) and the other as text?

Try:
=Vlookup(C2+0,Export!A:L,4,FALSE)

and/or

Select column A of Export sheet and go to Data|Text to column and click Finish.

or post actual workbook sample instead of pictures.. those don't help with diagnostics.

[Coors]

Also, make sure the table you are looking up has the look up value in order (1,2,3..etc.) Excel prefers this I have found.

[martindwilson]

Coors that doesn't mater if you use the =Vlookup(C2+0,Export!A:L,4,FALSE) false argument
but lookup(a1,A:L,... is never going to work it's the wrong syntax for look up!
but
as a general comment post a workbook!

[NickPDC]

Try:

=Vlookup(C2,Export!A:L,4,FALSE)

Have a look at this function in Excel help for more details.

I was having a weird moment yesterday!
This formula works a treat! Thank you very much
The cells on the "Export" sheet were a set to a different data type than those on the other one. Changed those and it worked!

Now, another quick question.

How would I write the formula if the "Export" sheet was saved in a seperate workbook?

Something like..

=VLOOKUP(C2,"C:\Export![Sheet1]A:L,4,FALSE) ??

Thanks!

Nick

[NickPDC]

I have tried the above formula and it came up with a window saying "Update Values For"
So I browsed to where the file is located and clicked ok.

It has now given me the following formula, but I am getting the #NAME? Error!

=VLOOKUP($C$2,'C':'\\pdcdata\users\[Export]Sheet1'!A:L,5,FALSE)

[NickPDC]

I have now managed to get a different type of error.
The "Export" file will be on a network drive eventually.
So I have put the file on the "H" drive. And have tried mapping the formula to that worksheet.
Formula is as follows:

=VLOOKUP($C$2,'H:\Environment\[Export.xlsx]Sheet1'!A:L,5,FALSE)

I am now getting the #N/A error..

Anyone any ideas?

I have both sheets open with data that worked before the "Export" sheet was made external from the workbook!

[outofthehat]

When I'm writing formulae between books I never try to type it... You could try setting up your formula this way, and seeing if that gets it working...
http://www.ooth.co.uk/excel-tips/gen...-the-easy-way/

[NickPDC]

When I'm writing formulae between books I never try to type it... You could try setting up your formula this way, and seeing if that gets it working...
http://www.ooth.co.uk/excel-tips/gen...-the-easy-way/

Thanks for the advice! I will give it a go and see if it works

[NickPDC]

Hey again,

Still having problems with getting this working!

I had it operating when the worksheet was within the workbook.

Now I have put the worksheet with the data on in a different workbook and stored it on a network drive.

I am constantly getting the #N/A Error!

The worksheet I am working from is "Ops cards", and I am looking up data from the "Export" workbook which will be stored in the following location. 'H:\Environment'.

I have mainly been working on the "Metal Melting" worksheet, hence the none working formulas on there!

Any help would be greatly appreciated

Cheers,

Nick

[NBVC]

Select Column A in Export sheet and then go to Data|Text to Columns and just click Finish.

Your entries in column A don't match the formatting of C2 in the Ops Cards workbook.

[NickPDC]

Perfect! Solved!

Thank you!!