Sharepoint Equivalent of VLOOKUP

cotton31

cotton31

cotton31

Associate
Joined
25 Feb 2007
Posts
905
Location
Midlands
I have two lists in Sharepoint:

List A contains all of my data for different items

List B will be used to enter figures based on an item selected

I want to be able to have a dropdown in List B which is populated with the items from List A. This is what I have so far.

Now I have other columns in List B which I need to be automatically populated based on the item chosen above, exactly how VLOOKUP works in Excel.

Is this possible, and what would I need to do it?

Thanks,
 
I'm not totally sure what you are trying to do here but lookup columns functionality is fairly basic I think. You can have an 'items' list and then have one of your fields driven off another list using the lookup field type. I'm not sure what else you need to do. There is limited functionality for lookups really.
 
Hi Ringo, thanks for your reply.

Basically I have a list of tasks with a column containing the user assigned to each task.

The other list is to allow users to enter monthly stats for each task. I've created a lookup column using an add in for sharepoint which limits the dropdown based on a filter (in this case I am only showing tasks assigned to the user filling out the form). Once they have chosen the task, I then want other fields on the form to be populated with data from the task list.

So for example the tasks list might look like this:

Task Target Group Assigned To
------------------------------
T1 90% B jsmith
T2 95% A another
T3 85% C bjones

So if jsmith was adding a record to the other list, they could only pick T1. Once they had picked this, the target and group values for that task would also be populated.

I hope this makes sense - it's difficult to explain!

Thanks for your reply
 
I think I know what you are trying to achieve but I'm not sure how to do this. If you were were using custom code you could probably write an event receiver so that when an item is added/selected then it will call some methods behind the scenes to populate the rest of the fields.

I think VinceB1 on here is the man to ask about custom sharepoint development stuff. I have don't bits and pieces but not much. I can't think of any standard OOTB functionality that will do what you need to do.

One thing you could try instead of using addins is just to use views to control who see's what. If you only want users to see tasks assigned to them you can create a default view and set it up to show only fields where field1 = Me for example. I think the Me keyword is all you need to refer to current logged in user.
 
Really confused at what your trying to achieve but can certainly help with writing a reciever etc. Could you possibly mock up some kind of flow chart showing data relationships between the two lists? Or even a couple of screens of the form showing where the data is coming from. I am sure that this would help.

BTW - what version of sharepoint are you running as all of them from WSS3 on have pretty good ways of sharing info between lists without the use of a combersome addin (most of which are utter trash). Another thing, are the lists on the same site? Or in a different site in the same collection? As this will have some kind of bearing on how you would best develop this (basically were these needs considered at design time and was this taken into account in the structure of your collection?).

One thing to remember here is that sharepoint is not a relational database and as such cannot be used in the same way as one, some of these third part apps can do weird and wonderful things with list but let me tell you that generally speaking adding these into your environment can and will come back to bite you (Generally when migration time comes).
 
Last edited:
Thanks both for your replies.

@VinceB1 - I am trying to create a way for our users to enter their performance information against KPIs. I have a list containing all of our KPIs along with the targets they have been set and who they are assigned to. I then have another list which will be the input form to hold the performance data for each KPI. When a user creates a new entry, obviously I only want them to be able to enter data for their KPIs. To achieve this, I have used an add in to give a drop down list which allows filters based on views (I have created a view against the KPI list which only shows users the KPIs assigned to them). This bit works well.

The second part that I need help with is the automation of the rest of the form. Ideally, I want there to be only two fields the user can edit - the KPI chosen, and the performance data field (usually a percentage). However, I need other data (the target set) stored on the KPI list to be pulled through to the entry form, for use in calculated columns to show trends and performance indicators etc.

I've tried to explain as best I can visually below (if you need something a bit clearer, I can try and knock a better image up):

KPI List
-------
KPI
Target
Assigned To

Entry List
---------
KPI Selector (lookup on items in KPI List where Assigned To = [Me])
Target (auto populated based on choice made above)
Actual (single text, editable by user)

In short, I want Entry List.Target.Value = KPI List.Target.Value where KPI List.KPI.Value = Entry List.KPI Selector.Value

Thanks again!

EDIT: Just reread your post and saw your questions; WSS3 and lists are on the same site
 
Last edited:
Do you have SharePoint designer and visual studio? What you are trying to do is absolutly possible. Your lists are set up correctly in that they are on the same site and WSS3 has a whole host of things to help.

The options I see are:

1) Custom code it, use an event handler to pull the item id out of the source list. You can then save the fields in variables and populate the destination list on save. (I do this to turn leads into clients and create document libraries and permissions etc on the fly).

