Solved

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

Posted on 2011-03-25
15
1,180 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

789 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