software

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
quot;directquot; if true and quot;indirectquot; 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,quot;directquot;,quot;indirectquot;)
--
paul
remove nospam for email addy!
quot;milkmanquot; wrote:

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



quot;paulquot; wrote:

gt; its not clear to me at all.what sort of info is in the drop down box and how
gt; is it related to the other sheet?What are the tests or arguments?
gt; =if(Sheet2!b1=a1,quot;directquot;,quot;indirectquot;)
gt; --
gt; paul
gt; remove nospam for email addy!
gt;
gt;
gt;
gt; quot;milkmanquot; wrote:
gt;
gt; gt; How do I write a reference from a drop down list to another sheet and have
gt; gt; the formula do the if function so that it can do several arguments and return
gt; gt; quot;directquot; if true and quot;indirectquot; if false. I had posted this before but then
gt; gt; realized I hadn't been very clear about it. I hope this makes more sense.
gt; gt; Thanks!
gt; gt; 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 quot;directquot; or quot;indirectquot; to the cell.

quot;paulquot; wrote:

gt; its not clear to me at all.what sort of info is in the drop down box and how
gt; is it related to the other sheet?What are the tests or arguments?
gt; =if(Sheet2!b1=a1,quot;directquot;,quot;indirectquot;)
gt; --
gt; paul
gt; remove nospam for email addy!
gt;
gt;
gt;
gt; quot;milkmanquot; wrote:
gt;
gt; gt; How do I write a reference from a drop down list to another sheet and have
gt; gt; the formula do the if function so that it can do several arguments and return
gt; gt; quot;directquot; if true and quot;indirectquot; if false. I had posted this before but then
gt; gt; realized I hadn't been very clear about it. I hope this makes more sense.
gt; gt; Thanks!
gt; gt; 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.
quot;paulquot; wrote:

gt; its not clear to me at all.what sort of info is in the drop down box and how
gt; is it related to the other sheet?What are the tests or arguments?
gt; =if(Sheet2!b1=a1,quot;directquot;,quot;indirectquot;)
gt; --
gt; paul
gt; remove nospam for email addy!
gt;
gt;
gt;
gt; quot;milkmanquot; wrote:
gt;
gt; gt; How do I write a reference from a drop down list to another sheet and have
gt; gt; the formula do the if function so that it can do several arguments and return
gt; gt; quot;directquot; if true and quot;indirectquot; if false. I had posted this before but then
gt; gt; realized I hadn't been very clear about it. I hope this makes more sense.
gt; gt; Thanks!
gt; gt; Ed

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

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

A sample construct is available at:
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:
quot;Medsquot; or quot;Assmtquot; = quot;Directquot;
quot;Paperworkquot; = quot;Indirectquot;
(Interp is necessary to derive the results as quot;Directquot; or quot;Indirectquot;)

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(quot;'quot;amp;$A2amp;quot;'!B:Bquot;),
MATCH(B$1,INDIRECT(quot;'quot;amp;$A2amp;quot;'!A:Aquot;),0)),
{quot;Medsquot;,quot;Directquot;;quot;Assmtquot;,quot;Directquot;;quot;Paperworkquot;,quot;Ind irectquot;},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
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!
quot;milkmanquot; wrote:

gt; Paul: I reread my reply and realized it still comes up short on info. the
gt; cell that the direct or indirect text is to show up on is on the sheet that
gt; is the form for the school. The other columns on the form sheet are
gt; populated by the =indirect formula and that works fine for that particular
gt; data. However those columns of info are just direct transfer of info from
gt; the particular students data base. The column on the form that needs to
gt; evaluate and return an answer based on what the text in the interventions
gt; column on the individual students data base is what perplexes me and since I
gt; am relatively new at working with excel I find I am having trouble explaining
gt; what I need so I do want to apologize for that.
gt; quot;paulquot; wrote:
gt;
gt; gt; its not clear to me at all.what sort of info is in the drop down box and how
gt; gt; is it related to the other sheet?What are the tests or arguments?
gt; gt; =if(Sheet2!b1=a1,quot;directquot;,quot;indirectquot;)
gt; gt; --
gt; gt; paul
gt; gt; remove nospam for email addy!
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;milkmanquot; wrote:
gt; gt;
gt; gt; gt; How do I write a reference from a drop down list to another sheet and have
gt; gt; gt; the formula do the if function so that it can do several arguments and return
gt; gt; gt; quot;directquot; if true and quot;indirectquot; if false. I had posted this before but then
gt; gt; gt; realized I hadn't been very clear about it. I hope this makes more sense.
gt; gt; gt; Thanks!
gt; gt; gt; 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 quot;directquot; or quot;indirectquot; 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
quot;directquot; other wise return quot;Indirectquot;. The data in the students data base is
sequential so writing the proper formula and filling down by auto fill should
work. quot;I thinkquot;. 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

quot;paulquot; wrote:

