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">

 

[SilentPirate007]

Thanks!! BTW - where did you get the drop down menu?..Been looking for that one!

 

[scottylad2]

In sheet 2 create a list, say from a1 to a20 store you list of unique numbers there pressing control +t will let you create a table from that list, click to say it doesnt have headers. Also, while theyre all highlighted, click in the name box and name your table, something like UniqueNumber. In the cell where you'd like a drop down, hit Alt+D+L to open the datavalidation dialogue, select list, and if you hit F3 you can then click on the named list you created, UniqueNumbers then click ok. You should now have a drop down. If you want to add other numbers onto it, click back to your table and with the bottom number highlighted, hit the tab key then add new number. Because you created it as a table, your dropdown list will update as well.

 

[marki]

- 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 id=jsProxy type=hidden *******="jsCall();">

click in the box H4 then =VLOOKUP $D$4 (in the first line) click into the second line on the vlookup box which i think says tablearray then highlight all the data you want searching, on your example would be A2 toD5,click in the next line down on the vlookup box and enter the column no. which on your example would be 2(ben representing 1001) click into the last line on the vlookup box and type FALSE. what should happen is when you put a number in H4 will search in the area you selected and return the answer from the next column. when you drag down the formula to look for address it will still look at D4 because you lock that cell with $ but you will need to change the 2 to a 3 in the formula and then to 4 when you drag to postcode. hope i've been some help

 

[SilentPirate007]

Thank to everyone for you assistance, i have managed to get it fully working now....

I do however, have another thing I would like to add and it may be a bit off the original topic.

Example: I have 1st quarter, ... ,4th quarter. When using a drop menu to select 4th quarter another cell should reveal the final conclusion. The conclusion should only be visible if I were to select 4th quarter. If I were to select 1st/2nd/3rd quarter, the cell should be invisible.

Here is an example:
http://i893.photobucket.com/albums/ac137/SilentPirate007/Quarter1.jpg

http://i893.photobucket.com/albums/ac137/SilentPirate007/Quarter4.jpg

 

[xenou]

You can use conditional formatting on the cell:
Condition: ($D$4<>"4th Quarter")
Conditional Format: White Font, No Fill, etc.

This renders the cell more or less invisible for general purposes, though the value is still in the cell (you could probably see its "shadow" if you select the cell by dragging your mouse through it).

 

[scottylad2]

Assuming yiur drop down is in A1 and it contains Q1 Q2 Q3 and Q4, and the cell with your formula is in B1 you could use an =If(A1="Q4",your formula here,"") which broken down says if A1 shows Q4 show the formula results. The double "" means blank, so if no Q4 show a blank cell. If your Q1 etc contains any text you need to wrap that part of th If in quotes, but if its just numbers no quotes required