| Article | Body |
| 11 | Thank you Sahak and Andre I never would have succeeded without your assistance Andre Croteau <milandre@bigpond.com wrote in message news:D9wO8.12373$Hj3.40490@newsfeeds.bigpond.com... Ed, You could also try this formula: TEXT(ROUNDUP(MONTH(F17)/3,0),0)&" qtr "&TEXT(YEAR(F17),0) André "Ed Wauszkiewicz" <nredwz@yahoo.com wrote in message news:aedjaq$6adlh$1@ID-78737.news.dfncis.de... I have a date in cell f17 it's format is 06/14/02 I wish to use this date in cell g5 to indicate the second quarter of the current year that is 2 qtr 02 with the spaces. Is this possible? thanks for checking this post..Ed |
| 18 | I'm useing XP Excel 02, A1 has today's date =TODAY A2 to A10 has Text dates, june 16 02 etc B2 to B10 has data 12 hrs only if booking is required so some cell maybe blank B12 has the actual hrs allowed, say 100 Problem...How do I make the Date in A1 check the text date from A2 to A10 and check if B2 to B10 has data and return the sum in B13 of the hrs used after the the date has gone past. Thanks Ross |
| 21 | Many apologies for the multiple postings, was under a "tad" pressure to get results. Normally my manners are better than that. Thanks for the help - it works! -----Original Message----- =SUMPRODUCT((APR02!D2:D407=DATEVALUE("04/1/2002"))*(APR02! AC2:AC407="I")) or =SUMPRODUCT((APR02!D2:D407="04/1/2002"*1)*(APR02! AC2:AC407="I")) In article <3D0A4BC0.C4C9B8F5@schliferclinic.com, BSCC <general@schliferclinic.com wrote: I'm trying (and trying!) to get data in one column (D) that equals 4/1/2002 and data in another column (AC) that is equal to "I". Have tried this but no good: =SUMPRODUCT(APR02!D2:D407,"04/1/2002")*(APR02! AC2:AC407,"I") Any ideas? thanks . |
| 40 | Thanks Tom, I will try that first. However I think in the near future I will build an Acess database with user friendly inputs and easy form printouts. Jeff -----Original Message----- Usually there would be a database of daily values with date and amount. Then on another sheet you can have formulas that sum up the monthly and quarterly amounts. Since you say you would refer back to the particular day, it sounds like you have a separate workbook with each days data. To do what you want would require intentional circular references, but I am not sure how you would expect the formula to know what the current quarter and month are knowing when to restart the accumulated total. Do use intentional circular references, you would have to go to the calculate tab in tools=options and check mark iteration. Change max iterations to 1. Then you can have a formula refer to itself In A1 =if(A2<0,A1+A2,0) as an example. Of couse every time the sheet is calculated, the value in A1 would be incremented by the value in A2. Jeff Mouras <mouras@bellsouth.net wrote in message news:d6fe01c21523$f2c54b50$36ef2ecf@tkmsftngxa12... Tom, I want to be able to open up my excell file, change the numbers for the day (daily cell), then print the workbook displaying: Daily, monthly and quarterly numbers. I hadn't put much thought to saving the daily numbers because I could just refer back to the particular day. Another goal of mine was to make this as simple as possible so anyone could enter the data and get the correct print out. I am open for suggestions though... Thanks, Jeff -----Original Message----- What do your formulas look like now. Are you using intentional circular references. This seems like a good way to get the wrong answer to me. Why don't you not want to store your daily numbers and then use formulas to summarize them? Regards, Tom Ogilvy Jeff Mouras <mouras@bellsouth.net wrote in message news:eb1701c21468$c5f1aee0$b1e62ecf@tkmsftngxa04... Hello, my question is: Can I build a formula that I can input data into a single cell (Daily cell) and have that data totaled up and saved in a "Monthly" and Quarterly" cell? My trouble is every time I change the daily data the Monthly and Quarterly cells do not calculate from their previous total. Thanks in advance for any help with this. Jeff Mouras . . |
| 49 | Anyone know a quick way to get the date out of a text string (in a single cell) such as "Prices last updated as of 6/17/2002. These are provided by xxx company"? A formula or vb solution would suffice. Thanks for the help !!! |
| 55 | One possible way, with your string in A1 =TRIM(SUBSTITUTE(MID(A1,FIND("/",A1)-2,10),".",""))*1 format result as mm/dd/yy this will work if there is always a space before the date and the date string itself is not less than 8 characters Regards, Peo Sjoblom Anyone know a quick way to get the date out of a text string (in a single cell) such as "Prices last updated as of 6/17/2002. These are provided by xxx company"? A formula or vb solution would suffice. Thanks for the help !!! . |
| 58 | One way (assumes a 4 digit year): =DATEVALUE(LEFT((MID(A1, FIND("/", A1) - 2, 255)), FIND("%", SUBSTITUTE(A1, "/", "%", 2)) - FIND("/", A1) + 2)) In article <#IrpunfFCHA.1260@tkmsftngp07, Dave B <david.bracknell@avmltd.com wrote: Anyone know a quick way to get the date out of a text string (in a single cell) such as "Prices last updated as of 6/17/2002. These are provided by xxx company"? A formula or vb solution would suffice. Thanks for the help !!! |
| 65 | Thanx!! This works great. "Peo Sjoblom" <terre08@mvps.org wrote in message news:f3a501c21600$5a94fdc0$19ef2ecf@tkmsftngxa01... One possible way, with your string in A1 =TRIM(SUBSTITUTE(MID(A1,FIND("/",A1)-2,10),".",""))*1 format result as mm/dd/yy this will work if there is always a space before the date and the date string itself is not less than 8 characters Regards, Peo Sjoblom Anyone know a quick way to get the date out of a text string (in a single cell) such as "Prices last updated as of 6/17/2002. These are provided by xxx company"? A formula or vb solution would suffice. Thanks for the help !!! . |
| 66 | This did not work for me - It looks like it should work but I get #Value as answer. "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:170620020721318363%jemcgimpsey@mvps.org... One way (assumes a 4 digit year): =DATEVALUE(LEFT((MID(A1, FIND("/", A1) - 2, 255)), FIND("%", SUBSTITUTE(A1, "/", "%", 2)) - FIND("/", A1) + 2)) In article <#IrpunfFCHA.1260@tkmsftngp07, Dave B <david.bracknell@avmltd.com wrote: Anyone know a quick way to get the date out of a text string (in a single cell) such as "Prices last updated as of 6/17/2002. These are provided by xxx company"? A formula or vb solution would suffice. Thanks for the help !!! |
| 96 | Is there any way to have a custom format have a hard return. I want to have the date displayed as ddd dd/mm/yy, but I don't want the column to be wide, I want the day of the week to be on one line and the date on the next line of the cell. Like this: Mon Tues 6/17/02 6/18/02 Ethan |
| 101 | I have a date field, and the users want an input mask of 00/00/00;0;_ The users would like to only input the Month and Day, and hit return, having the Year be populated automatically based on current year (or type over the YY if desired). Can you insert the YY of Now() into the input mask? or add it to the user input somehow? Thank you in advance for any help you can give. Andy |
| 108 | You don't have to insert the year because Excel automatically interprets an entry like 7/4 or 7-4 as part of the current year. So Excel would read an entry of 7/4 as 7/4/02. HTH Jason Atlanta, GA -----Original Message----- I have a date field, and the users want an input mask of 00/00/00;0;_ The users would like to only input the Month and Day, and hit return, having the Year be populated automatically based on current year (or type over the YY if desired). Can you insert the YY of Now() into the input mask? or add it to the user input somehow? Thank you in advance for any help you can give. Andy . |
| 130 | I cannot get the following code to work (which is in the Excel Help files)--I keep getting the error message "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct and try again." However, this doesn't make sense since everything else in the Sub Procedure works. I cannot get any code to return the trendline count--I always get various types of error messages. I want to use an IF statement to determine whether a trendline exists so that it can be deleted or added for different chart datasets (series collections). (Count code is almost at end of Sub Procedure) Private Sub DataSetList_Change() Dim strfile As String, intTrendline As Integer, intCount As Integer strfile = Application.ThisWorkbook.Name With Worksheets("Review").ChartObjects(3).Chart .HasTitle = True .ChartTitle.Text = DataSetList Select Case DataSetList Case "Total Authorized-RBM (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Cum" intTrendline = 1 Case "Total Authorized-RBM (Qtr)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Qtr" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Qtr" intTrendline = 0 Case "2002 RBM Budget-Actual (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_Cum" intTrendline=1 Case "2002 RBM Budget-Actual (M)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_M" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_M" intTrendline=0 End Select End With MsgBox Worksheets("Review").Charts(3).SeriesCollection (2).Trendlines.Count 'If Worksheets("Review").Charts(3).SeriesCollection(2) '.Trendlines.Count = 1 And intTrendline = 0 Then 'ActiveSheet.ChartObjects("Chart 3").Activate 'ActiveChart.SeriesCollection(2).Trendlines(1).Select 'Selection.Delete 'Else If Worksheets("Review").Charts (3).SeriesCollection(2) '.Trendlines.Count = 0 And intTrendline = 1 Then 'Worksheets("Review").ChartObjects (3).Chart.SeriesCollection(2) '.SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="Linear Trend" 'Worksheets("Review").ChartObjects(3).Activate 'End If Worksheets("Review").Range("B5").Activate End Sub |
| 133 | Does anyone know a formula (or if it can be done!) that would automatically icrease a date entered in a cell by 1 month? example: someone enters 6/17 in cell A1, and 7/17 automatically appears in cell B1. Thanks! |
| 136 | I have unusual problem with printing more than one copy of a spread sheet from excell 2000/2002. When more than one copy is printed, the first copy prints, however the second copy prints very large and the formatting is wrecked as well. Sr 1 for xp applied and sr2 for 2000 applied. firm ware on printer is up to date as well. This function works on any onther printer except the one in question a 5si nx. Further, this is not a problem in excel off97, I can print multiple copies in office 97 |
| 140 | 1. Select the cells that you want to format 2. Choose FormatCells 3. On the Number tab, choose Custom 4. In the Type box, type ddd 5. Hold the Alt key, and on the number keypad, type 0010 6. Type dd/mm/yy 7. On the Alignment tab, choose Wrap Text, click OK 8. You'll have to manually adjust the height of the cells, as Autofit doesn't seem to work with this formatting. Ethan wrote: Is there any way to have a custom format have a hard return. I want to have the date displayed as ddd dd/mm/yy, but I don't want the column to be wide, I want the day of the week to be on one line and the date on the next line of the cell. Like this: Mon Tues 6/17/02 6/18/02 Ethan -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 143 | William - You skipped a level in the hierarchy of objects. Change this line: MsgBox Worksheets("Review").Charts(3).SeriesCollection(2).Trendlines.Count to this: MsgBox Worksheets("Review").ChartObjects(3).Chart.SeriesCollection(2) .Trendlines.Count (all one line - watch the email wordwrap). - Jon _______ In article <f36f01c21633$c64fb1d0$b1e62ecf@tkmsftngxa04, bartusek_william_c@cat.com says... I cannot get the following code to work (which is in the Excel Help files)--I keep getting the error message "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct and try again." However, this doesn't make sense since everything else in the Sub Procedure works. I cannot get any code to return the trendline count--I always get various types of error messages. I want to use an IF statement to determine whether a trendline exists so that it can be deleted or added for different chart datasets (series collections). (Count code is almost at end of Sub Procedure) Private Sub DataSetList_Change() Dim strfile As String, intTrendline As Integer, intCount As Integer strfile = Application.ThisWorkbook.Name With Worksheets("Review").ChartObjects(3).Chart .HasTitle = True .ChartTitle.Text = DataSetList Select Case DataSetList Case "Total Authorized-RBM (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Cum" intTrendline = 1 Case "Total Authorized-RBM (Qtr)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Qtr" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Qtr" intTrendline = 0 Case "2002 RBM Budget-Actual (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_Cum" intTrendline=1 Case "2002 RBM Budget-Actual (M)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_M" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_M" intTrendline=0 End Select End With MsgBox Worksheets("Review").Charts(3).SeriesCollection (2).Trendlines.Count 'If Worksheets("Review").Charts(3).SeriesCollection(2) '.Trendlines.Count = 1 And intTrendline = 0 Then 'ActiveSheet.ChartObjects("Chart 3").Activate 'ActiveChart.SeriesCollection(2).Trendlines(1).Select 'Selection.Delete 'Else If Worksheets("Review").Charts (3).SeriesCollection(2) '.Trendlines.Count = 0 And intTrendline = 1 Then 'Worksheets("Review").ChartObjects (3).Chart.SeriesCollection(2) '.SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="Linear Trend" 'Worksheets("Review").ChartObjects(3).Activate 'End If Worksheets("Review").Range("B5").Activate End Sub |
| 147 | Is there an echo?? - Jon In article <3D0E5CB4.BCF0639C@msn.com, ec35720@msn.com says... How about this? You keep the date in a column (hidden??) and then use a formula that formats it and wraps it the way you like: =TEXT(A1,"ddd")&CHAR(10)&TEXT(A1,"mm/dd/yyyy") ethan wrote: After playing with it some more, I still have the same original problem. The width of the columns is determined by the format of the date as if the hard return was not there. So even though the format looks correct, if I shrink the column width down so that it is the width of just the date, I get ####. This makes the sheet too wide to fit on the page. Ethan "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D0E4989.3050501@contextures.com... 1. Select the cells that you want to format 2. Choose FormatCells 3. On the Number tab, choose Custom 4. In the Type box, type ddd 5. Hold the Alt key, and on the number keypad, type 0010 6. Type dd/mm/yy 7. On the Alignment tab, choose Wrap Text, click OK 8. You'll have to manually adjust the height of the cells, as Autofit doesn't seem to work with this formatting. Ethan wrote: Is there any way to have a custom format have a hard return. I want to have the date displayed as ddd dd/mm/yy, but I don't want the column to be wide, I want the day of the week to be on one line and the date on the next line of the cell. Like this: Mon Tues 6/17/02 6/18/02 Ethan -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Dave Peterson ec35720@msn.com |
| 152 | Mary - This partially debugged macro will do this for up to 256 points per series: Sub MoveChartDataFromSheetToFormula() Dim myCht As Chart Dim mySrs As Series Dim iSrsCt As Integer, iSrsIx As Integer Dim iPtsCt As Long, iPtsIx As Long Dim strName As String Set myCht = ActiveChart iSrsCt = myCht.SeriesCollection.Count For iSrsIx = 1 To iSrsCt Set mySrs = myCht.SeriesCollection(iSrsIx) iPtsCt = mySrs.Points.Count Select Case TypeName(ActiveChart.Parent) Case "ChartObject" strName = "Cht" & myCht.Parent.Index Case "Workbook" strName = "Cht" & myCht.Index End Select ' Feeble attempt to prevent duplicate names strName = "Sht" & ActiveSheet.Index & strName & "Srs" & iSrsIx ActiveWorkbook.Names.Add _ Name:=strName & "X", _ RefersTo:=mySrs.XValues ActiveWorkbook.Names.Add _ Name:=strName & "Y", _ RefersTo:=mySrs.Values mySrs.Values = "='" & ActiveWorkbook.Name & "'!" & strName & "Y" mySrs.XValues = "='" & ActiveWorkbook.Name & "'!" & strName & "X" Next End Sub What it does is store the X and Y values into static named formulas in the workbook, and aplies these to the chart. - Jon _______ In article <dfa601c216db$e1a91a40$a5e62ecf@tkmsftngxa07, mary_johansen@roi.com says... I have a huge amount of data and many charts. However, I need to break the link in one of the charts so it won't update every time I change the data. Nothing Works! I've tried using the little camera icon, paste special is not available...all I could do was take a screen shot - has anyone else got a solution! We're crunching a deadline here -Help! |
| 153 | Thanks guys, Problem solved! Buried at the end of the dates were times which didn't actually appear in the cells. i have removed the times and my logic and AND logic works fine now!! -----Original Message----- Slowly going mad here with AND logic. Why doesn't this work? =AND(A1=A2,B1=B2) in cell A1 I have 23/10/03 A2 23/10/03 B1 31/10/03 B2 31/10/03 So, I'm comparing two dates with each other. If they correspond the AND should return TRUE. It doesn't - it returns FALSE. Why, Why, Why??? I'm trying to read the dates off a pivot. Could they be stored in a differnt way? Thanks for help . |
| 160 | I have a set of about 500 worksheets from which I want to extract info in a summary sheet I am building. I have the names and locations of the workeets which are located all over our network and which are being constantly updated by people at other sites. I'm trying build a column of lookups that automatically changes whan I change the name of the file being looked into. I've created the first onebut because the name of the file is in brackets [*.wks], I can't get the value within the brackets to change as if I enter a cell refference to the file name string. Are there specical commands when dealing with bracketed data? What's a reasonable solution for this? Dan |
| 240 | Hi, How can I calculate the number of days between two dates taking into account workdays only (i.e. Monday-Friday). Thanks for your help. George |
| 243 | Hi George, See NETWORKDAYS Worksheet Function in HELP NETWORKDAYS(start_date,end_date,holidays) HTH, datetime.htm has date and time relate info on my site. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm "George" <gjbeaupre@archchemicals.com wrote in message How can I calculate the number of days between two dates taking into account workdays only (i.e. Monday-Friday). |
| 499 | I need help again. the macro saves a file with a given name. To the name I want to append today's date. What should be the command. Thanks a lot for the help. tony :-) |
| 502 | goto "insert" click on date&time select the format you want good luck -----Original Message----- So I am the first visitor. Can anybody tell me how to change the date format in footer. I am using WIN 98, Office 2000. Presently it is coming in dd-mm-yy format. The Regional Settings properties for date tab are as follow: dd-mmm-yy I want the date format to look like 21-Jun-02. Thanks in advance. Vittal . |
| 505 | Hi, I want to place a countdown feature in excel to remind me how many days remain from a given date, can this be done? Pat |
| 508 | If cell a1 contains the date you are counting down to, then use: =a1-today() You need to format the cell as a number Tony "Pat" <glass_patrick@hotmail.com wrote in message news:af1ud3$auq$1@newsg1.svr.pol.co.uk... Hi, I want to place a countdown feature in excel to remind me how many days remain from a given date, can this be done? Pat |
| 509 | Hi Tony, Use this with the code you are already using: dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "tony" <tony4fly@netscape.net wrote in message news:f6ba01c219ec$919bd380$37ef2ecf@TKMSFTNGXA13... I need help again. the macro saves a file with a given name. To the name I want to append today's date. What should be the command. Thanks a lot for the help. tony :-) |
| 513 | Thanks very much for your help, now for the next question. can the time also include the date and decimal parts of a second? For example 06-22-02 08:17:54.379 , Chuck -----Original Message----- Hi Chuck You can copy paste special as values the Now() function over the top of itself, or use the simple macro: Push Alt+F11 Go to InsertModule Paste in the code below Push Alt+Q and Save Push Alt+F8 Click "AddTime" then Options assign a shortcut key Sub AddTime() ActiveCell = Time End Sub -- "chuck conroy" <cconroy@jps.net wrote in message news:115e801c219b4$76737140$39ef2ecf@TKMSFTNGXA08... | I would like to have Excel input the time in this format | hh:mm:ss | I have tried two different things. | 1. The now statement =now() this works fine however the | next time I enter it the previous time gets updated to the | new time. I want each cell to display the exact time that | is is entered and then to retain this time. | 2. I have also tried control shift : this does display | a different time in each cell, however it only displays | the hour and minute, the seconds are always 00 no mater | how I format the cell. | Does anyone have any suggestions? | Thanks, | Chuck Conroy . |
| 516 | Try Sub EnterDate_Time() ActiveCell.Value = Now ActiveCell.NumberFormat = "mm-dd-yy hh:mm:ss" End Sub I don't think you can get excel to record less than a second although it is possible to enter in a cell.. -- Regards, Peo Sjoblom "chuck conroy" <cconroy@jps.net wrote in message news:1249c01c219ff$8b3e5c70$35ef2ecf@TKMSFTNGXA11... Thanks very much for your help, now for the next question. can the time also include the date and decimal parts of a second? For example 06-22-02 08:17:54.379 , Chuck -----Original Message----- Hi Chuck You can copy paste special as values the Now() function over the top of itself, or use the simple macro: Push Alt+F11 Go to InsertModule Paste in the code below Push Alt+Q and Save Push Alt+F8 Click "AddTime" then Options assign a shortcut key Sub AddTime() ActiveCell = Time End Sub -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "chuck conroy" <cconroy@jps.net wrote in message news:115e801c219b4$76737140$39ef2ecf@TKMSFTNGXA08... | I would like to have Excel input the time in this format | hh:mm:ss | I have tried two different things. | 1. The now statement =now() this works fine however the | next time I enter it the previous time gets updated to the | new time. I want each cell to display the exact time that | is is entered and then to retain this time. | 2. I have also tried control shift : this does display | a different time in each cell, however it only displays | the hour and minute, the seconds are always 00 no mater | how I format the cell. | Does anyone have any suggestions? | Thanks, | Chuck Conroy . |
| 520 | Ralph, Assume the first date cell is A2. I'll tackle the first part. Click on the first date cell, press & hold Shift, Control and the Down Arrow to highlight all of your dates. Then, click Format, Conditional Formatting. Click the down arrow on Condition 1 and change it to "Formula Is", then, enter WEEKDAY(A2,2)=6 OR WEEKDAY(A2,2)=7, click the Format button, select the Patterns tab, and click on the desired Color then click OK and then click OK. In the second case, part 1, do the same thing but use this formula: =MAXA($B$1:$B$32)=B2; and, for part 2, use the formula =A2280 HTH, --- Ron McKenzie a proficient Excel MOUS Ralph Noble wrote: In Column "A" I have named the column DATE and entered dates for the entire summer ... I need to be able to identify the weekends (Saturdays and Sundays) and make them bold and red on the fly ... is this possible with a formula or some form of macro??? Second, I have Column "B" that is the average daily water usage for the city named in Column "B1" ... I know that maximum daily water usage for this city is 280 (in millions of gallons) and I want to be able to ... 1. Scan the entire column and mark in red and bold the highest daily value that exists anywhere in Column "B" 2. Scan the entire column and mark in blue and bold any records that were set (i.e. any figure that exceeds the 280) Can someone help guide me through how I'd do this??? I'll then apply the same formulas to the other columns, which have different maximums. I'm open to suggestions on how to better format the data, too. Thanks, Ralph Noble ralph_noble@hotmail.com DATE ATLANTA BALTIMORE CHICAGO 06/01/02 206 186 254 06/02/02 223 187 245 06/03/02 231 185 264 etc |
| 521 | Ralph, Assume the first date cell is A2. I'll tackle the first part. Click on the first date cell, press & hold Shift, Control and the Down Arrow to highlight all of your dates. Then, click Format, Conditional Formatting. Click the down arrow on Condition 1 and change it to "Formula Is", then, enter WEEKDAY(A2,2)=6 OR WEEKDAY(A2,2)=7, click the Format button, select the Patterns tab, and click on the desired Color then click OK and then click OK. In the second case, part 1, do the same thing but use this formula: =MAXA($B$1:$B$32)=B2; and, for part 2, use the formula =A2280 HTH, --- Ron McKenzie a proficient Excel MOUS Ralph Noble wrote: In Column "A" I have named the column DATE and entered dates for the entire summer ... I need to be able to identify the weekends (Saturdays and Sundays) and make them bold and red on the fly ... is this possible with a formula or some form of macro??? Second, I have Column "B" that is the average daily water usage for the city named in Column "B1" ... I know that maximum daily water usage for this city is 280 (in millions of gallons) and I want to be able to ... 1. Scan the entire column and mark in red and bold the highest daily value that exists anywhere in Column "B" 2. Scan the entire column and mark in blue and bold any records that were set (i.e. any figure that exceeds the 280) Can someone help guide me through how I'd do this??? I'll then apply the same formulas to the other columns, which have different maximums. I'm open to suggestions on how to better format the data, too. Thanks, Ralph Noble ralph_noble@hotmail.com DATE ATLANTA BALTIMORE CHICAGO 06/01/02 206 186 254 06/02/02 223 187 245 06/03/02 231 185 264 etc |
| 526 | I'm looking for a formula to make a cell return the average from one cell containing a date (in this format 06/22/02) using only the day (22) divided by another cell containing a total. Is this possible? The cell containing the date will change variably as will the total. Thank you. |
| 528 | =D1/DAY(A1) where A1 holds the date and D1 the total format result as general or number or it might be returned as date -- Regards, Peo Sjoblom "James Beam" <jimbeam@onemain.com wrote in message news:yY1R8.15928$Fv1.1178764@newsread2.prod.itd.earthlink.net... I'm looking for a formula to make a cell return the average from one cell containing a date (in this format 06/22/02) using only the day (22) divided by another cell containing a total. Is this possible? The cell containing the date will change variably as will the total. Thank you. |
| 534 | Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, |
| 536 | Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, |
| 554 | Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . |
| 557 | Select all the numbers. Then EditReplace hold the Alt key and use the number keypad on the right of the keyboard to enter 0160 as the "Find what". Be sure the "Replace with" is empty. Replace All. That is a common HTML character that looks like a space. Alternatively, download Refinate and use it for free to convert to numbers by dragging and clicking (apply Refinate's Number-Values action). --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "George" <george39672002@yahoo.com wrote in message news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04... Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . |
| 577 | Could you use a Worksheet_Change event instead? If the cell reference in one of the four cells is changed, the chart is updated. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim str1 As String Dim str2 As String Select Case Target.Address Case "$H$4", "$H$5", "$I$4", "$I$5" str1 = "Sheet1!" & Range("H4") & ":" & Range("H5") str2 = "Sheet1!" & Range("I4") & ":" & Range("I5") Worksheets("Sheet1").ChartObjects(1).Chart _ .SeriesCollection(1).Formula _ = "=SERIES(," & str1 & "," & str2 & ",1)" End Select End Sub John Vera wrote: Hi again. I'm trying to automatize my graphs so they read from a cell the range in which they are supposed to get points. Say, I have X data in column B and Y data in colmun D. My spreadsheet locates the appropriate range and displays it as text in four cells (upper and lower x range, upper and lower y range), say H4 says "$B$10" H5 says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the ranges in the graph to "read" the text strings in H4, H5 as the range in X and I4,I5 as the range for Y. Help very much appreciated. Typing indirect in the graph range is invalid (Excel 2000). Thanks, John -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 578 | You could add some code that will insert the date in the footer before the sheet is printed. For example, the following code (in the ThisWorkbook module) adds the formatted date to the right footer: Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.RightFooter = _ Format(Date, "dd-mmm-yy") End Sub Vittal wrote: So I am the first visitor. Can anybody tell me how to change the date format in footer. I am using WIN 98, Office 2000. Presently it is coming in dd-mm-yy format. The Regional Settings properties for date tab are as follow: dd-mmm-yy I want the date format to look like 21-Jun-02. Thanks in advance. Vittal -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 580 | Well, this works if an extraneous nonprinting character makes Excel think there is text, not numbers, in the cells. But I see 'M's and 'B's, which are text, too. Here's how to change them into numbers that Excel understands: Select the numbers. Do a find and replace, and replace B with E9, then replace M with E6. Billions are 10^9 or 1E9, and Millions are 10^6 or 1E6. This converts the non-numbers into numbers (I did a little test just now), so they can be sorted normally. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <a36R8.17345$Fv1.1219940@newsread2.prod.itd.earthlink.net, adetaylor said... Select all the numbers. Then EditReplace hold the Alt key and use the number keypad on the right of the keyboard to enter 0160 as the "Find what". Be sure the "Replace with" is empty. Replace All. That is a common HTML character that looks like a space. Alternatively, download Refinate and use it for free to convert to numbers by dragging and clicking (apply Refinate's Number-Values action). --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "George" <george39672002@yahoo.com wrote in message news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04... Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . |
| 603 | Zillion tks, it worked ! -----Original Message----- Hi Tony, Use this with the code you are already using: dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "tony" <tony4fly@netscape.net wrote in message news:f6ba01c219ec$919bd380$37ef2ecf@TKMSFTNGXA13... I need help again. the macro saves a file with a given name. To the name I want to append today's date. What should be the command. Thanks a lot for the help. tony :-) . |
| 606 | Hi Jon & Brian, I tried the character 160 and wasn't getting any results, so I thought it was my computer and will=20 troubleshoot that over next little while. It is useful info to know of the character 160. The transfer of data into the excel sheet and the sort=20 function works well. Thanks to both of you. Much appreciated, =20 George -----Original Message----- Sorry George. Thanks Jon. I had to run earlier and didn't take enough time to understand the question properly but figured it might help to know about character 160. --Brian "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:O8Yu7dmGCHA.2404@tkmsftngp08... Well, this works if an extraneous nonprinting character makes Excel think there is text, not numbers, in the cells. But I see 'M's and 'B's, which are text, too. Here's how to change them into numbers that Excel understands: Select the numbers. Do a find and replace, and replace B with E9, then replace M with E6. Billions are 10^9 or 1E9, and Millions are 10^6 or 1E6. This converts the non-numbers into numbers (I did a little test just now), so they can be sorted normally. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <a36R8.17345$Fv1.1219940@newsread2.prod.itd.earthlink.ne t, adetaylor said... Select all the numbers. Then EditReplace hold the Alt key and use the number keypad on the right of the keyboard to enter 0160 as the "Find what". Be sure the "Replace with" is empty. Replace All. That is a common HTML character that looks like a space. Alternatively, download Refinate and use it for free to convert to numbers by dragging and clicking (apply Refinate's Number-Values action). --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "George" <george39672002@yahoo.com wrote in message news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04... Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com =CD{=9Dw=C0=1FE=05lgQ_4=FA*_<zS=0B =EC[=04 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . . |
| 608 | Hi folks, Please excuse me if this is a FAQ, but I'm just terribly busy with finishing this project for school and I'd rather not waste time in Microsoft help documentation or searching the web. All I'm wondering is why all the dates that I have formatted as Custom: yyyy-mm-dd keep reverting to Custom: yy-mm-dd? I'm not sure why this is happening and it's getting really infuriating. My system format is yyyy-mm-dd, and I don't see any options for the default format or anything like that. Any ideas? Thanks, Doug |
| 610 | Hi. thanks for your advice. Still, since I'm not an advanced Excel user, would this work when I rename my worksheet? What happens is, I'm trying to make a template that will then become various files with different data, and each file consists of 8 sheets with almost the same configuration. But each file and each sheet in each file will be named differently, will I also need to rename the sheets in the code you gave me? thanks, John -----Original Message----- Could you use a Worksheet_Change event instead? If the cell reference in one of the four cells is changed, the chart is updated. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim str1 As String Dim str2 As String Select Case Target.Address Case "$H$4", "$H$5", "$I$4", "$I$5" str1 = "Sheet1!" & Range("H4") & ":" & Range("H5") str2 = "Sheet1!" & Range("I4") & ":" & Range("I5") Worksheets("Sheet1").ChartObjects(1).Chart _ .SeriesCollection(1).Formula _ = "=SERIES(," & str1 & "," & str2 & ",1)" End Select End Sub John Vera wrote: Hi again. I'm trying to automatize my graphs so they read from a cell the range in which they are supposed to get points. Say, I have X data in column B and Y data in colmun D. My spreadsheet locates the appropriate range and displays it as text in four cells (upper and lower x range, upper and lower y range), say H4 says "$B$10" H5 says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the ranges in the graph to "read" the text strings in H4, H5 as the range in X and I4,I5 as the range for Y. Help very much appreciated. Typing indirect in the graph range is invalid (Excel 2000). Thanks, John -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 611 | Hi. thanks for your advice. Still, since I'm not an advanced Excel user, would this work when I rename my worksheet? What happens is, I'm trying to make a template that will then become various files with different data, and each file consists of 8 sheets with almost the same configuration. But each file and each sheet in each file will be named differently, will I also need to rename the sheets in the code you gave me? thanks, John -----Original Message----- Could you use a Worksheet_Change event instead? If the cell reference in one of the four cells is changed, the chart is updated. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim str1 As String Dim str2 As String Select Case Target.Address Case "$H$4", "$H$5", "$I$4", "$I$5" str1 = "Sheet1!" & Range("H4") & ":" & Range("H5") str2 = "Sheet1!" & Range("I4") & ":" & Range("I5") Worksheets("Sheet1").ChartObjects(1).Chart _ .SeriesCollection(1).Formula _ = "=SERIES(," & str1 & "," & str2 & ",1)" End Select End Sub John Vera wrote: Hi again. I'm trying to automatize my graphs so they read from a cell the range in which they are supposed to get points. Say, I have X data in column B and Y data in colmun D. My spreadsheet locates the appropriate range and displays it as text in four cells (upper and lower x range, upper and lower y range), say H4 says "$B$10" H5 says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the ranges in the graph to "read" the text strings in H4, H5 as the range in X and I4,I5 as the range for Y. Help very much appreciated. Typing indirect in the graph range is invalid (Excel 2000). Thanks, John -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 615 | Buck, XL97 will be fine to learn on. -- Regards from Virginia Beach, EarlK ------------------------------------------------------------- "buckjordan" <buckjordan@thegrid.net wrote in message news:IDsR8.26332$uH2.982@newsread1.prod.itd.earthlink.net... Peo Sjoblom & ikr commented on the worth of a textbook on Excel 2002 vs. 2000. I have 97 SR, and at a low level of learning (i.e. a beginner). Am I a little out of date, way out of date or what. Do I need a newer version? Comments from both above and anyone else appreciated. -------------------------------------------------------------------------- -- ---- Richard B. Jordan buckjordan@thegrid.net (Fresno)559-439-9550 (Cambria)805-927-3897 (Fresno Fax)559-447-0124 |
| 617 | John - INDIRECT doesn't work, but OFFSET will. If you can spare an extra cell, use this approach. I'll assume you are working in Sheet1, and that the X data is in column A. AN19 contains 37 (the column number of column AK) AN20 contains 25 (the first row to plot) AN21 contains 38 (the last row to plot) Define two named ranges (a named formula, if you will): Ctrl-F3 to open the define names dialog Name: theYdata1 Refers To: =OFFSET(Sheet1!$A$1,Sheet1!$AN$20-1,Sheet1!$AN$19-1,Sheet1!$AN$21-Sheet1!$AN$2 0+1,1) (all one line, watch for newsreader word wrap) Name: theXdata1 Refers To: =OFFSET(theYdata1,0,1-Sheet1!$AN$19) Use theXdata2, theYdata2 for subsequent series. Now select the series and edit the chart series definition formula in the formula bar. It looks like: =SERIES(,Sheet1!$A$25:$A$38,Sheet1!$AN$25:$AN$38,1) change it to =SERIES(,Sheet1!theXdata1,Sheet1!theYdata1,1) Now when your cells update the values in AN19:AN21, the chart also will update. - Jon _______ In article <fb6601c21ae1$6b41bfb0$2ae2c90a@hosting.microsoft.com, emperorj@hotmail.com says... Hello again. I f anyone can help me it's infinitely appreciated. I'm making a "template" excel file with 8 sheets that I can use to organize data. I have data in columns, for which I have cells that pick the appropriate higher and lower values. These cells display the cell adresses in which the upper and lower values are (in one case, AN20 has "$AK$25" and AN21 "$AK$38" no quotes). Now, I need to automatize this so it will create a graph with the range $AK$25 to $AK$38 (in this case) without having to pick the range myself or having to use the sheet name (since each sheet name will change for every file I do). Indirect referencing doesn't work in the chart, neither does the address command (which I could have used to create the cell adress in the range). Help!! Thanks, John |
| 619 | Thanks Debra, it worked. -----Original Message----- You can hide the zeros: 1. Choose ToolsOptions 2. On the View tab, remove the check mark from Zero Values 3. Click OK hovendick wrote: Fred, thanks for the "use 0" reply. I tried that but "0's" show up in that row/cells. I am using this for a presentation in which it needs to show nothing (like an empty cell). If I change my formula to use zeros, is there a way to also make the text color white (so it does not show) or some other way to format it to appear empty (and keep the other rows showing values)? Thanks. -----Original Message----- Use 0 rather than blanks. If row 2 is 1/1/91 8000 1/1/91 0 XIRR will calculate a result for you. Regards, Fred. "hovendick" <mitch.hovendick@elpaso.com wrote in message news:f05a01c218ab$570d2a30$37ef2ecf@TKMSFTNGXA13... i have a file with dates and values like 1/1/90 -10000 1/1/90 -10000 1/1/91 8000 1/1/92 7000 1/1/92 7000 1/1/93 9000 1/1/93 9000 Column c is if(col a = "1/1/91", "", col a) and column d is if(col a = "1/1/91", "", col b). I created columns c and d to run an xirr on all dates/numbers except 1/1/91. My problem is that xirr does not like the ""'s in row 2. If I create columns c and d by hand with original "empty" cells in row 2 xirr works. Can anybody tell me how to put "empty" cells in row 2 with my if formula? . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 630 | Deb Your question, if I can call it that, is too broad to handle. You have to resolve to work this problem like you would eat a cow. One piece at a time. Be more specific about what you have to start, what you want to finish with, and how you propose to get there. The people in these newsgroups can help you with virtually anything but we have to start with something smaller than the whole cow. HTH Otto "Deb Koplen" <deb.koplen@verizon.com wrote in message news:fc8801c21b27$cdee0d80$36ef2ecf@tkmsftngxa12... First, thanks for the responses. But I see I need to be more detailed in describing my "opportunity"! I have a tendancy to overcompensate for my natural wordiness. I prepare 17 different workbooks containing product sales results vs. plan on anywhere from 1 to 20 "sub-products". One of the things that has been added this month is a forcast for the "best view" of how the year will turn out. As usual, management (in their infinate wisdom) has said what the year-end results are to be. What is needed from the field is their estimate by month of how this "best view" will be reached. For June they will fill in July thru December. And the total for each of the sub- products must equal management's best view less actuals to date. I need a way to enforce that the input adds up to the required total -- down to the decimal. AND I don't want to give the field the opportunity of fudging management's best view. When the sheets come back, it is a very quick turn-around befor I need to publish the results back to management and already involves a number of processes. I don't want to have to add checking each spread sheet just to make sure that the field has input the correct number for each sub-product. I was thinking a pop-up would help point out any error in imput. I was looking at activate/deactivate as a way of double-checking before the workbook is returned to me. It is always best for the field to "find" their own mistakes, and fix them, rather than me having to call them up and tell them of their error and finding out how they want to correct it. Comments and suggestions, please. |
| 632 | Using either a nifty function or a simple macro, can anyone offer an answer to this problem: I have 3 columns... Col A= Location Col B= Period Col C= Sales Amount In column D I want to create a Period to date column that will only total up each unique location's associated relevant periods. When the location changes, I need it to start accumulating at that same row. It is sorted by Location, by period. For example: A B C D Loc1 Pd1 15 15 Loc1 Pd2 10 25 Loc1 Pd3 20 45 Loc2 Pd1 10 10 Loc2 Pd2 15 25 Loc2 Pd3 10 35 ... and so on. Any help would be very much appreciated! Alan |
| 705 | Dear All I am using Excel 97. Is there any way that, as in an Access database, that an Excel workbook can be opened by many users and updated? If this requires DAO/ADO programming or general Excel functions please offer some advice on the way to target the problem. Alastair MacFarlane |
| 712 | When copying and pasting ( say a table from FrontPage to Excel ) fractions like 6/11 get translated into dates. I have a large number of data tables to copy and paste and going through editing each cell with a fraction is not an option. How does one prevent Excel from making this conversion? |
| 715 | Hi John Try formatting the cells (before pasting) as Text. -- "John Ricketts" <master@westnet.com.au wrote in message news:erNSZ6CHCHA.2436@tkmsftngp11... | When copying and pasting ( say a table from FrontPage to Excel ) fractions | like 6/11 get translated into dates. I have a large number of data tables | to copy and paste and going through editing each cell with a fraction is not | an option. | | How does one prevent Excel from making this conversion? | | | | |
| 718 | Thanks for the suggestion but I had already tried that. I had also tried formatting as Fraction but that didn't help the cause either. "" <DavidH@OzGrid.com wrote in message news:gTXR8.7$1h6.12510@vicpull1.telstra.net... Hi John Try formatting the cells (before pasting) as Text. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "John Ricketts" <master@westnet.com.au wrote in message news:erNSZ6CHCHA.2436@tkmsftngp11... | When copying and pasting ( say a table from FrontPage to Excel ) fractions | like 6/11 get translated into dates. I have a large number of data tables | to copy and paste and going through editing each cell with a fraction is not | an option. | | How does one prevent Excel from making this conversion? | | | | |
| 719 | I don't know about FrontPage, but you could paste the table into Word. Then, record a macro as you: 1. Convert the table to text, using tabs 2. Replace all tabs with tab-zero (Replace:^t With:^t0) 3. Replace all paragraph marks with paragraph marks-zero (Replace:^p With:^p0) Copy the results, and paste into Excel. For subsequent tables, paste into Word, run the macro, copy and paste into Excel. John Ricketts wrote: When copying and pasting ( say a table from FrontPage to Excel ) fractions like 6/11 get translated into dates. I have a large number of data tables to copy and paste and going through editing each cell with a fraction is not an option. How does one prevent Excel from making this conversion? -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 720 | You should get the Paste special option when you right click? Maybe one of these options will help, a normal paste would undo the Text format. -- "John Ricketts" <master@westnet.com.au wrote in message news:uE0TjlDHCHA.704@tkmsftngp08... | Thanks for the suggestion but I had already tried that. | I had also tried formatting as Fraction but that didn't help the cause | either. | | | "" <DavidH@OzGrid.com wrote in message | news:gTXR8.7$1h6.12510@vicpull1.telstra.net... | Hi John | | Try formatting the cells (before pasting) as Text. | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | "John Ricketts" <master@westnet.com.au wrote in message | news:erNSZ6CHCHA.2436@tkmsftngp11... | | When copying and pasting ( say a table from FrontPage to Excel ) | fractions | | like 6/11 get translated into dates. I have a large number of data | tables | | to copy and paste and going through editing each cell with a fraction is | not | | an option. | | | | How does one prevent Excel from making this conversion? | | | | | | | | | | | | |
| 999 | Susan, Try this Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Not Intersect(Target, Range("B:B")) Is Nothing Then For Each myCell In Intersect(Target, Range("B:B")) If myCell.Value < "" Then myCell.Offset(0, 6).Value = Date End If Next myCell End If End Sub HTH, Bernie "Susan Clause" <claussu@em.agr.ca wrote in message news:1131f01c21eb1$c1c5de10$a5e62ecf@tkmsftngxa07... That is AMAZING! Thanks so much! Is there any way to expand the Range to include the entire "B" Column? S. -----Original Message----- In the worksheet module Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("B13") Then If Target < "" Then Target.Offset(0, 6) = Date End If End If End Sub HTH "Susan Clause" <claussu@em.agr.ca wrote in message news:1313001c21eae$65808720$9ae62ecf@tkmsftngxa02... Maybe the problem is that I come from a database world and I'm trying to make Excel behave like a database, but here goes. I am trying to a default value for a cell. When the user enters data into cell B13, I would like the current date to appear in cell H13. I have used the Now() function (also works in databases!), but everytime I open the spreadsheet, of course it changes the date to today's date when what I really want to is the date that the data was actually entered. Any help would be appreciated. Sue. . |
| 1001 | Sure Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then If Target < "" Then Target.Offset(0, 6) = Date End If End If End Sub HTH "Susan Clause" <claussu@em.agr.ca wrote in message news:1131f01c21eb1$c1c5de10$a5e62ecf@tkmsftngxa07... That is AMAZING! Thanks so much! Is there any way to expand the Range to include the entire "B" Column? S. -----Original Message----- In the worksheet module Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("B13") Then If Target < "" Then Target.Offset(0, 6) = Date End If End If End Sub HTH "Susan Clause" <claussu@em.agr.ca wrote in message news:1313001c21eae$65808720$9ae62ecf@tkmsftngxa02... Maybe the problem is that I come from a database world and I'm trying to make Excel behave like a database, but here goes. I am trying to a default value for a cell. When the user enters data into cell B13, I would like the current date to appear in cell H13. I have used the Now() function (also works in databases!), but everytime I open the spreadsheet, of course it changes the date to today's date when what I really want to is the date that the data was actually entered. Any help would be appreciated. Sue. . |
| 1002 | Wilson, Your line: If Target = Range("B13") Then Is actually comparing the values of the range Target and the range B13, since .Value is the default property or range objects. The date would have been entered for cell B13, and only other changes where the value actually equaled that of B13. The correct way would have been If Not Intersect(Target, Range("B13")) Is Nothing Then though If Not Intersect(Target, Range("B:B")) Is Nothing Then would match the OP's request. HTH, Bernie "Wilson" <jwilson@wickes.com wrote in message news:#$ofMCrHCHA.2644@tkmsftngp09... In the worksheet module Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("B13") Then If Target < "" Then Target.Offset(0, 6) = Date End If End If End Sub HTH "Susan Clause" <claussu@em.agr.ca wrote in message news:1313001c21eae$65808720$9ae62ecf@tkmsftngxa02... Maybe the problem is that I come from a database world and I'm trying to make Excel behave like a database, but here goes. I am trying to a default value for a cell. When the user enters data into cell B13, I would like the current date to appear in cell H13. I have used the Now() function (also works in databases!), but everytime I open the spreadsheet, of course it changes the date to today's date when what I really want to is the date that the data was actually entered. Any help would be appreciated. Sue. |
| 1004 | Excellent! Thanks to all! :) Sue. -----Original Message----- Susan, Try this Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Not Intersect(Target, Range("B:B")) Is Nothing Then For Each myCell In Intersect(Target, Range("B:B")) If myCell.Value < "" Then myCell.Offset(0, 6).Value = Date End If Next myCell End If End Sub HTH, Bernie "Susan Clause" <claussu@em.agr.ca wrote in message news:1131f01c21eb1$c1c5de10$a5e62ecf@tkmsftngxa07... That is AMAZING! Thanks so much! Is there any way to expand the Range to include the entire "B" Column? S. -----Original Message----- In the worksheet module Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("B13") Then If Target < "" Then Target.Offset(0, 6) = Date End If End If End Sub HTH "Susan Clause" <claussu@em.agr.ca wrote in message news:1313001c21eae$65808720$9ae62ecf@tkmsftngxa02... Maybe the problem is that I come from a database world and I'm trying to make Excel behave like a database, but here goes. I am trying to a default value for a cell. When the user enters data into cell B13, I would like the current date to appear in cell H13. I have used the Now() function (also works in databases!), but everytime I open the spreadsheet, of course it changes the date to today's date when what I really want to is the date that the data was actually entered. Any help would be appreciated. Sue. . . |
| 1007 | Wilson, both of the cells N3 and C7 are formatted as a date does this make a difference and in your Last Sub, I have a rap problem. Maybe you could help with these suggestions. TIA Greg "Wilson" <jwilson@wickes.com wrote in message news:eDda3UpHCHA.2464@tkmsftngp10... Can't be of any help. Works fine on my Excel 2000 Windows 2K PRO ????? "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#yqd5UiHCHA.1740@tkmsftngp10... Wilson, Sub first works great, Sub Last, I get a "Type Mismatch" error. Greg "Wilson" <jwilson@wickes.com wrote in message news:eAL5yuhHCHA.2272@tkmsftngp09... Here you go, Greg Sub First() Dim r As Integer r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) For r = 8 To 21 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r End Sub Sub Last() Dim r As Integer Dim e r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) + 15 Cells(r - 1, 3).FormulaR1C1 = "=DAY(DATE(YEAR(R[-3]C[11]),MONTH(R[-3]C[11])+1,0))" e = Cells(r - 1, 3) For r = 8 To e - 9 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r Cells(6, 3).ClearContents End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:ecoKAgfHCHA.2240@tkmsftngp08... Wilson, your macros work perfectly. Now how do I improve on them and fill C8 to C??? for the balance of the pay period. In other words your macros fill the First for the first day of the month and Last, the 16th. Now I want to fill column C to the 15th for the First and 16th to end of month for Last. TIA Greg "Wilson" <jwilson@wickes.com wrote in message news:uSwOcZfHCHA.2644@tkmsftngp10... Assign these macros to the buttons Sub First() Range("C7") = Range("N3") End Sub Sub Last() Range("C7") = Range("N3") + 15 End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#n9AcRfHCHA.2496@tkmsftngp08... I have an option button from the forms toolbar on my sheet. There are 2 options, First and Last. If First is chosen, I would like cell C7 to reflect the first date of the month, which is indicated in Cell N3. If Last is chosen, I would like the 16th of the month in Cell C7. How do I accomplish this feat. TIA Greg |
| 1008 | Wilson, ignore my last post. It was a rap problem. Works like a dream.............Thanks Greg "Wilson" <jwilson@wickes.com wrote in message news:eDda3UpHCHA.2464@tkmsftngp10... Can't be of any help. Works fine on my Excel 2000 Windows 2K PRO ????? "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#yqd5UiHCHA.1740@tkmsftngp10... Wilson, Sub first works great, Sub Last, I get a "Type Mismatch" error. Greg "Wilson" <jwilson@wickes.com wrote in message news:eAL5yuhHCHA.2272@tkmsftngp09... Here you go, Greg Sub First() Dim r As Integer r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) For r = 8 To 21 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r End Sub Sub Last() Dim r As Integer Dim e r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) + 15 Cells(r - 1, 3).FormulaR1C1 = "=DAY(DATE(YEAR(R[-3]C[11]),MONTH(R[-3]C[11])+1,0))" e = Cells(r - 1, 3) For r = 8 To e - 9 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r Cells(6, 3).ClearContents End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:ecoKAgfHCHA.2240@tkmsftngp08... Wilson, your macros work perfectly. Now how do I improve on them and fill C8 to C??? for the balance of the pay period. In other words your macros fill the First for the first day of the month and Last, the 16th. Now I want to fill column C to the 15th for the First and 16th to end of month for Last. TIA Greg "Wilson" <jwilson@wickes.com wrote in message news:uSwOcZfHCHA.2644@tkmsftngp10... Assign these macros to the buttons Sub First() Range("C7") = Range("N3") End Sub Sub Last() Range("C7") = Range("N3") + 15 End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#n9AcRfHCHA.2496@tkmsftngp08... I have an option button from the forms toolbar on my sheet. There are 2 options, First and Last. If First is chosen, I would like cell C7 to reflect the first date of the month, which is indicated in Cell N3. If Last is chosen, I would like the 16th of the month in Cell C7. How do I accomplish this feat. TIA Greg |