gt; no need to apologize,its hard to explain isnt it.I think the real strength of
gt; this forum is the ability of the people who reply to interpret the questions!
gt; --
gt; paul
gt; remove nospam for email addy!
gt;
gt;
gt;
gt; quot;milkmanquot; wrote:
gt;
gt; gt; Paul: I reread my reply and realized it still comes up short on info. the
gt; gt; cell that the direct or indirect text is to show up on is on the sheet that
gt; gt; is the form for the school. The other columns on the form sheet are
gt; gt; populated by the =indirect formula and that works fine for that particular
gt; gt; data. However those columns of info are just direct transfer of info from
gt; gt; the particular students data base. The column on the form that needs to
gt; gt; evaluate and return an answer based on what the text in the interventions
gt; gt; column on the individual students data base is what perplexes me and since I
gt; gt; am relatively new at working with excel I find I am having trouble explaining
gt; gt; what I need so I do want to apologize for that.
gt; gt; quot;paulquot; wrote:
gt; gt;
gt; gt; gt; its not clear to me at all.what sort of info is in the drop down box and how
gt; gt; gt; is it related to the other sheet?What are the tests or arguments?
gt; gt; gt; =if(Sheet2!b1=a1,quot;directquot;,quot;indirectquot;)
gt; gt; gt; --
gt; gt; gt; paul
gt; gt; gt; remove nospam for email addy!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;milkmanquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; How do I write a reference from a drop down list to another sheet and have
gt; gt; gt; gt; the formula do the if function so that it can do several arguments and return
gt; gt; gt; gt; quot;directquot; if true and quot;indirectquot; if false. I had posted this before but then
gt; gt; gt; gt; realized I hadn't been very clear about it. I hope this makes more sense.
gt; gt; gt; gt; Thanks!
gt; gt; gt; gt; 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 quot;uploadquot; 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:
www.flypicture.com/
cjoint.com/index.php
www.savefile.com/index.php

For cjoint.com (it's in French), just click quot;Browsequot; button,
navigate to your folder gt; select the sample file gt; Open, then click the
button centred in the page below (labelled quot;Creer le lien Cjointquot;) and it'll
generate the link. Then copy amp; paste the generated link as part and parcel
of your response here.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;milkmanquot; gt; wrote in message
...
gt; Ok fella's Im gonna try again. I think Max is about right but doesn't
gt; vlookup require the args or something to be in the farthest left column?
My
gt; knowledge so far is piece meal so I am not sure. I will try it again
because
gt; of the nature of the data base for each student. on the form sheet a3 is
the
gt; location of the drop down list that contains the student names column c is
gt; where the quot;directquot; or quot;indirectquot; return should show up. Now on the sheet
gt; that contains the data base for a particular student column b contains the
gt; interventions
gt; that I do for the kids ie: meds, vitals, paper work, etc. As I understand
gt; it these will be the arguments for the function. That is to say: any
gt; intervention that required actual contact with the student should return
gt; quot;directquot; other wise return quot;Indirectquot;. The data in the students data base
is
gt; sequential so writing the proper formula and filling down by auto fill
should
gt; work. quot;I thinkquot;. You know guy's I cannot so far find any online list of
how
gt; to use the double and single quotes, parenthesis etc to write
gt; functions/formulas. One of the folks on the site did give me the name of
a
gt; book that should help but I haven't been able to chase it down yet.
gt; Thanks
gt; Ed
I will try to send a copy of the workbook in question with the names changed.
I am trying to get column D5 down on the sheet called school log to populate
with quot;directquot; or quot;indirectquot; based on column b on the individual worksheet
named with the name selected in the drop down in a3 on the school log. Of
course the return is to be evaluated as I indicated in one of my previous
post. Thanks for all of your patience. The file should be at
www.savefile.com/files/4530191 if I did it correctly.
Ed

quot;Maxquot; wrote:

gt; Can you upload a small sample copy of your file (sanitized if necessary) via
gt; a free filehost** and then post the *link* to it in response here (the link
gt; will be generated when you quot;uploadquot; in the filehost (follow the instructions
gt; there), then just copy the link and paste it into your reply to this post)
gt;
gt; **Some free filehosts that could be used:
gt; www.flypicture.com/
gt; cjoint.com/index.php
gt; www.savefile.com/index.php
gt;
gt; For cjoint.com (it's in French), just click quot;Browsequot; button,
gt; navigate to your folder gt; select the sample file gt; Open, then click the
gt; button centred in the page below (labelled quot;Creer le lien Cjointquot;) and it'll
gt; generate the link. Then copy amp; paste the generated link as part and parcel
gt; of your response here.
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;milkmanquot; gt; wrote in message
gt; ...
gt; gt; Ok fella's Im gonna try again. I think Max is about right but doesn't
gt; gt; vlookup require the args or something to be in the farthest left column?
gt; My
gt; gt; knowledge so far is piece meal so I am not sure. I will try it again
gt; because
gt; gt; of the nature of the data base for each student. on the form sheet a3 is
gt; the
gt; gt; location of the drop down list that contains the student names column c is
gt; gt; where the quot;directquot; or quot;indirectquot; return should show up. Now on the sheet
gt; gt; that contains the data base for a particular student column b contains the
gt; gt; interventions
gt; gt; that I do for the kids ie: meds, vitals, paper work, etc. As I understand
gt; gt; it these will be the arguments for the function. That is to say: any
gt; gt; intervention that required actual contact with the student should return
gt; gt; quot;directquot; other wise return quot;Indirectquot;. The data in the students data base
gt; is
gt; gt; sequential so writing the proper formula and filling down by auto fill
gt; should
gt; gt; work. quot;I thinkquot;. You know guy's I cannot so far find any online list of
gt; how
gt; gt; to use the double and single quotes, parenthesis etc to write
gt; gt; functions/formulas. One of the folks on the site did give me the name of
gt; a
gt; gt; book that should help but I haven't been able to chase it down yet.
gt; gt; Thanks
gt; gt; Ed
gt;
gt;
gt;