If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

The following formula queries and totals information on a specified sheet. In this case the specified sheet name is 2003 I have several sheets (2001, 2002, 2003, 2004.....) I would like to have the value for the sheet name in the formula reference a cell in which I could type in the name of the sheet I want to query. Can someone tell me how to accomplish this. Just changing the '2003' in the formula to the reference cell doesn't work. (FYI - the reference cell will be L1) =SUMIF(('2003'!H2:I400),+(H3),('2003'!D2:I400)) TIA Mike =SUMIF((INDIRECT("'...

While trying to import an excel spreadsheet to a SQL Server table, DTS fails, pointing to a column with date values. I have looked at all the date values, and they appear correct. I need to get this excel spreadsheet loaded to a database table. Is there any way to pinpoint the exact cell causing the problem? Or, can you think of another way to export the data in the spreadsheet besides DTS that might not be so sensitive to data content? Thanks, Dean Slindee ...

Any help appreciated! I need to work out the average speed it would take to cover a certain distance. EG. If you covered 10 miles in 28 minutes what would teh average speed be... Column A: 10 miles Column B: 28 minutes Column C: Would return 21.43mph. I then want to to add time increments of say 15 seconds in rows so that I can see that if it took 28 minutes, 45 seconds, the average speed column would return 20.87mph. I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s), but I want to format it as time, i.e: 00:28:45 (or 01:10:30) Is this possible? Thanks...

How do I copy worksheet formatting, inc. custom headers, footers, column & row formatting across all the worksheets in a workbook? Hi one way: formating them all at the same time: - group the sheets (hold down the SHIFT key while selecting the sheets) - apply the format to a cell, column, row -- Regards Frank Kabel Frankfurt, Germany driverdriver wrote: > How do I copy worksheet formatting, inc. custom headers, > footers, column & row formatting across all the worksheets > in a workbook? You can also select the entire sheet with CTRL+A and do Edit / Copy, then group yo...

We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the me...

How do I combine text and date formula to show the written date and not the Excel number date? I have text in a couple of cells and a date formula in another cell(input from a different worksheet). I have the formula =A3 & F3 & A4 in A1 and would like it to read: We went to the station on December 21, 2009 and took the train. However, it is reading We went to the station on 40168 and took the train. Thanks. Try something like this... A1 = Today is B1 = 12/21/2009 =A1&" "&TEXT(B1,"mmmm dd, yyyy") Returns: Today is December 21, 2009 ...

I'd like to have 4 conditions, Red, Yellow, and Green. But I have two conditions for green, without using two conditions for green is there a way to use OR somehow to beat the limit? Thanks, Norm PS Win2000 + XL2002 Sure ... just click on "Formula Is", and you can use "OR" for more then two conditions. For example: =OR(A1=2,A1=4,A1="good",A1="bad") Will trigger the set format if "any" of the above equate to "True". -- HTH, RD --------------------------------------------------------------------------- Please keep all corr...

Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

Does anyone know why I am unable to enter a number into Excel's column width box, yet using the mouse, I am able to adjust the width. When opening the text box, I can delete the number that appears, but am unable to even reenter that one after it is deleted. I must tell you that I'm a new user who's working through numerous tutorials. Thanks, Hi do you get an error message or what happens exactly -- Regards Frank Kabel Frankfurt, Germany rly2rys wrote: > Does anyone know why I am unable to enter a number into Excel's > column width box, yet using the mouse, I am ...

IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,">="&DATE(1900,1,1))-COUNTIF(E:E,">="&DATE(2010,2,5))+COUNTIF(E:E,DATE(2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? =SUMPRODUCT(--(E1:E100<=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: > IM trying to create a formula that gives me...

Hi I have a whole spreadsheet full of fields similiar to the following 10:00AM 12:15PM 2:25PM 4:40PM 7:00PM 9:20PM 10:00PM 12:20PM 2:25PM 4:30PM 7:15PM 10:00AM 12:30PM 2:45PM 9:15PM 10:40AM 1:10PM 5:00PM 7:20PM 9:35PM etc. I need to convert all times to 24 hour, and drop the AM & PM. The later is easy, but how do I do a quick Search & Replace without incorrectly converting 10:**AM to 22:** etc. Any tips would be appreciated. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly fro...

In my earlier version of Excel, (Office 97), I could format the cells to automatically enter amounts as dollars and cents. For example, I would type 30. and the amount was shown as $30.00. Or, I could type 4357, and the amount was shown as $43.57. In the Office 2003 version if I type 30., the amount is reflected in the cell as 30 cents instead of $30.00. Yes that is a change that other people have complained about when one uses fixed decimals -- Regards, Peo Sjoblom (No private emails please) "hat1" <hat1@discussions.microsoft.com> wrote in message news:2C3CBB7C...

I have a data table shown under a diagram. However it won't display all the decimals. I.e. in the input to the diagram for january 2006 is 1.123, but only 1.1 is shown in the data table. The only solution seems to be to enlarge the diagram but this is not an option in this particular case! As a last resort I could embed a table under a traditional diagram without the data table, but that is second best. Looking forward to any help If you right click on the data table you can change the font size by clicking on 'format data table' and the font tab. You can type in the size ...

I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

Publisher doesn't support this. You would need to do it manually. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "Bob T" <Bob T@discussions.microsoft.com> wrote in message news:68671977-F71B-444F-AE72-801D2B359FEE@microsoft.com... > ...

Is it possible to shade fields progressively darker or lighter depending on the values. For example, I would like the following values to have the following colours: -5 dark red -4 lighter red -3 slightly lighter red -2 etc, etc 1 light green 5 dark green One step further - would it be possible to tell excel to work out which is the lowest and highest number in the data set and work out for itself how light or dark to shade the values e.g, if my dataset happened to have the values -50, -40, etc excel would work out which was a low value and which was a high one. Conditional formatting ...

I would like to apply a Conditional Format based on two factors. Currently I have two different Conditions set up. Condition 1: =IF(IF($G2="Ready to Publish",1,0)=1,1,0) This format successfully works to gray out and cross out text that matches "Ready to Publish." Condition 2: =MOD(ROW()-1,2)=0 This format applies alternating row fills for easier viewing. While both of these formulas work, my problem is Condition 1 overrides Condition 2 when "Ready to Publish" is detected. Is there a way to combine the two formulas so they wouldn't overr...

Pub2002/XP - how to format numbers in a table - I want it to automatically space for thousands etc and align the numbers correctly ie. 1 650 243 500 etc You will have to do it manually, or copy and paste it from a spreadsheet. -- See if Courier font will do it. Most other fonts do proportional spacing i.e. the letter m is wider than an l. In Courier (maybe teletype, typewriter and a few other fonts) use the same width of space and thus will line up columns from row to row. -- Don Vancouver, USA, a great city in one of the 45+ countries in America! "simonh" <si...

Trying to get this to work, but no success: =LOOKUP(A6,{DATEVALUE("3/26/2010"),DATEVALUE("6/24/2010"),DATEVALUE("9/22/2010")},{"3","2","1"}) The value in A6 is a function; not hard-coded. It seems to work with one date and one result vector, but not two, and ultimately I will need several, maybe 7 or 8. Any ideas? Thanks! Ryan-- You can't use functions in array constants. >ultimately I will need several, maybe 7 or 8. Why don't you just create a table in ascending order by date: .............A.......

I am using WEEKNUM in Excel to do some COUNTIF functions and have problems with matches because Excel returns a single digit rather than 2 of weeks 1 throuhg 9 of any given year. Using teh COUNTIF function 20051 then mathmatically is the same as 200510. Is there some way to format the week returned to be 2 digits? -- RonB Hi Something like this: =TEXT(WEEKNUM(A1),"00") Arvi Laanemets "RonB" <ronb@discussions.microsoft.com> wrote in message news:AB373DCB-47DC-48C6-AB3C-9D9B284E4A73@microsoft.com... > I am using WEEKNUM in Excel to do some COUNTIF func...

I wish to create a conditional format formula that will change the background colour in a cell to Blue, if the value of another cell is between to values (which are also detailed) Cell I want to change is D8 on Sheet1 The dependent cell of D8 is in A8 on Sheet1 The two values which A8 must be between/or equal to is in F7 & H7 on Sheet2 I just can't get my head around how to construct this Thanks Select cell D8. Format/ Conditional Formatting/ Formula Is/ =AND(A8>=Sheet2!F7,A8<=Sheet2!H7) if F7 is less than or equal to H7 or Format/ Conditional Formatting/ For...

Currently we can add several analysis code in FRx Row format by using several rows (each row will contain only 1 analysis code - limitation of FRx with MDA). The client has many analysis codes in each analysis group. What they need is to add some of the analysis code in a single row; separating it into several rows will add more work in designing the FRx report. There should be a way for next releases of FRx to handle adding mulitple analysis codes in a single row or column of the report. ...

I tried running this without success. Can anybody tell me why? Sub Fix192Hyperlinks() ' Dim OldStr As String, NewStr As String OldStr = "c:\My Templates\Profile Database\" NewStr = "\\Pinoak\Data\Mfr\Grinding Room\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(hyp.Address, OldStr, NewStr) Next hyp End Sub Thank you > > >> > "Ron de Bruin" wrote: > > > >> I copy this from David McRitchie his site > >> http://www.mvps.org/dmcritchie/excel/buildtoc...

Hi: Having strange problem with erratic behavior of field format on a report/subreport, which I hope someone can solve. (Using Ac2003) On the subreport Detail, I have a field "Charges", Where Format= Fixed, Decimal Places = 2, and ControlSource = =Replace([ChargesApproved],"."," "). On the subreport Footer, I have a field "SumCharges", whose Format=Currency, Decimal places = 2, and ControlSource==DSum("[ChargesApproved]","TopN") (where TopN is the underlying query for the subreport). Then, in the Report Detail section, there is a fie...