Solved

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

Posted on 2011-03-25
15
1,235 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

696 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