 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2007flow chart vlookup if statements
I am trying to convert a flow chart into series of questions. Right now I have a flow chart that asks a yes/no question. I would like to set up something where it asks the first question and then if they answer yes, it gives them the next question if no, then it asks a different questions. I can do this for one question just using an IF statement but once I get to the second question I am stuck since the second question will be different depending on if the answer to the first was yes or no. Any suggestions? What does this have to do with VLOOKUP? Dave  SignatureA hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. > I am trying to convert a flow chart into series of questions. Right now I > have a flow chart that asks a yes/no question. I would like to set up [quoted text clipped - 4 lines] > depending on if the answer to the first was yes or no. > Any suggestions? I thought that I might be able to list all of the questions in a table and use VLOOKUP to pull the correct question. Not a good idea? > What does this have to do with VLOOKUP? > [quoted text clipped - 8 lines] > > depending on if the answer to the first was yes or no. > > Any suggestions? Actually a VLOOKUP (or INDEX/MATCH) might be just the idea but a lot depends on what you store where. The flow chart you are describing seems like a tree, where the last question is the parent and correct/ incorrect are two paths stemming from the parent into the two children questions. WHen storing graphs as tables (and trees in this case) the following structure can work: QuestionID ParentID Path Question 1 null null What is my name? 2 1 corr What is my last name? 3 1 incorr What is John's name? The first question is the root hence has no parent or path. Questions 2 and 3 are the questions after 1 (whence their parentID) and you go there for a correct or incorrect answer respectively. Thus, assuming these data start from A2 (row 1 are headers), and if the last question asked is in F2 and the answer (corr/incorr) is in G2, then you can retrieve the next question with an *array* formula like: =INDEX(D2:D20,MATCH(1,(B2:B20=F2)*(C2:C20=G2),0)) (since it is an array formula you have to commit with Shift+Ctrl +Enter) This is by no means a full solution to your problem but it might be a start. HTH Kostis Vezerides > I thought that I might be able to list all of the questions in a table and > use VLOOKUP to pull the correct question. Not a good idea? [quoted text clipped - 14 lines] > > > depending on if the answer to the first was yes or no. > > > Any suggestions? Don't you think your INDEX/MATCH suggestion is more appropriate than VLOOKUP? Seems it offers more flexibility. Dave SignatureA hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. > Actually a VLOOKUP (or INDEX/MATCH) might be just the idea but a lot > depends on what you store where. The flow chart you are describing [quoted text clipped - 44 lines] > > > > depending on if the answer to the first was yes or no. > > > > Any suggestions? Hi Dave, Yes, of course INDEX/MATCH is always more versatile... In this particular case it seems to be necessary. Hard to go far with VLOOKUP if you handle data like this. Kostis > Don't you think your INDEX/MATCH suggestion is more appropriate than VLOOKUP? > Seems it offers more flexibility. [quoted text clipped - 52 lines] > > > > > depending on if the answer to the first was yes or no. > > > > > Any suggestions? Sorry, I do not understand how the columns are set up in your example. It looks like you only have four columns so wouldn't the questions be asked in column D? > Hi Dave, > [quoted text clipped - 60 lines] > > > > > > depending on if the answer to the first was yes or no. > > > > > > Any suggestions? The entire flowchart is stored in columns A:D. Questions' text is in column D:D indeed. This is the underlying structure needed to store a tree-like graph. But we assume that the questions appear elsewhere, in other cells. To build the whole thing might require considerable effort and depends how you want to set it up. In fact my suggested formula is half-complete, it only shows the philosophy. A semi- automatic scheme: In another area we have: F2 contains 1 (QuestionID for the first question) G2 contains: =VLOOKUP(F2,A:D,4,0) -- the question text H2 contains the answer supplied by the user. I2 contains your grade (corr/incorr). Now, in Row 3, we want to produce the next question, according to the answer grade of the last question. In F3 (array formula): =INDEX($A$2:$A$100,MATCH(1,($B$2:$B$100=F2)*($C$2:$C$100=I2),0)) G3 is the copy down of G2. This will produce the new question H3 is supplied by the user I3 contains new grade As I said, this is semiautomatic, it can take considerable work until it is presentable, but this is one way along which it can be done. I have to go, so maybe someone else will jump in later if necessary. Does this help? > Sorry, I do not understand how the columns are set up in your example. It > looks like you only have four columns so wouldn't the questions be asked in [quoted text clipped - 64 lines] > > > > > > > depending on if the answer to the first was yes or no. > > > > > > > Any suggestions? Thanks for your help. I will work on it. > The entire flowchart is stored in columns A:D. Questions' text is in > column D:D indeed. This is the underlying structure needed to store a [quoted text clipped - 93 lines] > > > > > > > > depending on if the answer to the first was yes or no. > > > > > > > > Any suggestions? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.