How do I write a reference from a drop down list to another sheet and have
the formula do the if function so that it can do several arguments and return
"direct" if true and "indirect" if false. I had posted this before but then
realized I hadn't been very clear about it. I hope this makes more sense.
Thanks!
Ed

its not clear to me at all.what sort of info is in the drop down box and how
is it related to the other sheet?What are the tests or arguments?
=if(Sheet2!b1=a1,"direct","indirect")
--
paul
remove nospam for email addy!



"milkman" wrote:

How do I write a reference from a drop down list to another sheet and have
the formula do the if function so that it can do several arguments and return
"direct" if true and "indirect" if false. I had posted this before but then
realized I hadn't been very clear about it. I hope this makes more sense.
Thanks!
Ed

"paul" wrote:

its not clear to me at all.what sort of info is in the drop down box and how
is it related to the other sheet?What are the tests or arguments?
=if(Sheet2!b1=a1,"direct","indirect")
--
paul
remove nospam for email addy!



"milkman" wrote:

How do I write a reference from a drop down list to another sheet and have
the formula do the if function so that it can do several arguments and return
"direct" if true and "indirect" if false. I had posted this before but then
realized I hadn't been very clear about it. I hope this makes more sense.
Thanks!
Ed

Paul: I decided to repost. The drop down list is a list of students. The
args are medications, assessment, paperwork, etc. The other sheet is named
with each individual students name per sheet and contains a column that
contains the interventions ie: meds, assessment, etc. The drop down list is
on a form I have to fill out for the school and the form has a column that is
to show whether or not the intervention was a direct intervention (working
with the student) or indirect (doing paper work,or meetings regarding the
student in question. The goal is to refer to the same row and appropriate
column on the particular students sheet and evaluate the text found there and
assign the appropriate "direct" or "indirect" to the cell.

"paul" wrote:

its not clear to me at all.what sort of info is in the drop down box and how
is it related to the other sheet?What are the tests or arguments?
=if(Sheet2!b1=a1,"direct","indirect")
--
paul
remove nospam for email addy!



"milkman" wrote:

How do I write a reference from a drop down list to another sheet and have
the formula do the if function so that it can do several arguments and return
"direct" if true and "indirect" if false. I had posted this before but then
realized I hadn't been very clear about it. I hope this makes more sense.
Thanks!
Ed

Paul: I reread my reply and realized it still comes up short on info. the
cell that the direct or indirect text is to show up on is on the sheet that
is the form for the school. The other columns on the form sheet are
populated by the =indirect formula and that works fine for that particular
data. However those columns of info are just direct transfer of info from
the particular students data base. The column on the form that needs to
evaluate and return an answer based on what the text in the interventions
column on the individual students data base is what perplexes me and since I
am relatively new at working with excel I find I am having trouble explaining
what I need so I do want to apologize for that.
"paul" wrote:

its not clear to me at all.what sort of info is in the drop down box and how
is it related to the other sheet?What are the tests or arguments?
=if(Sheet2!b1=a1,"direct","indirect")
--
paul
remove nospam for email addy!



"milkman" wrote:

How do I write a reference from a drop down list to another sheet and have
the formula do the if function so that it can do several arguments and return
"direct" if true and "indirect" if false. I had posted this before but then
realized I hadn't been very clear about it. I hope this makes more sense.
Thanks!
Ed

"milkman" wrote:
... The drop down list is a list of students.
The args are medications, assessment, paperwork, etc.
The other sheet is named with each individual students name
per sheet and contains a column that contains the interventions
ie: meds, assessment, etc. The drop down list is on a form
I have to fill out for the school and the form has a column that is
to show whether or not the intervention was a direct intervention
(working with the student) or indirect (doing paper work,
or meetings regarding the student in question).
The goal is to refer to the same row and appropriate
column on the particular students sheet and
evaluate the text found there and
assign the appropriate "direct" or "indirect" to the cell.

Here's one interp, and a play to try out ..

A sample construct is available at:
http://www.savefile.com/files/6980067
Lookup data from indiv student sheets, by row and by col_milkman_wks.xls

In A2:B4 in each student's sheet named: Stud1, Stud2 etc
are assumed data such as, for example in sheet: Stud1,

Subject1 Meds
Subject2 Assmt
Subject3 Paperwork

It is also assumed that the standard text phrases for interventions:
"Meds" or "Assmt" = "Direct"
"Paperwork" = "Indirect"
(Interp is necessary to derive the results as "Direct" or "Indirect")

Then in a sheet: Summary, where
Student names are listed in A2 down, viz.: Stud1, Stud2, etc
Subjects are listed in B1 across: Subject1, Subject2, etc

we could put in B2:
=VLOOKUP(INDEX(INDIRECT("'"&$A2&"'!B:B"),
MATCH(B$1,INDIRECT("'"&$A2&"'!A:A"),0)),
{"Meds","Direct";"Assmt","Direct";"Paperwork","Ind irect"},2,0)
and copy B2 across to D2, fill down to D4 to populate

The above will return the results:

........Subject1 Subject2 Subject3
Stud1 Direct Direct Indirect
Stud2 Direct Indirect Indirect
Stud3 Indirect Direct Direct
etc

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

no need to apologize,its hard to explain isnt it.I think the real strength of
this forum is the ability of the people who reply to interpret the questions!
--
paul
remove nospam for email addy!



"milkman" wrote:

Paul: I reread my reply and realized it still comes up short on info. the
cell that the direct or indirect text is to show up on is on the sheet that
is the form for the school. The other columns on the form sheet are
populated by the =indirect formula and that works fine for that particular
data. However those columns of info are just direct transfer of info from
the particular students data base. The column on the form that needs to
evaluate and return an answer based on what the text in the interventions
column on the individual students data base is what perplexes me and since I
am relatively new at working with excel I find I am having trouble explaining
what I need so I do want to apologize for that.
"paul" wrote:

its not clear to me at all.what sort of info is in the drop down box and how
is it related to the other sheet?What are the tests or arguments?
=if(Sheet2!b1=a1,"direct","indirect")
--
paul
remove nospam for email addy!



"milkman" wrote:

How do I write a reference from a drop down list to another sheet and have
the formula do the if function so that it can do several arguments and return
"direct" if true and "indirect" if false. I had posted this before but then
realized I hadn't been very clear about it. I hope this makes more sense.
Thanks!
Ed

Ok fella's Im gonna try again. I think Max is about right but doesn't
vlookup require the args or something to be in the farthest left column? My
knowledge so far is piece meal so I am not sure. I will try it again because
of the nature of the data base for each student. on the form sheet a3 is the
location of the drop down list that contains the student names column c is
where the "direct" or "indirect" return should show up. Now on the sheet
that contains the data base for a particular student column b contains the
interventions
that I do for the kids ie: meds, vitals, paper work, etc. As I understand
it these will be the arguments for the function. That is to say: any
intervention that required actual contact with the student should return
"direct" other wise return "Indirect". The data in the students data base is
sequential so writing the proper formula and filling down by auto fill should
work. "I think". You know guy's I cannot so far find any online list of how
to use the double and single quotes, parenthesis etc to write
functions/formulas. One of the folks on the site did give me the name of a
book that should help but I haven't been able to chase it down yet.
Thanks
Ed

"paul" wrote:

no need to apologize,its hard to explain isnt it.I think the real strength of
this forum is the ability of the people who reply to interpret the questions!
--
paul
remove nospam for email addy!



"milkman" wrote:

Paul: I reread my reply and realized it still comes up short on info. the
cell that the direct or indirect text is to show up on is on the sheet that
is the form for the school. The other columns on the form sheet are
populated by the =indirect formula and that works fine for that particular
data. However those columns of info are just direct transfer of info from
the particular students data base. The column on the form that needs to
evaluate and return an answer based on what the text in the interventions
column on the individual students data base is what perplexes me and since I
am relatively new at working with excel I find I am having trouble explaining
what I need so I do want to apologize for that.
"paul" wrote:

its not clear to me at all.what sort of info is in the drop down box and how
is it related to the other sheet?What are the tests or arguments?
=if(Sheet2!b1=a1,"direct","indirect")
--
paul
remove nospam for email addy!



"milkman" wrote:

How do I write a reference from a drop down list to another sheet and have
the formula do the if function so that it can do several arguments and return
"direct" if true and "indirect" if false. I had posted this before but then
realized I hadn't been very clear about it. I hope this makes more sense.
Thanks!
Ed

Can you upload a small sample copy of your file (sanitized if necessary) via
a free filehost** and then post the *link* to it in response here (the link
will be generated when you "upload" in the filehost (follow the instructions
there), then just copy the link and paste it into your reply to this post)

**Some free filehosts that could be used:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

For cjoint.com (it's in French), just click "Browse" button,
navigate to your folder select the sample file Open, then click the
button centred in the page below (labelled "Creer le lien Cjoint") and it'll
generate the link. Then copy & paste the generated link as part and parcel
of your response here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"milkman" wrote in message
...
Ok fella's Im gonna try again. I think Max is about right but doesn't
vlookup require the args or something to be in the farthest left column?
My
knowledge so far is piece meal so I am not sure. I will try it again
because
of the nature of the data base for each student. on the form sheet a3 is
the
location of the drop down list that contains the student names column c is
where the "direct" or "indirect" return should show up. Now on the sheet
that contains the data base for a particular student column b contains the
interventions
that I do for the kids ie: meds, vitals, paper work, etc. As I understand
it these will be the arguments for the function. That is to say: any
intervention that required actual contact with the student should return
"direct" other wise return "Indirect". The data in the students data base
is
sequential so writing the proper formula and filling down by auto fill
should
work. "I think". You know guy's I cannot so far find any online list of
how
to use the double and single quotes, parenthesis etc to write
functions/formulas. One of the folks on the site did give me the name of
a
book that should help but I haven't been able to chase it down yet.
Thanks
Ed