Solved

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

Posted on 2011-03-25
15
1,147 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
  • 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
 
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
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.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now