Solved

Excel VLOOKUP won't recognise reference to an Excel table dervied from a sharepoint list

Posted on 2011-03-25
15
1,203 Views
Last Modified: 2012-05-11
My VLOOKUP request doesn't work because the table array is actually an Excel table derived from a sharepoint list.

=VLOOKUP(1,Table_owssvr_1,2,FALSE)

Any thoughts?
0
Comment
Question by:RichShare
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 5
15 Comments
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35214317
could you post a sample so we can test it?
0
 

Author Comment

by:RichShare
ID: 35214382
It is very basic as I am just proving the principle at this stage.

My objective is to create a printable report from a sharepoint list.  I have looked at various routes to doing this with the minimum number of steps for users.  I want users to start with a SP list and a simple print routine.  The closest I have got is to create an excel report in the reports centre, they put in the ID number for the list and it looks up the various fields and puts them in a nice report format.  But, the VLOOKUP doesn't seem to work.

Thanks for the help.
CAPA-Report.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214391
Try this

=VLOOKUP("1",Table_owssvr_1,2,FALSE)

instead of

=VLOOKUP(1,Table_owssvr_1,2,FALSE)

Sid
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 6

Accepted Solution

by:
KnutsonBM earned 500 total points
ID: 35214394
i just put "" around the 1 in yoru vlookup formula and it worked perfectly

=vlookup("1",Table_owssvr_1,2,false)


-Brandon
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214462
Thank you RichShare :)

Sid
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35214476
hmmmmmm.........i think a point split would have been appropriate there...................

-Brandon
0
 

Author Comment

by:RichShare
ID: 35214487
Okay, new problem.  I now want to make the "1" a reference to another cell rather than a hard coded number.
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35214493
can you post another question please?

Brandon
0
 

Author Comment

by:RichShare
ID: 35214502
OOPs!  Sorry Sid.  I will place a new question around the referenced cell if that's okay?
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35214503
=VLOOKUP(A1,Table_owssvr_1,2,FALSE)

Replace A1 with whatever cell you are trying to reference, i guess no need to post a new question
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214515
>>> OOPs!  Sorry Sid.

That's 'OK' since it is a genuine miss ;)

Sid
0
 

Author Comment

by:RichShare
ID: 35214532
Just returned #N/A on my file?  Am I doing something wrong (again)? :-)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214545
That's because the number is stored as text in the table. Try this

=VLOOKUP(TEXT(A1,"@"),Table_owssvr_1,2,FALSE)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214709
Did it work?

Sid
0
 

Author Comment

by:RichShare
ID: 35230413
Hi Sid, just got round to checking it and it works fine.  Thank you for that and apologies again on the points distribution.  Have a good day. :-)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question