Drop down list & reference cells...

  • Thread starter Thread starter techprot-google04
  • Start date Start date
T

techprot-google04

Three questions:

If I create a spreadsheet with two values, say item and time (to
complete task), can I set a cell in another spreadsheet, same workbook,
to pick from the item column?

Now, if that is possible... how can I set it up so that when I pick an
item the time cell is updated with the time for the item picked. For
example: I select, Daily Checklist, and the field to the right is
updated based on the value in the spreadsheet I mentioned above. Make
sense...???

Finally, Is it possible display a list of those where are not selected,
so I have have a "remaining" items list?

I've been fumbling around and haven't quite figured this one out.
Thanks,
-a
 
Hi

if i'm understanding you correctly and you have a list with both item & time
filled in and you want a list where you can choose the item & the time is
automatically "copied" from your original list to another cell then you can
use Data Validation & VLOOKUP to do this.

Basically, name your item list
- select the list of items including the heading - which i'm assuming is
"item" and choose insert / name / create, ensure "first row" is checked and
then click ok. You're created a list called item
-while you're hear select from the first item (not the heading) in the item
column to the last entry in the time column, click in the name box to the
left of the formula bar and type
mylist
and press Enter. You're created another range called "mylist" which we'll
use later.

then create the drop down
- click in the cell(s) where you want the drop down list to appear and
choose data / validation - on the settings tab choose list - click in the
source box and press the F3 key, and choose Item from the dialog box - click
OK and then Ok again. Now you have your drop down list

now populate the associated data
- click in the cell next to the first drop down cell (created above -
assuming cell E2) and type
=VLOOKUP(E2,mylist,2,0)
this means, lookup what is in E2, in the first column of "mylist" and return
the data from the column next to it where there is an exact match
if E2 is blank this formula will return an #NA error so modify it to read
=IF(ISNA(VLOOKUP(E2,mylist,2,0)),"",VLOOKUP(E2,mylist,2,0))

now filldown as far as necessary. This will work equally as well on one
worksheet or on different worksheets within the same workbook.

as far as your last question goes - about the remaining items list, i'm not
sure how to achieve this. You might like to check out Debra Dalgleish's
site under Data Validation to see if she has anything you can use there (i'm
not able to get i'net at the moment) ... www.contextures.com/tiptech.html

Hope this helps
Cheers
JulieD
 
Back
Top