Hi there, Can anyone help! I am trying to create the following formula : A1 = 01.08.02 IF (A1="01.08.02","N","S") As A1 does = 01.08.02 it should have "N" in the cell which I am usin the formula in shouldn't it ? or is there something I'm missing. The dates are exactly the same format in the formula and in the A cell. Any help would be very much appreciated!! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Availab...

Hi all, i have an templet on an excel sheet having complex calculation. i am not able to view the formulas, as the sheet has been proteceted. is there any way i will be able to view the formulas. Thanks and best regards, subbu. Google for a password protection remover. There are many of them - most are free. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Subbu" <subbu3580@gmail.com> wrote in message news:1164782719.812301.212180@l39g2000cwd.googlegroups.com... > Hi all, > i have an templet on an excel sheet having complex calcu...

Please look into the formula & suggest. The excel sheet is fille-in as below: Column A B C D Row 1 2 6 5 Row 2 20 30 90 D2=A$1*A2+B$1*B2+C$1*C2 Row 3 2 4 8 D3=A$1*A3+B$1*B3+C$1*C3 & so on for further rows. What I need is, if a column is added between, say, B & C, then th formula in D column should update for the added column & similarly fo deleted column. Is there a way? I would prefer excel formula solution. If code is the only solution please write the complete code. The undo button should not beco...

