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

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 "direct" or "indirect" 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
http://www.savefile.com/files/4530191 if I did it correctly.
Ed

"Max" wrote:

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

One way ..

Sample implementation at:
http://cjoint.com/?cicQQKOJVu
Tagging based on keyword search.xls

In sheet: school log,

Put in D5 (normal ENTER), then copy down:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(T(INDIRECT("'" & $A$3 & "'!B" & ROW(F2))),
{"vitals";"G tube";"Cath";"Insulin/meds";"meds"})))0,"D","O")

The list of key words used in the formula (listed below) was derived from
what was indicated in your previous IF formula in col D:

vitals
G tube
Cath
Insulin/meds
meds

Adapt the list to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"milkman" wrote in message
...
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 "direct" or "indirect" 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
http://www.savefile.com/files/4530191 if I did it correctly.
Ed

Thanks Max and Paul !!!!!!!!!!! Especially for your patients. Max your
formula works. I can't wait till I can get the book on formulas so I can
better understand these matters!
Ed

"Max" wrote:

One way ..

Sample implementation at:
http://cjoint.com/?cicQQKOJVu
Tagging based on keyword search.xls

In sheet: school log,

Put in D5 (normal ENTER), then copy down:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(T(INDIRECT("'" & $A$3 & "'!B" & ROW(F2))),
{"vitals";"G tube";"Cath";"Insulin/meds";"meds"})))0,"D","O")

The list of key words used in the formula (listed below) was derived from
what was indicated in your previous IF formula in col D:

vitals
G tube
Cath
Insulin/meds
meds

Adapt the list to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"milkman" wrote in message
...
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 "direct" or "indirect" 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
http://www.savefile.com/files/4530191 if I did it correctly.
Ed

Pleasure` Ed !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"milkman" wrote in message
...
Thanks Max and Paul !!!!!!!!!!! Especially for your patients. Max your
formula works. I can't wait till I can get the book on formulas so I can
better understand these matters!
Ed

Max: If I might impose once more. I was looking at the workbook in question
as to if it is ready to use and I realized that I had not provided for some
means to cause the month drop down in A1 of the school log to populate the
sheet with only the data for that month. First off, is that even possible?
The reason I am asking you is that you have seen the workbook and know what
it is layed out like.
Thanks!
Ed

"Max" wrote:

Pleasure` Ed !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"milkman" wrote in message
...
Thanks Max and Paul !!!!!!!!!!! Especially for your patients. Max your
formula works. I can't wait till I can get the book on formulas so I can
better understand these matters!
Ed

"milkman" wrote
.. I was looking at the workbook in question
as to if it is ready to use and I realized that
I had not provided for some means to cause
the month drop down in A1 of the school log
to populate the sheet with only the data for that month.
First off, is that even possible?

Here's one way, using non-array formulas ..
(a rather extensive revamp is required ..)

A sample implementation is available at:
http://cjoint.com/?cji7RsnnDT
Multi-Criteria Extract n Tagging_based_on_keyword_search.xls

In sheet: school log,

Put in A5:F5 (all formulas normal ENTER)

In A5:
=IF(ISERROR(SMALL($F:$F,ROW(A1))),"",
INDEX(OFFSET(INDIRECT("'"&$A$3&"'!A:A"),,2),MATCH( SMALL($F:$F,ROW(A1)),$F:$F
,0)-3))

In B5:
=IF(ISERROR(SMALL($F:$F,ROW(B1))),"",
INDEX(OFFSET(INDIRECT("'"&$A$3&"'!A:A"),,3),MATCH( SMALL($F:$F,ROW(B1)),$F:$F
,0)-3))

In C5:
=IF(ISERROR(SMALL($F:$F,ROW(C1))),"",
INDEX(OFFSET(INDIRECT("'"&$A$3&"'!A:A"),,5),MATCH( SMALL($F:$F,ROW(C1)),$F:$F
,0)-3))

In D5:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(INDEX(OFFSET(INDIRECT("'"&$A$3&"'! A:A"),,1)
,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)-3),{"vitals";"G
tube";"Cath";"Insulin/meds";"meds"})))0,"D",IF(ISERROR(SMALL($F:$F,ROW( A1))
),"","O"))

In E5:
=IF(ISERROR(SMALL($F:$F,ROW(E1))),"",
INDEX(OFFSET(INDIRECT("'"&$A$3&"'!A:A"),,7),MATCH( SMALL($F:$F,ROW(E1)),$F:$F
,0)-3))

In F5:
=IF(AND(INDIRECT("'"&$A$3&"'!A"&ROW(A2))=$A$3,MONT H(INDIRECT("'"&$A$3&"'!E"&
ROW(A2)))=MATCH($A$1,{"January";"February";"March" ;"April";"May";"June";"Jul
y";"August";"September";"October";"November";"Dece mber"},0)),ROW(A1),"")

(Col F is a criteria col)

Select A5:F5, fill down as far as required
(Fill down by the smallest extent large enough
to cover the max expected number of rows for any month)

The above will return the desired results, i.e. only the lines for the
particular month indicated in A1's droplist, and from the student's sheet
(name indicated in A3's droplist). Test it out ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Here's a more enduring link to the sample implementation:
http://www.savefile.com/files/9355685
Multi-Criteria Extract n Tagging_based_on_keyword_search.xls

(earlier cjoint's link expires in 14 days)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max: Just a quick note.
I was trying your last idea but was so tired I think I incorrectly wrote the
formula as it didn't work. We know however that it will due to the fact that
you wrote it in a copy of my work. Anyway, I have had to back burner that
project to work on others. I will get back with you when I can return to the
excel work. Again, thanks!
Ed

"Max" wrote:

Here's a more enduring link to the sample implementation:
http://www.savefile.com/files/9355685
Multi-Criteria Extract n Tagging_based_on_keyword_search.xls

(earlier cjoint's link expires in 14 days)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Ed, thanks for the closure note.

You might have hit some adaptation? / typo? problems. Perhaps just try
direct copy paste from the sample file's formula bar to your sheet's
formula bar, then edit to suit. Ensure that the sheetnames referenced by
INDIRECT match exactly (except for case) what's on the sheet tabs. Look-out
for any "extra" spaces within actual sheetnames (on tabs) vs what's
indicated within the cells, or vice-versa. All it takes is an extra space
somewhere (or a simple typo, maybe a "1" mistaken for an "l") to throw the
matching off.

Retain the earlier sample file (with the working implementation) as the
reference point. When you're back onto this in future, if you still can't
solve the adaption issues, just put in as new post(s) to the newsgroup,
giving the relevant details (paste the actual formulas which didn't work,
for example), and I'm sure there would be responders around then who would
be able to help. All the best.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"milkman" wrote in message
...
Max: Just a quick note.
I was trying your last idea but was so tired I think I incorrectly wrote
the
formula as it didn't work. We know however that it will due to the fact
that
you wrote it in a copy of my work. Anyway, I have had to back burner that
project to work on others. I will get back with you when I can return to
the
excel work. Again, thanks!
Ed