[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1355
  • Last Modified:

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

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
RichShare
Asked:
RichShare
  • 5
  • 5
  • 5
1 Solution
 
KnutsonBMCommented:
could you post a sample so we can test it?
0
 
RichShareAuthor Commented:
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
 
SiddharthRoutCommented:
Try this

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

instead of

=VLOOKUP(1,Table_owssvr_1,2,FALSE)

Sid
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
KnutsonBMCommented:
i just put "" around the 1 in yoru vlookup formula and it worked perfectly

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


-Brandon
0
 
SiddharthRoutCommented:
Thank you RichShare :)

Sid
0
 
KnutsonBMCommented:
hmmmmmm.........i think a point split would have been appropriate there...................

-Brandon
0
 
RichShareAuthor Commented:
Okay, new problem.  I now want to make the "1" a reference to another cell rather than a hard coded number.
0
 
KnutsonBMCommented:
can you post another question please?

Brandon
0
 
RichShareAuthor Commented:
OOPs!  Sorry Sid.  I will place a new question around the referenced cell if that's okay?
0
 
KnutsonBMCommented:
=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
 
SiddharthRoutCommented:
>>> OOPs!  Sorry Sid.

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

Sid
0
 
RichShareAuthor Commented:
Just returned #N/A on my file?  Am I doing something wrong (again)? :-)
0
 
SiddharthRoutCommented:
That's because the number is stored as text in the table. Try this

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

Sid
0
 
SiddharthRoutCommented:
Did it work?

Sid
0
 
RichShareAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now