Linking tasks (lookup...??)

[SilentPirate007]

- Hello -

I'm busy with a task, but I am a little stuck...

I've created a table containing information on people. Attached to each person is a unique number, address, postcode etc...On a new sheet, I have a tool which is linked to the unique number (I named the numbers 1001, 1002, 1003... - "numbers"), so when I use a drop down scroller, I see each unique number and I could then select one. The problem is, I would like to link to it with another tool (in the same sheet however), so that when I select one of the unique numbers, it will display the attached credentials (name, address, postcode, etc...). I have attached some images to give you a rough idea of what im trying to do:

First--
http://i893.photobucket.com/albums/ac137/SilentPirate007/GUI_1.jpg

Second--
http://i893.photobucket.com/albums/ac137/SilentPirate007/GUI_2.jpg
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[xenou]

You should be able to get what you need with Index() and Match() formulas.

Try looking over the examples here:
http://www.contextures.com/xlFunctions03.html

I'd also suggest dynamic range names for your lookup ranges:
http://support.microsoft.com/kb/830287

Let us know if you can't get it going and we'll look at it some more together.
ξ

 

[SilentPirate007]

I am still a little confused I'm afraid. I have linked the information in the table to the Number tool, which displays the unique numbers (I have linked just the unique numbers, which have been assigned a dinamic range name). The only step i'm missing now, is when I select 1002, for example, that the name, address and postcode be displayed on the tool (shown in the 2nd link I posted).

If no number was selected, then the result would look as such:

Number: Name:
Address:
Postcode:

If I were to choose unique number 1002, the result would be:

Number: 1002 Name: Time
Address: Address 2
Postcode: 10

Thanks for you help!
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[xenou]

Assumptions:

• your drop down value is in Cell D4
• you have a named range LKPNumbers with the column of unique reference numbers
• you have a named range LKPNames with the parallel column containing names
• you have a named range LKPAddresses with the parallel column containing Addresses
• you have a named range LKPPostCodes with the parallel column containing post codes

This formula could go in H4 (or any cell) - it should return a name or #N/A if the unique reference number does not exist:
=INDEX(LKPNames,MATCH(D4,LKPNumbers,0))

Essentially what the formula does is (starting in the inside nested Match) finds the row number in the LKPNumbers range that matches the value in D4. Then it uses that row number to find the corresponding row in the LKPNames range - the corresponding name. You must be careful that all the ranges have the same dimensions (they should be parallel in height, starting in the same row and ending in the same row)

 

[SilentPirate007]

We are very close! However, you may or may not have noticed that the scrollbar is not linked to D4, so no matter what unique number I select, it returns #N/A - Which is expected...Should I link the scrollbar to D4 and if so how would I do it?

Thanks ever so much for your assistance!
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[SilentPirate007]

OK, I figured out how to link it, but it still does not return any result other than #N/A.

When I named the ranges, I simply selected A2:A6, for example, and called it 'Name'...Does this contradict the method you're using?
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[xenou]

The names you use shouldn't matter, as long as you've employed them correctly. I would actually avoid the name *Name* as this is the name of an Excel Name object (though it probably doesn't matter).

A common problem with lookups is values stored as text vs. values stored as numbers. If you can "add up" the values 1001, 1002, 1003 in your data then they are true numbers (i.e., using Sum() formula). Otherwise, they are text. Anyway, it should work ...

here's a sample file with a simple demonstration:
<a href="http://northernocean.net/etc/mrexcel/20100213_samplewithlookups.zip">Sample Lookup Workbook</a>

Hope this helps.
ξ

 

[scottylad2]

You'll also have to have your table where you would like your answer set up in the same way as the table your pulling data from, you've got both a vertical and a horizontal table showing, lookups won't cope with that

 

[scottylad2]

Excel Workbook
	A	B	C	D	E
2		Name	Address 1	postcode	Date
3	1	person1	street 1	post 1	01/01/2010
4	2	person2	street 2	post 2	02/01/2010
5	3	person3	street 3	post 3	03/01/2010
6	4	person4	street 4	post 4	04/01/2010
7	5	person5	street 5	post 5	05/01/2010
8	6	person6	street 6	post 6	06/01/2010
9	7	person7	street 7	post 7	07/01/2010
10	8	person8	street 8	post 8	08/01/2010
11	9	person9	street 9	post 9	09/01/2010
12	10	person10	street 10	post 10	10/01/2010
Sheet2

this is my table of data, with unique reference for each person details

and on my front page this works with a vlookup.

formula in B2 and dragged acroos

Excel Workbook
	A	B	C	D	E
2	4	person4	street 4	post 4	04/01/2010
Sheet1

 

[scottylad2]

My uniqe referewnces are in a drop down in A2