Reference

M

milkman

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
 
P

paul

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

milkman

paul said:
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")
Click to expand...
 
M

milkman

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.
 
M

milkman

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.
 
M

Max

milkman said:
... 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.
Click to expand...

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","Indirect"},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 ..
 
P

paul

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!
 
M

milkman

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
 
M

Max

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.
 
M

milkman

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
 
M

Max

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 ..
 
M

milkman

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
 
M

milkman

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
 
M

Max

.. 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?
Click to expand...

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,MONTH(INDIRECT("'"&$A$3&"'!E"&
ROW(A2)))=MATCH($A$1,{"January";"February";"March";"April";"May";"June";"Jul
y";"August";"September";"October";"November";"December"},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 ..
 
M

milkman

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
 
M

Max

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.
 
Top