How do you use the Contains Function in Visual Basic?

If function

• QUESTION: I have afile which has many different sheets divided by countries. What I want to do is search within the file, that is create the Sheet1 to be a "search engine" user friendly. What I had in mind was to do as following: A1 B1 C1 Type country name in box: [] =IF(B1="Austria";" AUSTRIA!";"This country is not available yet") But what is missing is the retrieve or go to function? Any thoughts on how to solve this :) mb. Alfred ANSWER: You can use th INDIRECT function to extract information from a specific worksheet who's name is in a specific cell. Suppose cell A1 contains the worksheet name, then this formula gives the value of cell A1 on that sheet: =INDIRECT("'" & A1 & "'!A1") ---------- FOLLOW-UP ---------- QUESTION: I named the Sheet1='Search' After that comes, Sheet2='Austria', Sheet3='Bosnia' etc. A1 in Sheet2, Sheet3 contains the name of the country; Austria, Bosnia etc. I?m using2007 - is there a different solution in that case? mb. Alfred ANSWER: My solution works when the name of the sheet is the same as the name of the country, or when you put the name of the sheet into the cell A1 that is used in the INDIRECT function. So if you want info from a sheet called Sheet1, enter Sheet1 into cell A1 of the search sheet and then use my function as posted earlier. ---------- FOLLOW-UP ---------- QUESTION: Ok, thanks I understand what I was doing wrong now. Is there a way I can bring the whole document from Sheet2=Austria? So instead of displaying only the country name, it will show everything from A1:G75 in that sheet. mb. Alfred I'm trying to get the cell J2 to return "1" if D2 equals 3/1/2010 or 3/2/2010, "2" if D2 equals 3/3/2010 or 3/4/2010, etc. up to "7" if D2 equals 3/13/2010 or 3/14/2010. I'm using the equation below. It is only returning up to the number "4" (which is when the date is equal to either 3/7/2010 or 3/8/2010). When the date is 3/9/2010, the equation yields "0", and when the date is anywhere from 3/10/2010 to 3/13/2010 the equation yields "1". Any ideas what the problem is? =IF((AND(D2>= "3/1/2010",D2 = "3/3/2010",D2 = "3/5/2010",D2 = "3/7/2010",D2 = "3/9/2010",D2 = "3/11/2010",D2 = "3/13/2010",D2<="3/14/2010")),"7","0")))))))

• Answer:

  Why would you want to repeat that information? Unless you are creating some sort of summary sheet, which shows calculates results from that other sheet I fail to see the point of pulling in data from an entire sheet. You'd just as well benefit from a link to that sheet to click on (insert hyperlink). Whenever your values are continuous and you need to write it in formula by adding more conditions i always suggest you to type it in a separate cells and refer it in your formula like the below. copy and paste the Dates in T column and Values in U Column like the below:- T Column U Column Row 1 DATE VALUES Row 2 1-Mar-10 1 Row 3 2-Mar-10 1 Row 4 3-Mar-10 2 Row 5 4-Mar-10 2 Row 6 5-Mar-10 3 Row 7 6-Mar-10 3 Row 8 7-Mar-10 4 Row 9 8-Mar-10 4 Row 10 9-Mar-10 5 Row 11 10-Mar-10 5 Row 12 11-Mar-10 6 Row 13 12-Mar-10 6 Row 14 13-Mar-10 7 Row 15 14-Mar-10 7 Paste the below formula in J2 cell:- =IF(D2="","",IF(ISNA(VLOOKUP(D2,T:U,2,FALSE)),"",VLOOKUP(D2,T:U,2,FALSE))) If you construct the data in a separate cells like the below then it will be very easy for you to change the value instead of going through the formula.