Hi Have two columns of data, for example: Dept Personnel 1 60 2 78 3 35 3 45 2 23 3 24 4 67 2 23 1 23 2 34 3 34 I have already placed the formula =count if(A1:a1000, "1") and so on at the base of column A to count occurences of departments. At the base of column B I wish to have a calculation that totals the amounts of column b (personnel) that correspond with each dept (i.e. something to the extent of if looking at number of personnel corresponding to department 3, the result of the formula should be 138! Any help appreciated! Hi One way is to...

how do I use a % range for instance if f13 is 25-75% in a formula =IF(AND(F13>=25%,F13<=75%),"yes","no") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "las" <las@discussions.microsoft.com> wrote in message news:F912E1EA-9AC9-40A9-9DF5-1F18D93C4694@microsoft.com... > how do I use a % range for instance if f13 is 25-75% in a formula Bob I have been working on this for hours...thank you for you assistance it worked! las "Bob Phillips" wrote: > =IF(AND(F13>=25%,F13<=75%),"yes","...

Hello, i know that the funtion mid works in Excel. i need to use this function in Ms Query, (get external data, and edit my query there). is there any similar function Thank you Suhair Suhair, Try substr(string,startpostion,length) eg. substr(mystring, 1, 4) It's a bit of a shot in the dark but it might work. "suhair" wrote: > Hello, > i know that the funtion mid works in Excel. > i need to use this function in Ms Query, (get external > data, and edit my query there). > is there any similar function > Thank you > Suhair > ...

I have a formula in my spreadsheet that looks like: =IF(D11=0," ",IF(C11<D11,"Not Enough Hours",INDEX('PF Kintanas Pivot Tables'!$A$403:$A$418,MATCH($D11,t(E1),-1)))) In cell E1 I have the following text: gtalbo In the PF Kintanas Pivot Tables tab I have a named range with the name: gtalbo When I try to use the above formula I get the #Value! error. If I substitute the actual name of the range in the formula it works: =IF(D11=0," ",IF(C11<D11,"Not Enough Hours",INDEX('PF Kintanas Pivot Tables'!$A$403:$A$418,MATCH...

Cell A1 contains 1,274 characters. My function is: =mid(a1,xxx,12) Is there a way to determine the position of the first character in cell A1, besides physically counting over 'xxx' characters? Don't really understand your question. The position of the first character in A1 is "1". =Left(A1,12) - BUT - I'm sure that's not what you're looking for. Is it ? Do you want to put the value xxx in a cell B1 and use that ? =MID(A1,B1,12) You want to return 12 characters starting at position xxx. You have to have something to identify or mark that particular sta...

What is the file name where Outlook stores the contacts? And where is it stored? All Outlook data is stored in the same file. The file you need to back up is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc are stored. Take a look at these pages for info on Outlook data backup: http://www.slipstick.com/config/backup.htm -- Russ Valentine [MVP-Outlook] "dave" <anonymous@discussions.microsoft.com> wrote in message news:0d2101c3b2ab$9182b540$a401280a@phx.gbl... > What is the file name where Outlook stores the contacts? > And wher...

I need to do some type of lookup and sum in vba I'd like to use Total Sheet.B5 to return the value Total Sheet.B5 to = 55 if Total Sheet.A5 = Orange Total Sheet A5 = Orange B5 = 55 Sheet 1 A4 = Blue b4 = 5 A5 = Green b5 = 10 A6 = Orange b6 = 15 Sheet 2 A4 = Orange b4 = 10 A5 = Green b5 = 20 A6 = Blue b6 = 30 Sheet 4 A4 = Green b4 = 20 A5 = Orange b5 = 30 A6 = Blue b6 = 40 Thank you for any help Hummm, I can't really tell what you're doing, but look here: http://www.xldynamic.co...

Good day, I have the following data sample: Dates Risk Profits 16-Feb-04 Certain 0.0060 16-Feb-04 Uncertain 0.0060 16-Feb-04 Certain -0.0030 19-Feb-04 Certain 0.0060 20-Mar-04 Certain 0.0060 21-Mar-04 Certain -0.0040 22-Mar-04 Certain 0.0060 22-Mar-04 Certain 0.0060 22-Mar-04 Certain 0.0060 25-Mar-04 Certain -0.0040 08-Apr-04 Certain 0.0060 09-Apr-04 Certain -0.0040 1- I would like to be able to get the "Profits" average per month. So how can I SUM the values from the 3rd column for an entire month? 2- Also, per month,...

Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have recently saved a workbook as a new file, under a new name. Every time I save the file I get the following message: <br><br>"A formula in a cell (Name: Telecom) could not be converted because it contains a function that is not available in the file format to which you are saving." <br><br>When I first saved the file there were several named cells identified that I was able to find and delete, and this last one (Telecom) has been deleted using the name define list but t...

To All I am using Excel and OLE to generate a maintenance checklist spreadsheet , the spreadsheet is then downloaded to a palm device for field technicians to complete - only a date is required to be entered , what I would like is when the date cell is entered the current date is entered automatically ( saving the tech on entering a lot of dates on the small palm screen ) Something like this oSheet:Cells(nRow,"H"):Formula := "=FillDate()" Can someone assist me with an Excel formula to do this and how I associate it with a template sheet. Thanks for any help Colin ...

Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Hi Bobby One way Data>Filter>Autofilte...

How do I set up a formula that will deduct from a beginning balance and keep an ending balance as I add items to be deducted? One way is to use the SUM() function for your total (ending balance) and enter the amounts to be deducted as negative values by using a minus sign or enclosing them in parentheses. You could also build a formula such as =A1-A2-A3-A4, etc. Other ways are also possible. Hope this helps |:>) "Theresa" wrote: > How do I set up a formula that will deduct from a beginning balance and keep > an ending balance as I add items to be deducted? > ...

I have 4 series on a graph and I would like to be able to change which series is displayed (i.e. 1 of 4, or 2 of 4, 3 of 4, or all 4). I would like to do it by drop down menu if possible. Is this possible? Send me a sample file and I will show how. Remove TRUENORTH -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jamie" <Jamie@discussions.microsoft.com> wrote in message news:04DDBC1C-0558-4A89-83B7-517F7F6F404C@microsoft.com... >I have 4 series on a graph and I would like to be able to change which >series > is displayed (i.e. 1 of 4, or ...

Hello, I have a list of unique values in Column A in sheet 1. In sheet2, I am have data laid out in a table with row headers and column headers. I am trying to come up with a formula that will display the value that intersects in sheet 2, based on the combined column header and row header. So for example. Sheet 2 Column Headers Row Headers State City Weight Jeff TN Nashville 200 Tim FL Miami 155 Eric GA Atl 225 Sheet 1...

Hi guys, I'm having some issues with Mid, I was trying to remove 22 from cases that start like this 22###-* , I have query that is already doing this but I would like to remove it as the user enters case number and not after. These cases usually look like this 22060-01531-01 , or 22060-01531a, or 22060-01531 or 22060-01531a-04 but they also could be like this 060-2350a or 060-2350 or 0622-CC00215 or 0622-AC00218-03 What I do now is strip suffix, like -01, -02 etc. on the end using this: Dim strlen As Integer strlen = Len(CaseNo) If Mid(CaseNo, (strlen - 2), 1) = "-" Then Me...

hi, i have cells that contain diff. formula and the first time i enter any one of them then leave it (eg. use arrow key to scroll across columns in a given row) the worksheet change event is fired. is this a bug because nothing has changed in the cell (perhaps some recalculate event has fired?). the reason the above noted behaviour is a problem is because in my worksheet_change event i execute an Application.Undo and this line of code throws an error because nothing there is nothing to undo because nothing has actually changed. i don't know how to test for whether there...

I have a workbook in which there are formulas that pull through from other workbooks. I want to make the workbook so that it doesnt ask me to update the formulas everytime and make it so that only the values show up(formulas are no longer needed once computed) Is there a way to do that besides copying and pasting as special? Not 100% sure, but I think you can do this by going edit/links/break links. >-----Original Message----- >I have a workbook in which there are formulas that pull through from other >workbooks. I want to make the workbook so that it doesnt ask me to updat...

I am trying to use MID to extract teh first 2 numbers of a 4 digit number that is in a cell. The problem is, the number sometimes has a "0" as the first digit and when this happens, MID() ignores the 0, and reads the 2nd and 3rd character. So: Cell A1 = 0123 Cell B2 =MID(A1,1,2) Returns 12 instead of 1 How can I fix this? I need tehm to stay numbers so I can use them in mathmatical operations in another formula. I thought I had teh problem solved by formatting the cell to "Number ->custom 0000" TIA Adam One way =TEXT(MID(A1,1,LEN(A1)-2),"00") ...

Hi All Im having a problem, I have the following formula: =IF(D5="Standard Soft Start & optimiser","25",IF(D5="mechanica press","15",IF(D5="injection moulding 1","15",IF(D5="injection mouldin 2","15",IF(D5="conveyor_on","15",IF(D5="conveyor_off","20",IF(D5="scre compressor","10",IF(D5="reciprocating compressor","5","Error")))))))) All works fine, BUT it wont let me put any more IF commands in there I need to do another...

I am trying to build a index for lack of a better name. I would like to build a list several of the itmes "cells" at the beginning of a spreadsheet . When these items "cells" are double clicked, it would take you to the corresponding cell on the spreadsheet. The index cell would have the exact same information as the cell it would be looking for. I am continually adding rows to this spreadsheet so the row # would change as added. This may not be possible, but I have seen you guys do amazing things with excel on here. Thanks and keep up the great work!!!!!!!! John ...

Can someone help me shorten this formula? I'm not sure if the mathamatics can be shortened by altering the formula or using a different formula to figure out the problem, but the path name makes it exceed the max amount of charachters. Changing the linking files path location is not an option. I know I can achieve it by putting different formulas in a couple of seperate cell's but I'm trying to get the final result by only using one cell, I know the path name can be shortened by using '[pn] instead, but the name of the spreadsheet changes every month, so I can't easily find...

Can I do 'n Vlookup and then Hlookup in one formula? A B C 1 North South 2 Oil 1.00 2.00 3 Gas 3.00 4.00 4 5 6 7 Gas 8 North 'Answer in Cell A9 below: 9 3.00 <<< Contains formula =INDEX(B2:C3,MATCH(A7,A2:A3,0),MATCH(A8,B1:C1,0)) "Esrei" <anonymous@discussions.microsoft.com> wrote in message news:16a101c536bf$1d735bc0$a601280a@phx.gbl... > Can I do 'n Vlookup and then Hlookup in one formula? ...