How I can removed the Calendar that accidentally appeared in my worksheet?

About excel...

• This is to request you to please guide me how i can use if function in the following cells to get accurate value. A=Single Then B1 should contain value from C1=3000 and if A1=2(6w)or 3(6w)or 4(6w) then B1 should contain value from D1=what ever in D1 e.g A=2(6w)B1=D1 and if A=single then B1=C1 Please tell me how i can use if function in this regards. waiting for your reply and QUESTION: First of all i am really thankful to you for answer of my last question and how can i rate you for my previous answer, it really works in my sheet may Allah increase your knowledge and secondly i want to ask that we have two values e.g one in A1 and second from B1, can we take these to values in vlookup value and can we take three columns in vlookup table?please help me in this regards. ANSWER: B. You are welcome for the answer. You cannot rate me because I have removed the rating ability from my allexperts profile. Yes you can use the VLOOKUP function with more than one column, etc. But I cannot tell what you need to do so I can't give you a formula or suggestions beyond that. Give me more information and I will see if we can get you a better answer. ---------- FOLLOW-UP ---------- QUESTION: You have removed the rating ability OK sir. i am giving you brief hint of my file. Let A1=any city name and B1=vehicle type(20ft or 40ft) the freights for different cities for 20ft and 40ft are different in table array. i want to get the freight of cities in C=1 according to the vehicle size. e.g Freight of London for 20ft is 2,000/- and for 40ft is 3,500/- when A1=London B1=20ft C1 should pick freight for London according to size mentioned in B1 that is 20ft and freight is 2,000/- but if size of vehicle in B1 is 40ft then C1 automatically pick the freight of London for 40ft that is 3,500/-, and in table array A1= different cities B1=freights of 20ft then C1=same cities which are in A1 and D1= freights of 40ft containers. That is the brief description of my file. Please guide me in this regards. i will be thankful to you and thanks in advance because you have removed rating. Take Care ANSWER: B Here is the way I usually handle this type of vlookup problem. First each city and weight is unique, in otherwords london 20ft vehicle type is like no other, therefore I can make a combination of london and 20 to produce the result of london20. Do this with the & formula +A1&B1. If A1 is london and B1 is 20 then the result of A1&B1 is london20. Assume for this purpose that we create this formula in column D. Now create your table of cities, vehicle types, and prices based on the combined city/type in other words the list would look like this-- Column E= city name/type row 1= london20 row 2= london40 row 3= etc, row 4= etc, row 5= etc, Column F =freight cost row 1=2000 row 2=3500 row 3=etc, row 4=etc, row 5=etc The vlookup formula would be created in column C where you want the freight price to appear. and it would look up the value in column D. For example-- VLOOKUP(D1,E1:F100,2,FALSE) Where D1 is the combination of A1 and B1, and E1 to F100 is your table of cities.vehicle types and freight costs. The 2 in the formula says bring back the value from column 2 of the array which is hte frieght costs. The false in the formula says only find exact matches, otherwisewill find the closest match. I like to use this method because where you have a combination that is unique it greatly simplifies the process of the lookup. Remember your table can be anywhere on the worksheet or on another worksheet or workbook. Your combination of city and vehicle type can also be anywhere on the worksheet or another worksheet or workbook. ---------- FOLLOW-UP ---------- QUESTION: The idea which you have told me to mix up London city and its vehicle size in same column you are right this will works but sir i am being not allowed to change the format. In file format vehicle size in different column and city is mentioned in different column.and in table array same like that vehicle size,city name and its freight are in different columns.in table array i have six columns 20ft size column city name column freight column same like that 40ft column city column freight columns total six columns in table array. i cant change the format please guide me a formula without mixing of city and its size. Thanks in advance because you don't allow rating.thanks again i m facing a problem in excel. i want to copy the row of one sheet to another sheet on which vlookup is applied. e.g sheet 1= a1,b1,c1,d1,e1,f1 copy to sheet 2= a1,b1,c1,d1,e1,f1 and i applied vlookup on E1 and F1. i.e e1=VLOOKUP(G6125,'current freights '!B10:C217,2,FALSE) and table array of vlookup is from sheet3. when i copy row of sheet one from sheet two another sheet #REF! is appeared in coppied cell where vlookup is applied. how to resolve this problem. please suggest me solution i m waiting for your reply i m facing a problem in excel. i want to copy the row of one sheet to another sheet on which vlookup is applied. e.g sheet 1= a1,b1,c1,d1,e1,f1 copy to sheet 2= a1,b1,c1,d1,e1,f1 and i appliet vlookup on E1 and F1. i.e e1=VLOOKUP(G6125,'current freights '!B10:C217,2,FALSE) and table array of vlookup is from sheet3. when i copy row of sheet one from sheet two e1=#N/A and f1=#N/A how to resolve this problem. please seuggest me solution i m waiting for your reply

• Answer:

  You can use the IF formula in combination with the logical formula OR. Something like: =IF(A1="single",C1,IF(OR(A1=2,A1=3,A1=4),D1,"No match")) I am not sure of the behavior that you want in the quoted parts, but you can change them as required. At least this can give you an idea on how to use the nested IFs and the logical formula. It will be easier to create and maintain if you can use the method that I suggest. Remember you do not need to change your array where you have your city freight and size listed, simply use it to create part of your formula on another part of the worksheet. See the attached. If it is impossible for you to do it this way then I suggest you give me the specifics of what is contained on your worksheet. names weights sizes where they are located both row and column references, etc. and I will try to suggest another method. This is what thehelp says aboout the #REF! error--- "Correct a #REF! error Occurs when a cell reference is not valid. Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears. Review the possible causes and solutions. Possible causes and solutions Deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas Change the formulas, or restore the cells on the worksheet by clicking Undo immediately after you delete or paste the cells. Using a link to a program that is not running Start the program. Linking to a Dynamic Data Exchange (DDE) topic such as "system" that is not available Make sure that you're using the correct DDE topic. Running a macro that enters a function that returns #REF! Check the function to see if an argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) refers to a cell or range of cells that is not valid. For example, if the macro enters a function that refers to a cell above the function, and the cell that contains the function is in row 1, the function will return #REF! because there are no cells above row 1." I believe that you are changing the references when you are "copying" the information it could be that you are deleting the data, etc.. Check what you are doing and make sure that the VLOOKUP array isn't changed, or the reference to the cell you are looking up isn't changed. I cannot duplicate the problem you are having, which makes it a bit difficult to resolve. Usually the =N/A indicates an error, in the case of the vlookup function the =N/A indicates that the what you are looking for in cell G6125 cannot be found in the table array of the current freights sheet. If you know it is there, make sure your array references are correct. I have also seen the vlookup formula result in an =N/A for no apparent reason, and usually in these cases I have suspected a lack of memory capacity in the computer. But these cases are very unusual.