2) Use codeplex custom workflow actions to map fields between lists in a workflow you can auto kick off the workflow to populate the remaining (hiden) fields once the user has updated the input fields, again you would need the item ID to achieve this. Check this link for sp custom workflow actions

Either way wont be simple how confident are you with deploying custom code and workflows? Custom event recievers in sharepoint run from the GAC so you will need root access to the machine which will not be available on a shared hosted solution.
 
Last edited:
I've never done either, but have both SPD and VS installed.

Can you point me in the direction of some instructions/tutorial to achieve this?

Option 2 sounds the more straightforward, but option 1 sounds more appealing due to the other things I might be able to use it for!
 
For a start I could show you some c# code sample of an event reciever that I created which deals with some of our custom actions. I will have to find my WSS3 version as I had to re-write on 2010, I also tend to create a second application that will install my code to the GAC and associate it with a list so can also send that across.

Rather than share this in the forum which will be a pretty big post send me a quick email in trust and I will send a VS2008 solution file over for you to cast your eyes over. If you take one look and think WTF then it might not be for you otherwise I would be happy to help you through writing some code that works for you (its pretty simple and the object model is faily easy to navigate).
 
I have emailed you back a solution (slightly modified to protect our site). I have also added in comments to the code so that you can see what its doing in different places, if nothing else you will be able to see some of the things you can achieve easily in code.

BTW if your using WSS3 have you done the front end hack to give yourself MOSS navigation?

Edit: If you read this cotton, I forgot to mention in my email that with the navigation shown, all users are security trimmed and will only see the navigation for the sites that they have access to.
 
Last edited:
AS a bit of an update (and to ask more questions!) I've followed Vince's advice of using an Event Handler.

I've started to write this, but have little experience in writing C# so I could do with a bit of help.

Firstly, I'm using the ItemAdded method - I assume this is right, but I can see ItemAdding too...

I need to:

1. Get the value of the "task name" field from the item that has been added.
2. Using the task name, get the item from the "Tasks" list with corresponding task name.
3. Return the value of the "target" field from the "Tasks" list.
4. Set this value as the value for the "tasks" field in the added item.

I've googled for ideas but not come up with anything. Can anyone here point me in the right direction?

Thanks,
 
answers question 1 and the itemadded, adding:

Edit: ItemAdded and ItemAdding simply dictates at what time the code will run. ItemAdded will wait until the item is saved to the list, once the item is saved the code will take over. ItemAdding will run the code before the item is saved back. In your case it may be worth getting the code to run before the save that way all of the items fields will be available to you at the time of running where as if you wait until the item is added you will then have to address the new item which means making code to find it.

In my example this line of code will get the contents of the field named title and cast it to the variable "listTitle" from whichever list you install the event handler to (using the RegApp I sent across).

string listTitle = properties.AfterProperties["Title"].ToString();

2. Using the task name, get the item from the "Tasks" list with corresponding task name.

Once you have used the method above to address the current list and cast the field info to a variable you can then compare the field name in the required list to the variable "listTitle" by using a for loop. I have included an exaple of a for loop where I loop through role assignements on the list and remove known roles using an aray of possibilities before re-assigning. You could modify this so that instead of looping through the security properties you are looping through list items (would have to check the object model to nail this one down but its not overly tricky).

3/4: The key here is using variables to store the data and then updating the fields to the variable name. In the line of code below i create a new document library using a site template, I then set the name of the item as the variable name defined when casting the field name to a string.

Guid newListID = web.Lists.Add(listTitle, "", SPListTemplateType.DocumentLibrary);

I am building my new rig up tonight so wont be able to fire up my test bed but after the rig is built I could create a test environment and give you access, you could then recreate the list structure so that I could help you by being much more specific in helping you write the code.
 
Last edited:
Thanks Vince. I can sort of see what I need to do, but don't know the syntax and things, so the above helps.

When you say you've sent things across, have you sent me any more mail? If so I haven't received it, sorry. If you meant the first sln attachment you sent me, not sure if you've seen my reply with trouble opening it?

Again, thanks for your help!
 
Hi mate,

I sent a email with a new project file zipped up at about 3pm today. let me know if you don't have it.
 
Hi mate,

No, last mail I received from you was on Monday - not sure where it's got to?

Did you use the same gmail address?

Cheers,
 
I did indeed! It was sent from my work email address with a zip attachment at 3:15... *Edit*(I lie, it was 11:38am)... I am sending again now, hopefully it was not blocked by gmail for containing code?...

Ok I have sent it again, if you don't get it let me know and I will make it available on the web.
 
Last edited:
No - still no sign of it. Must be gmails filtering!

I can give you another address if you want or you can upload it somewhere.

Cheers,
 
Uploaded to some web space and emailed the address. Let me know once you have it and I will take it down. Cheers.
 
You must log in or register to reply here.
Back
Top Bottom