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,
 
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
 
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:
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!
 
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,
 
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,

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,
 
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,
 
You must log in or register to reply here.
Back
Top Bottom