Nested if 10 / Vlookup Therapist

I am working on a Scheduling program for a counseling center.  Thi
center has 10 + therapists and 20 + clients.  The plan is for th
manager to only have to enter the clients name into the therapist page
and to have the therapists name auto populate into the clients page.  
can get this to work fine with 7 therapists (the limit for nested i
statements) but I cannot figure out how to get vlookup or any othe
function to work.

ohh by the way the or() statements are used because there are 
possibilities for the clients name to be entere
(CLIENT/CLIENTc/CLIENTp)





Code
-------------------
  =(IF(OR(Therapist1!B5=PSR1!$B$1,Therapist1!B5=PSR1!$B$1&PSR1!$C$1,Therapist1!B5=PSR1!$B$1&PSR1!$D$1),Therapist1!$B$1,(IF(OR(Therapist2!B5=PSR1!$B$1,Therapist2!B5=PSR1!$B$1&PSR1!$C$1,Therapist2!B5=PSR1!$B$1&PSR1!$D$1),Therapist2!$B$1,(IF(OR(Therapist3!B5=PSR1!$B$1,Therapist3!B5=PSR1!$B$1&PSR1!$C$1,Therapist3!B5=PSR1!$B$1&PSR1!$D$1),Therapist3!$B$1,(IF(OR(Therapist4!B5=PSR1!$B$1,Therapist4!B5=PSR1!$B$1&PSR1!$C$1,Therapist4!B5=PSR1!$B$1&PSR1!$D$1),Therapist4!$B$1,(IF(OR(Therapist5!B5=PSR1!$B$1,Therapist5!B5=PSR1!$B$1&PSR1!$C$1,Therapist5!B5=PSR1!$B$1&PSR1!$D$1),Therapist5!$B$1,(IF(OR(Therapist6!B5=PSR1!$B$1,Therapist6!B5=PSR1!$B$1&PSR1!$C$1,Therapist6!B5=PSR1!$B$1&PSR1!$D$1),Therapist6!$B$1,(IF(OR(Therapist7!B5=PSR1!$B$1,Therapist7!B5=PSR1!$B$1&PSR1!$C$1,Therapist7!B5=PSR1!$B$1&PSR1!$D$1),Therapist7!$B$1,(IF(OR(Therapist8!B5=PSR1!$B$1,Therapist8!B5=PSR1!$B$1&PSR1!$C$1,Therapist8!B5=PSR1!$B$1&PSR1!$D$1),Therapist8!$B$1,(IF(OR(Therapist9!B5=PSR1!$B$1,Therapist9!B5=PSR1!$B$1&PSR1!$C$1,Therapist9!B5=PSR1!$B$1&PSR1!$D$1),Therapist9!$B$1,(IF(OR(Therapista!B5=PSR1!$B$1,Therapista!B5=PSR1!$B$1&PSR1!$C$1,Therapista!B5=PSR1!$B$1&PSR1!$D$1),TherapistA!$B$1,��)))))))))))))))))))
-------------------


--
Message posted from http://www.ExcelForum.com

0
5/25/2004 4:29:42 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
387 Views

Similar Articles

[PageSpeed] 23

I have also tried using "&" instead of nesting as explained on page
http://www.excelforum.com/showthread.php?s=&threadid=217176&highlight=nested+if+vlookup

I get an erroe saying "Formula is too long"

the code I tried

=IF(OR(Therapist1!B5=PSR1!$B$1,Therapist1!B5=PSR1!$B$1&PSR1!$C$1,Therapist1!B5=PSR1!$B$1&PSR1!$D$1),Therapist1!$B$1,��)&IF(OR(Therapist2!B5=PSR1!$B$1,Therapist2!B5=PSR1!$B$1&PSR1!$C$1,Therapist2!B5=PSR1!$B$1&PSR1!$D$1),Therapist2!$B$1,��)&IF(OR(Therapist3!B5=PSR1!$B$1,Therapist3!B5=PSR1!$B$1&PSR1!$C$1,Therapist3!B5=PSR1!$B$1&PSR1!$D$1),Therapist3!$B$1,��)&IF(OR(Therapist4!B5=PSR1!$B$1,Therapist4!B5=PSR1!$B$1&PSR1!$C$1,Therapist4!B5=PSR1!$B$1&PSR1!$D$1),Therapist4!$B$1,��)&IF(OR(Therapist5!B5=PSR1!$B$1,Therapist5!B5=PSR1!$B$1&PSR1!$C$1,Therapist5!B5=PSR1!$B$1&PSR1!$D$1),Therapist5!$B$1,��)&IF(OR(Therapist6!B5=PSR1!$B$1,Therapist6!B5=PSR1!$B$1&PSR1!$C$1,Therapist6!B5=PSR1!$B$1&PSR1!$D$1),Therapist6!$B$1,��)&IF(OR(Therapist7!B5=PSR1!$B$1,Therapist7!B5=PSR1!$B$1&PSR1!$C$1,Therapist7!B5=PSR1!$B$1&PSR1!$D$1),Therapist7!$B$1,��)&IF(OR(Therapist8!B5=PSR1!$B$1,Therapist8!B5=PSR1!$B$1&PSR1!$C$1,Therapist8!B5=PSR1!$B$1&PSR1!$D$1),Therapist8!$B$1,��)&IF(OR(Therapist9!B5=PSR1!$B$1,Therapist9!B5=PSR1!$B$1&PSR1!$C$1,Therapist9!B5=PSR1!$B$1&PSR1!$D$1),Therapist9!$B$1,��)&IF(OR(TherapistA!B5=PSR1!$B$1,TherapistA!B5=PSR1!$B$1&PSR1!$C$1,TherapistA!B5=PSR1!$B$1&PSR1!$D$1),TherapistA!$B$1,��

--
Message posted from http://www.ExcelForum.com

0
5/25/2004 4:32:49 AM
Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany

"seskridge >" <<seskridge.16so7g@excelforum-nospam.com> schrieb im
Newsbeitrag news:seskridge.16so7g@excelforum-nospam.com...
> I am working on a Scheduling program for a counseling center.  This
> center has 10 + therapists and 20 + clients.  The plan is for the
> manager to only have to enter the clients name into the therapist
pages
> and to have the therapists name auto populate into the clients page.
I
> can get this to work fine with 7 therapists (the limit for nested if
> statements) but I cannot figure out how to get vlookup or any other
> function to work.
>
> ohh by the way the or() statements are used because there are 3
> possibilities for the clients name to be entered
> (CLIENT/CLIENTc/CLIENTp)
>
>
>
>
>
> Code:
> --------------------
>
=(IF(OR(Therapist1!B5=PSR1!$B$1,Therapist1!B5=PSR1!$B$1&PSR1!$C$1,Thera
pist1!B5=PSR1!$B$1&PSR1!$D$1),Therapist1!$B$1,(IF(OR(Therapist2!B5=PSR1
!$B$1,Therapist2!B5=PSR1!$B$1&PSR1!$C$1,Therapist2!B5=PSR1!$B$1&PSR1!$D
$1),Therapist2!$B$1,(IF(OR(Therapist3!B5=PSR1!$B$1,Therapist3!B5=PSR1!$
B$1&PSR1!$C$1,Therapist3!B5=PSR1!$B$1&PSR1!$D$1),Therapist3!$B$1,(IF(OR
(Therapist4!B5=PSR1!$B$1,Therapist4!B5=PSR1!$B$1&PSR1!$C$1,Therapist4!B
5=PSR1!$B$1&PSR1!$D$1),Therapist4!$B$1,(IF(OR(Therapist5!B5=PSR1!$B$1,T
herapist5!B5=PSR1!$B$1&PSR1!$C$1,Therapist5!B5=PSR1!$B$1&PSR1!$D$1),The
rapist5!$B$1,(IF(OR(Therapist6!B5=PSR1!$B$1,Therapist6!B5=PSR1!$B$1&PSR
1!$C$1,Therapist6!B5=PSR1!$B$1&PSR1!$D$1),Therapist6!$B$1,(IF(OR(Therap
ist7!B5=PSR1!$B$1,Therapist7!B5=PSR1!$B$1&PSR1!$C$1,Therapist7!B5=PSR1!
$B$1&PSR1!$D$1),Therapist7!$B$1,(IF(OR(Therapist8!B5=PSR1!$B$1,Therapis
t8!B5=PSR1!$B$1&PSR1!$C$1,Therapist8!B5=PSR1!$B$1&PSR1!$D$1),Therapist8
!$B$1,(IF(OR(Therapist9!B5=PSR1!$B$1,Therapist9!B5=PSR1!$B$1&PSR1!$C$1,
Therapist9!B5=PSR1!$B$1&PSR1!$D$1),Therapist9!$B$1,(IF(OR(Therapista!B5
=PSR1!$B$1,Therapista!B5=PSR1!$B$1&PSR1!$C$1,Therapista!B5=PSR1!$B$1&PS
R1!$D$1),TherapistA!$B$1,��))))))))))))))))))))
> --------------------
>
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

0
frank.kabel (11126)
5/25/2004 6:28:27 AM
Reply:

Similar Artilces:

DATE #10
Feb 28,2008 ---> 2008-2-28 Feb 1,2008 ---> 2008-2-1 Format? "����" <cola@hz.cn> wrote in message news:OOQhTbRaKHA.5472@TK2MSFTNGP02.phx.gbl... > Feb 28,2008 ---> 2008-2-28 > Feb 1,2008 ---> 2008-2-1 > See your other post "ÎÞÃû" wrote: > Feb 28,2008 ---> 2008-2-28 > Feb 1,2008 ---> 2008-2-1 > > > . > Hi, Highlight the cell, right click in the mouse, cell format, custom format and enter yyyy-mm-dd "ÎÞÃû" wrote: > Fe...

Vlookup edited
Hello all you wonderfulhelp, Is it possible to avoid "NA" when using "vlookup" function. I need info only where it brings results. Thank you -- smile =IF(ISERROR(VLOOKUP(B1,C1:D4,2)),"",VLOOKUP(B1,C1:D4,2)) -- Gary''s Student - gsnu200851 Example of using ISNA rather than ISERROR which hides all errors. =IF(ISNA(VLOOKUP(G1,$A$1:$F$31,2,FALSE)),"",VLOOKUP(G1,$A$1:$F$31,2,FALSE)) Gord Dibben MS Excel MVP On Thu, 7 May 2009 09:26:02 -0700, israel <israel@discussions.microsoft.com> wrote: >Hello all you wonderfulhelp, > >...

Find and replace 03-04-10
I'm trying to scan a field in one of my tables and find a specific character and remove it. However, the character is a " so I'm having difficulty. The field I speak of of contains the sizes of our material so the values look like this: 1/4" 1/2" 1/3" and so on. How can I find all of the " in my feild and remove them? I don't want to replace them I just want to remove them. Thanks, Chris Savedge Create a query, and in the Criteria row under the problem field, enter: Like "*[""]*" -- Allen Browne - ...

Formula needed 01-22-10
Hi I need a formula that will look in one column range for a certain value and for another value in another column range and when they are both found in the same row, will count the number of occurances. Help!!!! Krissy wrote: > Hi > I need a formula that will look in one column range for a certain value and > for another value in another column range and when they are both found in the > same row, will count the number of occurances. Help!!!! http://www.contextures.com/xlFunctions01.html#SumProduct Omit the "values to be summed". =SUMPRODUCT(--(A2...

SendTo PDF Not Working on GP 10
I upgraded a client from GP 8.0 to GP 10.0. Since the ugprade, they are unable to use the SendTo PDF option from within GP. We have tried reinstalling Adobe and that hasn't changed anything. The users can see the PDF being created but a blank email doesn't open from their email client as it used to. The client is on version 10.00.0991 and I thought this issue was to be resolved in 10.0 SP 1 but obviously it hasn't been for us. The client is also using Outlook. Is this still a bug with 10.0 or are we missing something? I am on Adobe Acrobat Standard 8.0 with Microsoft ...

Lost contact list 04-22-10
I amusing Windows live mail...it had no problem picking up my contacts from Outlook Express. Now that I syncronized messages from my Hotmail e-mail account and my Telus email account into Windows Live, my contact list is only the two contacts from the hotmail account. All the rest are gone. They are still in the Address Book, but I cant access them How can I get it back into Windows Live Mail ??? Help please. You signed into the Hotmail account when you started Windows Live Mail. This is optional and not at all related to getting your mail. When you sign in, WLM sync...

Outlook 2003 03-17-10
Dear Sir, 1. Process will complete mailing address to sent item means anything ? 2. Repairmail up screen time that does not match the running because of what ? 3. The move affects the computer to send mail or receive ? Best Regards, On 3/16/2010 8:19 PM, supatsiri wrote: > Dear Sir, > > 1. Process will complete mailing address to sent item means anything ? > 2. Repairmail up screen time that does not match the running because of what ? > 3. The move affects the computer to send mail or receive ? > > Best Regards, What is your native language? Please post ...

vlookup problems with left
I'm using vlookup,i have a 10 digit number which i have to mach with 9 digit number (last digit is some kind of control digit) and when i use left to take only the first 9 digits =VLOOKUP( LEFT(F200;9);W198:W201;1;FALSE) i get #N/A if i manuallly delete the 10th digit i get mached data. is there a problem with using left on vlookup? =left() returns text. That will never match a real number. I'd take the integer amount after dividing by 10. =vlookup(int(f200/10);w198:w201;1;false) But if you're really only looking to see if there's a match, you could use: ...

vlookup help linking data between worksheet
hi, I have a master list of students (about 200+ )in one worksheet. On the succeeding worksheets are the attendance for seminars. We have more than 20 seminars in a year. Because of the large no of attendees per seminar, I usually type out a list of the students who came for each seminar, so there will be 20 +attendance worksheets. Not all students will come every time and there are new ones for each session. To update on master list, I will sort each sheet by surname, print it out and type in separate column (date) for each session and typed "P" for present and &quo...

disappearing e-mail 04-21-10
This has happenend several times: Vista; When I open my e-mail, some email just disappears, it's not in any folder, it's just gone. I''ve read other threads that say make sure you are are set to show all messages and I am. What next? "Trish1856" <pmf1856@cox.net> wrote in message news:C8EFF7C5-B8C5-4F52-8D9F-070299902F6C@microsoft.com... > This has happenend several times: Vista; When I open my e-mail, some > email > just disappears, it's not in any folder, it's just gone. I''ve read other > threads that say make...

Join based on next closest value (like Excel VLOOKUP)
Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query. I have the following tables: JOBS Job,Quantity A,96 B,256 C,300 D,4299 COSTS Quantity,Cost 0, $1000 100, $1200 200, $1500 300, $2000 400, $2500 500, $3000 I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and find the cost for the NEXT LOWEST quantity. (Example: Job B has a quantity of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs $1500.) The results should be as follows: JOBS.Job,COSTS.Cost A,$1000 B,$1500 C,$2000 D,$3000 This would be ...

Mail Merge 03-12-10
I have a log that has multiple lines with the same individual that I am trying to merge into one page per individual in word, I have to do a more advanced mail merge. Assuming you are using Word for the mailmerge and Excel for the source see these sites for help. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm Gord Dibben MS Excel MVP On Fri, 12 Mar 2010 08:33:01 -0800, Brooke <Brooke@discussions.microsoft.com> wrote: >I have a...

Dynamics GP 10 Reporting Question
I heard reporting in GP 10 is upgraded. Does anyone know if it will be anything like Rockton Software Dynamics Report Manager where you can send any report to like Sql reporting services or crystal? ------=_NextPart_0001_70733CB7 Content-Type: text/plain Content-Transfer-Encoding: 7bit I haven't heard anything at all that reporting in 10.0 will be changed. While I guess it's possible, I would assume for now that there won't be any big changes in general. I would expect more MS Office & SQL integration in the next version for sure, but I can't see everything rewrit...

Home based Job For Indian
Want the pleasure of life, don't worry, work 1-2hours/day and earn Rs. 10,000-15,000/month online from your home/cafe/office PC. For details write # ejob1189@rediffmail.com Please write the subject of mail as: "Job Inquiry" ...

Date and Time 05-14-10
I have cells in a worksheet that are formatted as date but still show as date and time. How do I pull out the time. E.g. 04/07/2009 1:00:00 AM I have tried the =left() and also =right () formulas with no luck. Date and time are just numbers underneath the formatting. 04/07/2009 1:00:00 AM is 39910.04167 Format the cell to General to see that number. Use Data>Text to Columns and delimit by decimal point. In third step choose the right hand column and "Skip" Finish Re-format the cell. Gord Dibben MS Excel MVP On Fri, 14 May 2010 14:30:0...

pick a number from 1 to 10
hey all, i want to create an asp.net web page that allows internet users to pick a number from 1 to 10. how do i manage users picking the same number? (if 2 users somewhere happen to be selecting the same number and the same time). Is this a concurrency issue? thanks, rodchar hi Rodchar, On 19.12.2009 19:01, rodchar wrote: > i want to create an asp.net web page that allows internet users to pick a > number from 1 to 10. You may use ten links or buttons. But what should happens after they "picked" a number? > how do i manage users picking the same nu...

Excel
Help, my formulas does not seem to calculate negative numbers. If a= 91 then result is 30 days. =if(a<-90,"-90days",if(a<-60,"-60days","30 days")) -- TTB Thanks! TTB, You need to give the formula a complete cell reference A1, or A2, not just A. =if(a1<-90,"-90days",if(a1<-60,"-60days","30 days")) Also if you want -90 to return "-90days", and -60 to return "-60days", then you will need to replace "<" with "<=" like this: =if(a<=-90,"-90days"...

Customizations 03-10-06
Hi, using the "customization" in "Settings" showed me an "E-mail" entity, althought there is no way to setup where to display it ("Areas that display this entity") and I'd like to find out how to adapt this!! Thanks for your helpfull reply, SturE Email enitity is a type od Activity, there is no way to modify display area for activities in user interface. You can try to modify sitemap.xml, but you will need some knowledge... "Sture Van de moortel" wrote: > Hi, > > using the "customization" in "Settings" ...

Query help please 04-08-10
I have two combo boxes. Location and Super1 The location box lets you select a location where a staff member is working at.For example U3 B-days. The Super1 box lets you select their supervisor who works U3 B-days. As it stands right now it will work. The problem is that it will show all Supervisors through out the whole place. I found a way where I can select from the Location box where it will pull up just the supervisors for that location in the Super1 box. That works so far so good. Now, where the problem comes in is when the staff member is a supervisor. For example: Staf...

Linking cells 01-22-10
My excel skills are lackluster at best, but I am trying to link specific cells in 2 different spreadsheets and could use some help. What I would like to do is automatically update a column in one file when I enter info in another column in the other file. Basically I need the date to update automatically to show the last pick up date for specific suppliers.... for example, if we pick up from XYZ company on Jan 1, I need that date linked to that client, and then we pick up again from XYZ company on Jan 21, I need the date updated to Jan 21 on the second file. Hope this m...

xldialogprint arguments 01-25-10
Are there any arguments that I can pass in the .show method. I am specifically looking for a way to hide the print preview button. Thank you, Tom I don't believe you can hide the print preview button. You can though preset most of the options by passing the relevant arguments. range_num, from, to, copies, draft, preview, print_what, color, feed, quality, y_resolution, selection, printer_text, print_to_file, collate Regards, Peter T "TWR" <TWR@discussions.microsoft.com> wrote in message news:44C13F42-80D5-49E3-918F-B0F8C9422F07@microsoft.com... >...

VLookup?????
=IF(ISNA(VLOOKUP(B2,'TableB'!$Y$2:$Y$100,1,FALSE)), "",Column A in tableB) This is the code i have. I want to search tru all column B in table A. If i find an identical value in table B i want the field in table A to show the corresponding field in Column A in table B. Also if there is 2 occurences of column B in table B, i want the field in table A to show both values. Any ideas? (if u can understand what im asking) -- Hazy ------------------------------------------------------------------------ Hazy's Profile: http://www.excelforum.com/member.php?action=g...

encryption 03-16-10
Hi, I remember there're some products for encrypting the VBA codes (Excel macros). Has anyone ever used that? -------------------------------------------------------------------------------- Many Thanks & Best Regards, HuaMin Chen ...

Rounding Problem 05-14-10
I have been reading through previous posts, and nothing I have tried is working. I have changed the field i want to round to 3 decimal places in the BE table. It is now Double, Standerd, 3 places. When i open up the form, its the same, but its still rounding it. Not sure why or what to do. Everything I have tried fails. PLEASE HELP!! THANKS!! Nevermind, I figure it out, ha...It seems like I try everything and can't get it, but as soon as I post that I need help, I figure it out, hahaha. "tsquared1518" wrote: > I have been reading through previous posts, ...

If(vlookup statement
I have three columns that I need to return a vlookup value. A B C 8 2 PSS I want to lookup column A in my table to return the name, if not then lookup B and return the name, if not then lookup C, otherwise give me "Part 1 - Other". In this case none of A, B, or C is found so my result should be "Part 1 - Other". My lookup table is below: CLASSIFIERID GENERICATTRIBUTE1 GENERICATTRIBUTE2 ANH Part_4 Athena Part_4 Athena 21 Part_4 Specialty Part_4 Specialty 24 Part_4 Specialty Part_4 Specialty CR Part_4 Specialty Part_4 Specialty ...