add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
| 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 |
| 14 | Thanks Tom That worked.............you were right, column contains multiple words. Just curious though, the "Asterix", what does that do for the formula?? Appreciate your time. Andy "Tom Ogilvy" <twogilvy@msn.com wrote in message news:uCytHuJFCHA.2488@tkmsftngp04... If the cells contain either Blue or Red or some other single word, then your formula should work although you don't need all the parens. =COUNTIF(A1:A2000,"Blue")+COUNTIF(A1:A2000,"Red") If the cells contain multiple words =COUNTIF(A1:A2000,"*Blue*")+COUNTIF(A1:A2000,"*Red*") although the second part would count a cell containing "scored" Anyway, it should work, so if it isn't, maybe a more detailed expanation of what is contained in the cell and your criteria (are you only counting cells that contain both the words Blue and Red) Regards, Tom Ogilvy Andy <toastmaster99NOSPAM@hotmail.com wrote in message news:R6LO8.3505$H67.18628@tor-nn1.netcom.ca... Good day folks. This is a relatively simple puzzle for those experienced users, but is has me baffled........ I am trying to count the number of times specific different words appear in a column. In a column that contain many many names of colors, my challenge is to count the total of the number of times Blue and Red appear in that column. So far this is what I have : =(COUNTIF(A1:A2000,"Blue")+(COUNTIF(A1:A2000,"Red"))) I get the count on Blue but not the added count on Red...........mmmmmmmmmm Any assistance would be greatly appreciated. Andy Please remove "NOSPAM" if replying by email. |
| 17 | That's brilliant, fits perfectly with a little project I have. Thanks Andy "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D0A6AF7.4030905@contextures.com... You can do this on the worksheet: 1. In cell B4, enter the number 1 2. In cell C4, enter the following formula: =REPT("y",B4*100) 3. Format cell C4 as Monotype Sorts font, and add a border to the cell. 4. Adjust the width of cell C4 to fit the bar. 5. Now, you can enter a percentage in cell B4 and the bar in cell C4 will be adjusted. Note: for a smaller thermometer, use x, and for a larger one, use z Peter Kretzman wrote: Does anyone know a product or available control that will let me display a visual for percent complete? A simple horizontal thermometer, filled accordingly, is what I was thinking of. Thanks, PK -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 35 | Hi Martin, Hope I have read you correctly. Going by your config described, try this set-up: a. Enter a formula in col AG to pick out the min % value for each row of your reference table first. For eg: in cell AG33, enter =MIN(AH33:AO33). Copy the formula down to AG57. b. Enter =VLOOKUP(M3,$AG$33:$AP$57,10,FALSE), where M3 contains the formula =MIN(AH33:AO57). The vlookup will now return the value of 18. Hth Max -----Original Message----- excel 2000 I am trying to find the corresponding value of a cell. I am using =MIN(AH33:AO57) to find the minimum %. Having found that value I want to then find the value in column AP. This value is a whole number. I have tried =VLOOKUP(M3, $AH$33:$AP$57, 9,0) but all I am getting is #N/A An example is:- -3% 15% -9% -14% 10% 2% -7% 5% 18 So if in the range AH33:AO57 the above row has the minimum % of -14% then vlookup would return the value of 18 Any help would be greatly appreciated. Ta, Martin . |
| 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 . . |
| 48 | hi all, i have been looking at this for an hour or so, and either ive lost my marbles or excel is trying to wind me up; in cell a1 i have value 569212145 in cell a2 i have value 569211864 why does the formula =IF(A1A2,"YES","NO") give NO???? both cells are format general, is there something completely glaringly obvious that I am missing? |
| 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 !!! |
| 54 | Cell A2 is probably formatted as text. or the value was entered with a leading apostrophe. Format the cell as General or another numeric format, edit the cell (F2) and hit enter. In article <f36b01c215f9$94863aa0$19ef2ecf@tkmsftngxa01, Dave <pepperds@lycos.co.uk wrote: hi all, i have been looking at this for an hour or so, and either ive lost my marbles or excel is trying to wind me up; in cell a1 i have value 569212145 in cell a2 i have value 569211864 why does the formula =IF(A1A2,"YES","NO") give NO???? both cells are format general, is there something completely glaringly obvious that I am missing? |
| 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 !!! |
| 72 | I am using XL2000 and Win 98. I don’t know if this problem is a Macro issue or a general Excel issue, but I start with this news group. I get an error message, “Cannot paste that macro formula into a worksheet” when ever I try to copy and paste a cell or group of cells form one workbook to another. This only happens in workbooks with macros, however the message occurs regardless of whether the copied cell is references in a macro. I can use the Paste Special, Formulas but that loses formatting. The Paste Special, Values or Paste Special, Formats always yields the error message. The details of the error say that I have caused an invalid page fault in Excel.exe. I have seen other posts in which the same error seems to prevent the file from opening, but this is not my problem. Any advice appreciated. Ray Wright -- raycyn.wright@prodigy.net |
| 75 | Brian, Thank you so very much for taking time to reply. You solved my problem perfectly! Again, thank you. Greg -----Original Message----- Greg, There are some things the macro recorder does not do well. The solution is the concatenation function. You can use this function in a macro or on the worksheet self. So the simple way is to enter this formula in cell C1: = A1 & ", " & B1 Copy this down for all the rows. Then you'll probably want to do a copy and paste special and choose values to convert the formula to values. HTH, Brian Greg Hight wrote: Kindly email me if you have a solution This should be a simple problem with a simple solution, but I'm stumped! I have several thousand rows and two columns. I want the contents of one column to appear in the other column next to the contents of the other column. Specifically, Column A contains last name Column B contains first name I want first name following last name, separated by comma and space. I've tried recording macros, but even when the relative function is applied, the macro only records exactly what I copied when creating the macro...it does not read the contents of the next row and then paste them to the new cell. Thanks very much. . |
| 78 | i need to be able to sort an excel list by A-Z, however when i am running my macro it copies and past values into the list to be sorted, but when it copies and past blank cells that have a formula in them it sorts those cells as actually having something in them, i don't want those cells to be included before my actual values. Is there a way to capture that range or anyrange in VB so that i could somewhere on a worksheet tally up the total cells with data using counta() and then import that number into VB to delete the remainder of cells (blank cells)? or is there just a simple way to exclude cells that contain the "" value from a formula after they have been copy and pasted with values only? |
| 87 | I had a typo when converting to my spreadsheet. This formula works well and will take into account the date format changing and changes to the text string. Thanx!!! "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 !!! |
| 94 | I need to know how to link a formula in a cell in one worksheet to a cell in another worksheet in the same workbook. So if it works the numbers and formulas will continue to add up in both worksheets as data is added. Thank you. |
| 98 | 2 files open first file enter = in formula bar, switch to 2nd book select cell, press enter Dave -----Original Message----- I need to know how to link a formula in a cell in one worksheet to a cell in another worksheet in the same workbook. So if it works the numbers and formulas will continue to add up in both worksheets as data is added. Thank you. . |
| 104 | Suppose your page #s are in cells A1:A20. In an open column to the right (say col. B), put this in B1 and copy the formula down to the bottom: =RIGHT(A1,LEN(A1)-FIND(" ",A1))*1 Then in another open column to the right (say C), put this in C1: =RANK(B1,$B$1:$B$20,1) and copy down to the bottom. Now sort all your data by column C, ascending. HTH Jason Atlanta, GA -----Original Message----- can anyone suggest an easy way to sort letters and numbers in a correct order: like: Page 1 Page 2 .. .. .. Page 19 Page 20 instead of: Page 19 Page 2 Page 20 Page 21 thanks a lot for the input . |
| 105 | Select Column A, for example, with A1 active (i.e., A1 in the name box on the formula bar). Choose Format/Conditional Formatting and enter, using the dropdowns and textbox: Formula Is =ISBLANK(A1) Click on Format/Pattern, choose green, then OK, OK. In article <ugs5t2mk9oc42a@corp.supernews.com, Splash <splash@mosquitonet.com wrote: Is there a way to make all empty cells in a column be formatted as a color, and when they are no longer empty, that that color be removed (and they have no color)? Thanks, guys. |
| 120 | Joe, If you have a column of constants (in A2:A10) and a row of other constants (in B1:J1), and wanted to create a table, you might use a formula like (in cell B2) =$A2*B$1 and then copy it to cells B2:J10 to create the table. HTH, Bernie Joe Hourigan wrote: Can someone please give me an example of when it is most appropriate to use a mixed reference in a spreadsheet, as opposed to an absolute reference or a relative reference? In other words, what type of analysis would one be doing when a mixed reference cell would be needed? (E.g. Mortgage calculations? car payments? etc.) |
| 121 | So what happens if there are two spaces? the formula won't work, is there an easy way to modify it to all for X amount of spaces? -----Original Message----- Suppose your page #s are in cells A1:A20. In an open column to the right (say col. B), put this in B1 and copy the formula down to the bottom: =RIGHT(A1,LEN(A1)-FIND(" ",A1))*1 Then in another open column to the right (say C), put this in C1: =RANK(B1,$B$1:$B$20,1) and copy down to the bottom. Now sort all your data by column C, ascending. HTH Jason Atlanta, GA -----Original Message----- can anyone suggest an easy way to sort letters and numbers in a correct order: like: Page 1 Page 2 .. .. .. Page 19 Page 20 instead of: Page 19 Page 2 Page 20 Page 21 thanks a lot for the input . . s |
| 124 | To anwser your second question, In other words, what type of analysis would one be doing when a mixed reference cell would be needed? (E.g. Mortgage calculations? car payments? etc.) the type of analysis is irrelevant when deciding whether to use relative, mixed, or absolute references. It's dependent upon the layout of the data in your worksheets and how you go about constructing formulas and copying them to new locations. HTH Jason Atlanta, GA -----Original Message----- Can someone please give me an example of when it is most appropriate to use a mixed reference in a spreadsheet, as opposed to an absolute reference or a relative reference? In other words, what type of analysis would one be doing when a mixed reference cell would be needed? (E.g. Mortgage calculations? car payments? etc.) . |
| 127 | Hello! I'm creating a excel spreadsheet to import information into a SQL Server Database. I've got a few columns that I've used functions to create some of the data. Now here's my question: Is there a way to copy that value that's produced by a function to another cell, so that it could be imported into the database. For example, a formula I have on the spreadsheet is =A3+17, which gives me a value. I'd like to have that value copied into a cell, so that it could be imported into the database. Currently the cell hold the value =A3+17, I want the result, say 20, not the actual function =A3+17 in another cell so I can DTS it into my database. I hope this has made some sense. Thanks, Erica |
| 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! |
| 137 | The scrolling speed is controlled by the distance you've moved the pointer off the sheet. For example, if you select row 1, and drag down till your pointer is pointing at the sheet tab (just barely off the worksheet), the scrolling should be at a moderate speed. If you point at the Status Bar (at the bottom of the Excel window), the scrolling speeds up. If you move your pointer to the bottom of the monitor, you reach those lightning speeds. There are other ways to select rows, that you might find easier. 1. Select the first row, then release the mouse button 2. Use the scroll bar to scroll down (without selecting), till you can see the last row you want to select. 3. Hold the Shift key and click in the last row. Or, if you know exactly which rows you want to select: 1. Click in the Name Box (to the left of the Formula Bar) 2. Type the range you want to select, e.g. 1:100 3. Press the Enter key 4. If you want to change the end row after using this method, hold the Shift key and click on a different ending row. George Smirnoff wrote: Is there any way to adjust the speed at which the cursor will scroll down when you use the mouse? For instance if I highlight Row 1 and want to highlight Row 1-1000, when I move the cursor down off the page, it scrolls at about 500 rows a second. So obviously I end up highlighting Rows 1-10000. Can I adjust this? -- 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 |
| 144 | No... and replace text as you type is turned off... But do you mean that this is not what append when you type a formula... here it does that on each installation of excel, even on another version at home!!! It's when I have a quoted text... same result here! +"TEST" &9:9 Thanks.. "Peo Sjoblom" <terre08@mvps.org a écrit dans le message de news: #6P904tFCHA.2656@tkmsftngp12... Check in toolsautocorrect and see if you have +"(" & in the replace what box and +"("&7:7 in the with box... Delete it. -- Regards, Peo Sjoblom "Yves Allaire" <y.allaire@spamsympatico.ca wrote in message news:BkIP8.1563$YG5.783051@news20.bellglobal.com... Can someone help me stop or at least explain this behavior... When typing a formula in a cell like this : +"(" & If I type a space after the "&" the screen scrolls to the right and enter automatically : +"("&7:7 I can prevent this by not typing a space, but what's the use of this behavior? Thanks. |
| 146 | That's happening because you are still pressing the Shift key when you type the space (Shift+Space selects an entire row). Similarly, Ctrl+Space selects an entire column. Release the Shift key before you type the space, and you'll be a happy camper. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Yves Allaire" <y.allaire@spamsympatico.ca wrote in message news:BkIP8.1563$YG5.783051@news20.bellglobal.com... Can someone help me stop or at least explain this behavior... When typing a formula in a cell like this : +"(" & If I type a space after the "&" the screen scrolls to the right and enter automatically : +"("&7:7 I can prevent this by not typing a space, but what's the use of this behavior? Thanks. |
| 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 |
| 149 | Good catch John.. -- Regards, Peo Sjoblom "John Walkenbach" <john@j-walk.com wrote in message news:#QPqJFuFCHA.2424@tkmsftngp04... That's happening because you are still pressing the Shift key when you type the space (Shift+Space selects an entire row). Similarly, Ctrl+Space selects an entire column. Release the Shift key before you type the space, and you'll be a happy camper. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Yves Allaire" <y.allaire@spamsympatico.ca wrote in message news:BkIP8.1563$YG5.783051@news20.bellglobal.com... Can someone help me stop or at least explain this behavior... When typing a formula in a cell like this : +"(" & If I type a space after the "&" the screen scrolls to the right and enter automatically : +"("&7:7 I can prevent this by not typing a space, but what's the use of this behavior? Thanks. |
| 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! |
| 155 | I should know... that happens to me ALL THE TIME. -John "Peo Sjoblom" <terre08@mvps.org wrote in message news:uaequLuFCHA.1596@tkmsftngp13... Good catch John.. -- Regards, Peo Sjoblom "John Walkenbach" <john@j-walk.com wrote in message news:#QPqJFuFCHA.2424@tkmsftngp04... That's happening because you are still pressing the Shift key when you type the space (Shift+Space selects an entire row). Similarly, Ctrl+Space selects an entire column. Release the Shift key before you type the space, and you'll be a happy camper. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Yves Allaire" <y.allaire@spamsympatico.ca wrote in message news:BkIP8.1563$YG5.783051@news20.bellglobal.com... Can someone help me stop or at least explain this behavior... When typing a formula in a cell like this : +"(" & If I type a space after the "&" the screen scrolls to the right and enter automatically : +"("&7:7 I can prevent this by not typing a space, but what's the use of this behavior? Thanks. |
| 156 | Thanks.... It looked like an easy one... "John Walkenbach" <john@j-walk.com a écrit dans le message de news: #QPqJFuFCHA.2424@tkmsftngp04... That's happening because you are still pressing the Shift key when you type the space (Shift+Space selects an entire row). Similarly, Ctrl+Space selects an entire column. Release the Shift key before you type the space, and you'll be a happy camper. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Yves Allaire" <y.allaire@spamsympatico.ca wrote in message news:BkIP8.1563$YG5.783051@news20.bellglobal.com... Can someone help me stop or at least explain this behavior... When typing a formula in a cell like this : +"(" & If I type a space after the "&" the screen scrolls to the right and enter automatically : +"("&7:7 I can prevent this by not typing a space, but what's the use of this behavior? Thanks. |
| 161 | Hi, I need to insert/delete a page break at a cell based on if another cell has a picture in it. So far i have a formula =IF(ISBLANK(A5),<delete page break,<insert page break) The question is - can this be done, and if so, what should I put in the <delete/insert page break parts of the formula? Thanks in advance, Joseph |
| 164 | You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom |
| 170 | Depending on how specific your requirements are (always strip the first character? only if it alpha? only if it is "L"?) you can use one of the following formulas =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"L",""),".",""),"0","") =SUBSTITUTE(MID(A1,2,LEN(A1)-1),".0","") =SUBSTITUTE(IF(ISNUMBER(LEFT(A1,1)),A1,MID(A1,2,LEN(A1)-1)),".0","") There are a variety of variations on these formulas depending on your specific needs. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Manoj" <mpillai@mecu.com wrote in message news:dffc01c216f7$27f38880$9ee62ecf@tkmsftngxa05... Is it possible to convert a code such as L1234.019 into 123419 I want to take the "L" and "0" out and combine the "1234" and the "19" Thanks for your help. |
| 173 | Hi George, Can't think of anything in particular. I'd check the last cell (ctrl+end) to see if that places you beyond your 2000 rows of data that you say you have. Do you have event macros in the worksheet, volatile user defined functions. Formulas dependent on data being changed which could trigger volatile functions. Are other sheets dependent on the data in the sheet being changed. You might check my page on Slow Response /dmcritchie/excel/slowresp.htm "George" <gsmirnoff@yahoo.com wrote in message news:dfc301c216dd$d5df9b60$a5e62ecf@tkmsftngxa07... As of a few months ago, whenever I do a search/replace in a spreadsheet for a particular column, it seems to take unusually long to get it done. I have a spreadsheet of 2000 rows, and when asking to search for a particular string to replace just for that row, the function takes 2- 3 minutes during which the highlighted column flashes as if stuck in an endless loop. This just started happening recently. Any ideas why? |
| 175 | Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 178 | Can you loop through each cell in the ranges and make them numeric this way Sub MakeNumeric() 'Multilpies each cell by 1 to make it numeric Dim DataRng As Range Dim cell As Range Set DataRng = Range("A2:A" & Range("A65536").End(xlUp).Row) For Each cell In DataRng cell = cell * 1 Next cell End Sub HTH? "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 179 | Howard, copy an empty cell, select your data (source and lookup table), do editpaste special and select add. That will make them all numeric.. -- Regards, Peo Sjoblom "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 182 | Thank you, but isn't this similar to what I do already. OK its a bit quicker but still seems a bit of a cheating way to do it. I was hoping there was some way of setting the data type like you can set the format (the lookup tables can be quite large) Howard "Peo Sjoblom" <terre08@mvps.org wrote in message news:OaVQcFwFCHA.2672@tkmsftngp13... Howard, copy an empty cell, select your data (source and lookup table), do editpaste special and select add. That will make them all numeric.. -- Regards, Peo Sjoblom "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 186 | Thank you, but this is essentially doing in code what I currently do by hand just copying down the formula =1*A1 and it would still need to be done with both the lookup table and any spreadsheets that use it. Surely there is a way to force cells to have a data type!? After all, what is the point of having a format command to show text or numeric if it doesn't really change the data type? Howard "Wilson" <jwilson@wickes.com wrote in message news:uL5oYAwFCHA.2552@tkmsftngp05... Can you loop through each cell in the ranges and make them numeric this way Sub MakeNumeric() 'Multilpies each cell by 1 to make it numeric Dim DataRng As Range Dim cell As Range Set DataRng = Range("A2:A" & Range("A65536").End(xlUp).Row) For Each cell In DataRng cell = cell * 1 Next cell End Sub HTH? "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 187 | You might want to use formula instead of value Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End Sub "Wilson" <jwilson@wickes.com wrote ... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 188 | Change the data type, before you run the macro. "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo4oe$er2$1@newsg2.svr.pol.co.uk... Thank you, but this is essentially doing in code what I currently do by hand just copying down the formula =1*A1 and it would still need to be done with both the lookup table and any spreadsheets that use it. Surely there is a way to force cells to have a data type!? After all, what is the point of having a format command to show text or numeric if it doesn't really change the data type? Howard "Wilson" <jwilson@wickes.com wrote in message news:uL5oYAwFCHA.2552@tkmsftngp05... Can you loop through each cell in the ranges and make them numeric this way Sub MakeNumeric() 'Multilpies each cell by 1 to make it numeric Dim DataRng As Range Dim cell As Range Set DataRng = Range("A2:A" & Range("A65536").End(xlUp).Row) For Each cell In DataRng cell = cell * 1 Next cell End Sub HTH? "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 189 | Please explain why, David? TIA "" <dmcritchie@msn.com wrote in message news:O9FYSawFCHA.2604@tkmsftngp12... You might want to use formula instead of value Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End Sub "Wilson" <jwilson@wickes.com wrote ... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 190 | Hi Wilson, With .value you have converted all formulas to constants. "Wilson" <jwilson@wickes.com wrote ... Please explain why, David? |
| 191 | Thanks, David "" <dmcritchie@msn.com wrote in message news:#xMarpwFCHA.220@tkmsftngp04... Hi Wilson, With .value you have converted all formulas to constants. "Wilson" <jwilson@wickes.com wrote ... Please explain why, David? |
| 196 | AFAIK you can't do this with a formula, because there isn't a character that Excel recognizes as a page break character. You could record or write a macro and run it before you print the worksheet, inserting a page break based on the content of cell A5. Joseph K wrote: Hi, I need to insert/delete a page break at a cell based on if another cell has a picture in it. So far i have a formula =IF(ISBLANK(A5),<delete page break,<insert page break) The question is - can this be done, and if so, what should I put in the <delete/insert page break parts of the formula? Thanks in advance, Joseph -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 197 | Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com |
| 201 | You could perhaps, try using "helper" columns. For example, if your Vlookup formula is: =Vlookup(A1,C1:E100,2,0) Change it to =Vlookup(B1,C1:E100,2,0) Where B1 contains the formula =A1*1 Using the same concept, pre-establish a column with a similar formula to take care of the look-up array of your imported data. HTH RD "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo4fe$ceh$1@newsg4.svr.pol.co.uk... Thank you, but isn't this similar to what I do already. OK its a bit quicker but still seems a bit of a cheating way to do it. I was hoping there was some way of setting the data type like you can set the format (the lookup tables can be quite large) Howard "Peo Sjoblom" <terre08@mvps.org wrote in message news:OaVQcFwFCHA.2672@tkmsftngp13... Howard, copy an empty cell, select your data (source and lookup table), do editpaste special and select add. That will make them all numeric.. -- Regards, Peo Sjoblom "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 204 | If your VLOOKUP is of the exact-match type, you might want to have a look at a thread that discusses the issue you rise: /board/viewtopic.php?topic=10915&forum=2 Aladin "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 210 | You could use INDEX instead (array-entered: Ctrl+Shift+Enter) =INDEX($I$1:$I$18,MATCH(A1*1,$H$1:$H$18*1,0)) where the values to match are in column I and the value to be returned is in column H. If it doesn't find the value it will return an #N/A error. Howard wrote: Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 213 | Correction: where the values to match are in column H and the value to be returned is in column I. Debra Dalgleish wrote: You could use INDEX instead (array-entered: Ctrl+Shift+Enter) =INDEX($I$1:$I$18,MATCH(A1*1,$H$1:$H$18*1,0)) where the values to match are in column I and the value to be returned is in column H. If it doesn't find the value it will return an #N/A error. Howard wrote: Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 217 | Very nice function for most people's email address. But there are some knuckleheads that have numbers in their addresses: You might want to change this line (but it occurs twice): If Mid(s, i, 1) Like "[A-Za-z.-]" Then to something like: If Mid(s, i, 1) Like "[A-Za-z0-9.-]" Then In fact, if you see email addresses that aren't alpha numeric/with dashes, you might want to add those characters, too. (Underscore comes to mind.) From ec35720@msn.com <vbg John Walkenbach wrote: You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom -- Dave Peterson ec35720@msn.com |
| 222 | How long would you like to be annoying your readers. Get really boring and trashy after say 3 seconds. There is no flashing font built into Excel, you would have to start a timer and flash the color (change the color) say every second -- stealing from the performance of your Excel. Advise change the color so you won't be confused with bad code, but either way it would be difficult to tell the difference. You might want to look at cell validation instead. Data Validation, Debra Dalgleish at contextures.com /xlDataVal01.html Debra's is better than mine but mine has some formula examples: /dmcritchie/excel/validation.htm since we don't know what you are really going after as an error. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "LK" <lazark@precisemailing.com wrote in message news:102de01c21716$77665630$3bef2ecf@TKMSFTNGXA10... is it possible to blink or flash a cell to get the readers attention? |
| 227 | Good point, Dave. I should have pointed out that it was not tested thoroughly. I whipped it off in about five minutes. Here's Version 2.0. I used a Const declaration to simplify changes to that list of characters. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String Const CharList As String = "[A-Za-z0-9.-_]" 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like CharList Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like CharList Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function -John "Dave Peterson" <ec35720@msn.com wrote in message news:3D0FB29E.3151E5EE@msn.com... Very nice function for most people's email address. But there are some knuckleheads that have numbers in their addresses: You might want to change this line (but it occurs twice): If Mid(s, i, 1) Like "[A-Za-z.-]" Then to something like: If Mid(s, i, 1) Like "[A-Za-z0-9.-]" Then In fact, if you see email addresses that aren't alpha numeric/with dashes, you might want to add those characters, too. (Underscore comes to mind.) From ec35720@msn.com <vbg John Walkenbach wrote: You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom -- Dave Peterson ec35720@msn.com |
| 228 | Oops, the hyphen must be the last character in the list! Change it to this: Const CharList As String = "[A-Za-z0-9._-]" -John "John Walkenbach" <john@j-walk.com wrote in message news:O4TZOCyFCHA.2076@tkmsftngp04... Good point, Dave. I should have pointed out that it was not tested thoroughly. I whipped it off in about five minutes. Here's Version 2.0. I used a Const declaration to simplify changes to that list of characters. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String Const CharList As String = "[A-Za-z0-9.-_]" 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like CharList Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like CharList Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function -John "Dave Peterson" <ec35720@msn.com wrote in message news:3D0FB29E.3151E5EE@msn.com... Very nice function for most people's email address. But there are some knuckleheads that have numbers in their addresses: You might want to change this line (but it occurs twice): If Mid(s, i, 1) Like "[A-Za-z.-]" Then to something like: If Mid(s, i, 1) Like "[A-Za-z0-9.-]" Then In fact, if you see email addresses that aren't alpha numeric/with dashes, you might want to add those characters, too. (Underscore comes to mind.) From ec35720@msn.com <vbg John Walkenbach wrote: You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom -- Dave Peterson ec35720@msn.com |
| 232 | This happens when your list has many formulas. There's an article in the MSKB that explains: XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode" Q213886) /default.aspx?scid=kb;EN-US;q213886 or XL: AutoFilter Status Bar Message Shows "Filter Mode" (Q189479) /default.aspx?scid=kb;en-us;Q189479 A code solution: XL: How to Count Rows Displayed After Data Has Been Filtered (Q148621) /default.aspx?scid=kb;en-us;Q148621 ***A workaround that I sometimes use: If you want to see a record count for the visible rows which contain data, you can use the Subtotal function in a formula in the same row as your headings. For example, if you want to count the visible entries in column C which contain numbers, you could use this formula: =SUBTOTAL(2,C:C) The 2 in the first argument tells Excel to use the COUNT function on the visible cells in the range. If you want to count rows that contain text, you could change the formula: =SUBTOTAL(3,D:D)-1 The 3 is for the COUNTA function, and the -1 removes one for the row which contains the column heading. NOTE: Blank cells will not be counted -- use a column with no blank cells. Celeste wrote: Sometimes the number of records are displayed in the lower left hand corner of screen, and sometimes they are not. I know if I right click in that location, another drop down menu appears. One can select "count nums", "sum", etc. It doesn't seem to make any difference which is selected. I really use the number of records displayed by the autofilter selection but I can't get it to do it every time. Lately, it hasn't been working at all. It doesn't seem to necessarily be related to free RAM or file size or even the number of records included in the autofilter. If anyone knows how this works, please pass it on to me! Thanks! Grand Junction, CO -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 233 | Thanks, I'll check these out. Typically, I do have lots of formulae. -----Original Message----- This happens when your list has many formulas. There's an article in the MSKB that explains: XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode" Q213886) /default.aspx?scid=kb;EN- US;q213886 or XL: AutoFilter Status Bar Message Shows "Filter Mode" (Q189479) /default.aspx?scid=kb;en- us;Q189479 A code solution: XL: How to Count Rows Displayed After Data Has Been Filtered (Q148621) /default.aspx?scid=kb;en- us;Q148621 ***A workaround that I sometimes use: If you want to see a record count for the visible rows which contain data, you can use the Subtotal function in a formula in the same row as your headings. For example, if you want to count the visible entries in column C which contain numbers, you could use this formula: =SUBTOTAL(2,C:C) The 2 in the first argument tells Excel to use the COUNT function on the visible cells in the range. If you want to count rows that contain text, you could change the formula: =SUBTOTAL(3,D:D)-1 The 3 is for the COUNTA function, and the -1 removes one for the row which contains the column heading. NOTE: Blank cells will not be counted -- use a column with no blank cells. Celeste wrote: Sometimes the number of records are displayed in the lower left hand corner of screen, and sometimes they are not. I know if I right click in that location, another drop down menu appears. One can select "count nums", "sum", etc. It doesn't seem to make any difference which is selected. I really use the number of records displayed by the autofilter selection but I can't get it to do it every time. Lately, it hasn't been working at all. It doesn't seem to necessarily be related to free RAM or file size or even the number of records included in the autofilter. If anyone knows how this works, please pass it on to me! Thanks! Grand Junction, CO -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 256 | Hi, I have a column of cells with numbers from 1 to 20. I would like to count how many 1's, 2's, etc. What formular should I use? TJ |
| 258 | Hi, For a solution with just formula's... If your data are in A1:A100: - enter the numbers 1 to 20 in column B - Select cells C1:C20 - type this formula: =FREQUENCY(A1:A100,$B$1:$B$20) press control-shift-enter simultaniously. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Hi, I have a column of cells with numbers from 1 to 20. I would like to count how many 1's, 2's, etc. What formular should I use? TJ . |
| 266 | Try: =IF(ISERROR(SEARCH("Inactive",B12,1)),"active","inactive") For some reason, XL's designers had Search and Find return errors rather than 0 for a non-found value. The above traps that error and returns "active". In article <d987c0b7.0206190920.5d224e76@posting.google.com, Ralph K. <thermometer@excite.com wrote: I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. |
| 272 | How can I hide a cell result which contains a formula until the actual arguiments ar entered. In other words the result is showing without entering data. The result will be text ("") and not zeros Thanks, |
| 275 | Hi Ralph, You will have to include ISERROR i.e. =IF(ISERROR(myformula),"error note", myformula) Might look something like this, since all you need is the error indicator: =IF(ISERROR(SEARCH("Inactive",B12,1)),IF(TRIM(B12)="","","active"),"inactive") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ralph K." <thermometer@excite.com wrote in message news:d987c0b7.0206190920.5d224e76@posting.google.com... I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. |
| 276 | Chris, try =SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0")) Btw, why are you not using the same naming system for your sheets? if the first sheet was named P1, then you could use this =SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'P"&ROW(INDIRECT("1:10"))&"'!D38"),"<0")) also note that this will return a #DIV/0 error if all cells are empty, you can prevent that by using wrap it in an IF function like =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0"))=0,0,SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0"))) if there are no negative numbers you could wrap with =if(sum('Patient 1:P10'!D38)=0,0,etc.. instead.. Also answered through email with sample attached -- Regards, Peo Sjoblom "Chris Bullock" <JohnCBullock@hotmail.com wrote in message news:e94701c217b5$6b9c73d0$36ef2ecf@tkmsftngxa12... Peo Sjoblom Thank You for your help with this, I did enter the formula you gave, although I did not understand what you meant by; entered with ctrl+shift&enter. I changed the cells referenced and this is the formula as I tried to use it =AVERAGE(IF('Patient 1:P10'! D38<0,'Patient 1:P10'!D38)) I am getting the #REF! error message, cell reference not valid. I do not understand why, Worksheet 1 thru 10 column D row 38 are all valid. If you are still willing to help with this I could definitely use the expert advice -----Original Message----- Try =AVERAGE(IF(A1:A100<0,A1:A100)) entered with ctrl+shift&enter -- Regards, Peo Sjoblom "Rob Fenn" <rob-fenn@maurice-phillips.co.uk wrote in message news:eBZb0h6FCHA.1360@tkmsftngp05... Chris Try the following. SUM(A1:A100)/COUNTIF(A1:A100,"0") but this will only work if there are no values less than 0. I am sure someone can think of an easier way but presumably this would work SUM(A1:A100)/(COUNT(A1:A100)-COUNTIF(A1:A100,"0")) A1:A100 is your range of data. HTH Rob "Chris Bullock" <JohnCBullock@hotmail.com wrote in message news:1027d01c217a7$48d06770$19ef2ecf@tkmsftngxa01... I am using Average functions accross worksheets and I need it to not use the cells that have a zero value in the average calculations. Is there a formula that will take care of this? The workbook is huge and deleting each cell with a zero value by hand will be very difficult. I have tried some If statements but have not been able to make any work because of limited knowledge. . |
| 277 | J.E., It works! Thanks a bunch. Ralph K. -----Original Message----- Try: =IF(ISERROR(SEARCH ("Inactive",B12,1)),"active","inactive") For some reason, XL's designers had Search and Find return errors rather than 0 for a non-found value. The above traps that error and returns "active". In article <d987c0b7.0206190920.5d224e76@posting.google.com, Ralph K. <thermometer@excite.com wrote: I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. . |
| 278 | David, Thanks for the information and links to your excel pages. Ralph K. ~~~~~~~~~~~~~~~~~~~ -----Original Message----- Hi Ralph, You will have to include ISERROR i.e. =IF(ISERROR(myformula),"error note", myformula) Might look something like this, since all you need is the error indicator: =IF(ISERROR(SEARCH("Inactive",B12,1)),IF(TRIM(B12) ="","","active"),"inactive") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ralph K." <thermometer@excite.com wrote in message news:d987c0b7.0206190920.5d224e76@posting.google.com... I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. . |
| 279 | Do you mean something like this? =IF(A1=0,"",A1+B1) HTH "Rich Mclean" <richmclean@fairchildfasteners.com wrote in message news:OQmAqm7FCHA.2640@tkmsftngp04... How can I hide a cell result which contains a formula until the actual arguiments ar entered. In other words the result is showing without entering data. The result will be text ("") and not zeros Thanks, |
| 280 | One way: =IF(OR(A1="", B1=""),"", A1+B1) In article <OQmAqm7FCHA.2640@tkmsftngp04, Rich Mclean <richmclean@fairchildfasteners.com wrote: How can I hide a cell result which contains a formula until the actual arguiments ar entered. In other words the result is showing without entering data. The result will be text ("") and not zeros Thanks, |
| 282 | One could also use isnumber and switch places with the "active" and "inactive" =IF(LEN(B12)=0,"",IF(ISNUMBER(SEARCH("Inactive",B12,1)),"inactive","active")) -- Regards, Peo Sjoblom "" <dmcritchie@msn.com wrote in message news:OM0ezo7FCHA.2388@tkmsftngp09... Hi Ralph, You will have to include ISERROR i.e. =IF(ISERROR(myformula),"error note", myformula) Might look something like this, since all you need is the error indicator: =IF(ISERROR(SEARCH("Inactive",B12,1)),IF(TRIM(B12)="","","active"),"inactive") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ralph K." <thermometer@excite.com wrote in message news:d987c0b7.0206190920.5d224e76@posting.google.com... I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. |
| 295 | Hey All, I have a workbook that has 100's of cells with formulas linking to values in another worksheet - although the locations of both of these worksheets has remained the same, somehow the worksheet with the links has 'lost' the worksheet with the source data. Every time I open it I now have to point excel to the source worksheet. is there any way to fix this short of rewriting the formulas? Thanks, Justin |
| 297 | Aladin, Thank you very much. I didn't know about that site. At least that gives a one line formula way of doing it. I'm still astounded that there is not a simple way of setting the data type from the menu (like one can in access). In a way, being able to change the FORMAT of a cell is even worse as then you lose any clues that you had to the cell's data tpye Thanks to all who replied Howard "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeoadf$k2t$1@news1.xs4all.nl... If your VLOOKUP is of the exact-match type, you might want to have a look at a thread that discusses the issue you rise: /board/viewtopic.php?topic=10915&forum=2 Aladin "Howard" <hsm@sentech.freeserve.co.uk wrote in message news:aeo1th$mbc$1@newsg3.svr.pol.co.uk... Hi I often use vlookup looking up a key composed of 4 digits to find some associated data. Sometimes these keys and data are imported from access. The problem is that sometimes these 4 digits are treated as numbers and sometimes they are treated as text. Vlookup fails if the thing you are looking up is text but in the lookup table it is numeric (or it might be the other way round!) even though there is no way to tell the difference visually - especially if the data is centred so you cannot tell by the default justification. I get round this by adding a column with formulae something like = 1*A1 where A1 contains the text/numeric digits. This forces them all to numeric, then I copy/ paste special|values back over the original data. Do this for both source and lookup table and everything is OK. But.... There must be a better way !!! How can I force the CONTENTS of a cell as opposed to the display FORMAT to be numeric? Howard |
| 305 | I can't give you the exact formula because I can't see your spreadsheet, but it seems what you need is a Lookup Table, within a nested IF statement, basically saying, "if it's due this date, put this in the cell, otherwise go look it up here, pick the right value and stick it here". You have to set up a table, either horizontally or vertically, where the forumula can go "lookup" some cell's value, with your 4 categories listed. If you want to send me the spreadsheet I'd be happy to take a look at it, when I'm at home tonight. Send it to gentlestorm2@hotmail.com Nancy Help Desk Analyst |
| 312 | Hi Justin, One possibility, if that other workbook is personal.xls it should be in your XLSTART library that you say you didn't change any locations. It should also be hidden Window, hide Perhaps you just need to open one of the workbooks before the other. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Justin" <arki3345@amtrak.com wrote in message news:1041701c217c9$04920920$19ef2ecf@tkmsftngxa01... Hey All, I have a workbook that has 100's of cells with formulas linking to values in another worksheet - although the locations of both of these worksheets has remained the same, somehow the worksheet with the links has 'lost' the worksheet with the source data. Every time I open it I now have to point excel to the source worksheet. is there any way to fix this short of rewriting the formulas? Thanks, Justin |
| 322 | Hi, Can anyone help me with this problem, I am trying to calculate a time sheet to give total hours worked over a 5 day period, as I work flexible hours I can go into debit this is the problem when I go into debit the results is a negative return e.g Hours Worked Mon 7:00 Tue 7:00 Wed 7:00 Thu 7:00 Fri 7:00 Core Time Credit Debit Total Hours carried to next period ####### Total Hours 35:00 36:00 ##### The formulas I use works fine as long as I am in credit. I would appreciate any help in finding a solution to provide me with a formula that would return an answer of minus -1:00 hour Hours Worked Mon 8:00 Tue 7:45 Wed 8:00 Thu 7:45 Fri 8:00 Core Time Credit Debit Total Hours carried to next period 3:30 Total Hours 39:30 36:00 3:30 Thanks in anticipation George D. |
| 323 | Thanks for the info. (50 meg might be worth it to find out if it can open files). Ron de Bruin wrote: 50 mb Dave I have download it for my brother two weeks ago.(10 minuts that is better than your puny modem) O yes I forgot somthing <vbg Regards Ron "Dave Peterson" <ec35720@msn.com schreef in bericht news:3D0FA860.66014496@msn.com... Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com -- Dave Peterson ec35720@msn.com |
| 326 | Is there any way to have cells that contain "0" display as blank? For example, if a cell's formula returns a zero, I'd like that cell to show as blank, instead of "$0.00" Thanks. Ken |
| 327 | One more slight change: =VLOOKUP(A2,INDIRECT("'"&A1&"'!a1:b100"),2,0) When I tested it, I put 'Jan-02 in A1. Excel liked to have this surrounded by single quotes. And the single quotes won't hurt if you really don't need them. === One more thing, do you really have the text Jan-02 in A1? If yes, then you can ignore the rest. But if you have a date in A1 (say 01/31/2002) and it's formatted to show just the month-YY, then you might have to do something like: =VLOOKUP(A2,INDIRECT("'"&text(A1,"mmm-yy")&"'!a1:b100"),2,0) wrote: Hi Brian, and Dave, Correction to original formula and modified formula The range must include Column A through Column B in order to refer to column 2 in the table. =vlookup(A2,INDIRECT(A1&"!a1:b100"),2,0) Testing shows failure to include column B results in #REF! error. "" <DavidH@OzGrid.com wrote ... Hi Brian, vlookup(a2,INDIRECT(A1&"!a1:a100"),2,0) "Brian Ferris" <brian.ferris@go.com.mt wrote ... | I would like to use a Vlookup, but I would like the name | of the sheet to vlookup to be a variable: | | eg. | Cell a1= Jan-02 | Cell a2= Brian | | Therefore | Cell a5= =vlookup(a2,'Jan-02'!a1:a100,2,0) | | If cell a1= Feb-02 | Therefore | Cell a5= =vlookup(a2,'Feb-02'!a1:a100,2,0) | | I would like to reference the sheet to look into with cell | a1. I know this can be done with the Indirect Funcion | somehow. -- Dave Peterson ec35720@msn.com |
| 328 | Never mind! Found it in Tools! Thanks anyway! "Ken Isaacson" <KJIsaacson@AllstatesWorldLegal.com wrote in message news:#lznoi9FCHA.2772@tkmsftngp13... Is there any way to have cells that contain "0" display as blank? For example, if a cell's formula returns a zero, I'd like that cell to show as blank, instead of "$0.00" Thanks. Ken |
| 332 | One more option: =IF(COUNTIF(B12,"*inactive*")0,"inactive","active") "Ralph K." wrote: I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. -- Dave Peterson ec35720@msn.com |
| 334 | much nicer Dave, but I would still test for cells that are or look blank to keep things looking more meaningful. =IF(TRIM(B12)="","",IF(COUNTIF(B12,"*inactive*"),"inactive","active")) "Dave Peterson" <ec35720@msn.com wrote ... One more option: =IF(COUNTIF(B12,"*inactive*")0,"inactive","active") "Ralph K." wrote: I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. -- Dave Peterson ec35720@msn.com |
| 338 | yep. I agree. maybe even check for both. If neither is found, then some other message (an error?). wrote: much nicer Dave, but I would still test for cells that are or look blank to keep things looking more meaningful. =IF(TRIM(B12)="","",IF(COUNTIF(B12,"*inactive*"),"inactive","active")) "Dave Peterson" <ec35720@msn.com wrote ... One more option: =IF(COUNTIF(B12,"*inactive*")0,"inactive","active") "Ralph K." wrote: I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. -- Dave Peterson ec35720@msn.com -- Dave Peterson ec35720@msn.com |
| 372 | Viewformula bar -- Regards, Peo Sjoblom "flipglen" <flipglen@stn.net wrote in message news:3d1115c3.4861924@news.stn.net... I feel silly, but I don't know how to restore the horizontal menu bar that should be the second line along the top and shows the code in a particular cel. It mysteriously disappeared! TIA Phil |
| 378 | Try a simple IF function: Set the output cell title as: Credit/(Debit) in cell c3 (credit cell): =if(a3=b3,(a3-b3),"" (interprets as if a3 is greater than or equal to b3, then put in this cell the value of (a3-b3), otherwise, put nothing. In cell d3 (debit cell): =if(a3<=b3),(b3-a3),"" I hope this is what you were looking for. Dan Winterton -----Original Message----- Hi, Can anyone help me with this problem, I am trying to calculate a time sheet to give total hours worked over a 5 day period, as I work flexible hours I can go into debit this is the problem when I go into debit the results is a negative return e.g Hours Worked Mon 7:00 Tue 7:00 Wed 7:00 Thu 7:00 Fri 7:00 Core Time Credit Debit Total Hours carried to next period ####### Total Hours 35:00 36:00 ##### The formulas I use works fine as long as I am in credit. I would appreciate any help in finding a solution to provide me with a formula that would return an answer of minus -1:00 hour Hours Worked Mon 8:00 Tue 7:45 Wed 8:00 Thu 7:45 Fri 8:00 Core Time Credit Debit Total Hours carried to next period 3:30 Total Hours 39:30 36:00 3:30 Thanks in anticipation George D. . |
| 383 | You could just leave the formats alone and sum, but you may find that your sum's give you a somewhat bizarre result: A1: 04:08:53 (4 days, 8 hours, 53 minutes) A2: 03:16:07 (3 days, 16 hours, 7 minutes) A3: =A1 + A2 === 07:25:00 (7 days, 25 hours) If you want to convert the numbers to regular XL Date/Time format (days as integers and times as fractional days, here's one way: Since XL will interpret 4:8:53 as 4 hours, 8 minutes, 53 seconds, you need to do a bit of manipulation: A1: 04:08:53 B1: =A1*60 - 1.5*INT(A1*24) === 4.370138889 A2: 03:16:07 B2: =A2*60 - 1.5*INT(A2*24) === 3.671527778 B3: =SUM(B1:B2) === 8.041666667 or 8 days 1 hour. In article <10aac01c218a2$776c9ad0$9be62ecf@tkmsftngxa03, M. E. <mcook@magellanhealth.com wrote: I am trying to be able to SUM and AVERAGE time for a month. The time the employee is available may be so many DAYS (24hours = 1 day) so many HOURS (60 minutes in an hour) and so many MINUTES. SO lets say someone worked 4 Days 8 Hours and 53 Minutes In the cell we would format this for TIME and enter it as 4:8:53 I can't think of a way to write the formula that allows us to truncate this number at the colons and multiply and or divide by the appropriate factor (24 for the first colon and 60 for the second colon.) Microsoft Excel 97 SR-2 ANY HELP? Thanks! |
| 384 | I think that excel gets the format for dates from a Windows setting. It sounds like one pc is different than the other and not recognizing the date. I'm in the US and if I type 12/31/2001, I get a date (expected). But if I type 31/12/2001, I get the text 31/12/2001--not really a date. On the "bad" pc, if you format the cell as General (Format|Cells|Number Tab), do you get a number? (I get 37621 for 12/31/2001 (using the 1900 date system). If you don't get a number, then that cell really doesn't have a date in it (no matter what it looks like). And since the value isn't numeric (it's a string), the =text() has no affect on it. If this looks like the case, then close excel and try: Start|Settings|Control Panel|Regional Settings Applet|Date Tab. Check (change to) the format. Start Excel. I'm not sure if it'll react to the change or if you'll have to change something for it to notice. Hit F2 and then enter on one of the cells. Did it fix your problem? If yes, maybe you can convert a whole column of dates at once. Select your column and do a Data|Text to columns. Follow the wizard and make sure you choose the correct (mdy?) format on step 3 of the wizard. Good luck, James Goodchild wrote: Hi all We have a Windows 2000 server running terminal services and Citrix. Because of a requirement from a custom built program, MS Office 2000 was installed to the local hard drive of the Citrix server as well as to the Citrix neighborhood. Everything loads fine and other than one formatting issue, everything seems to work. Here is the problem, in Excel 2000 in the local copy if you type the following date 12/31/01 in cell A1 and then enter the formula =TEXT(A1,"Mmm. Dd yyyy")in cell B2, the result displays as 12/31/01. It should display as Dec. 01 2001. If we try the same formula from Excel 2000 under the Citrix neighborhood install on the same server, it works fine. Unfortunately, the custom application is a reporting tool that makes calls to Excel to generate graphs and charts for the report and requires the local version. Anyone got any ideas on how to address the format issue? Thanks in advance. -- Dave Peterson ec35720@msn.com |
| 386 | How are you applying the names and what do those names look like. You can have workbook level names and you can have worksheet level names. Worksheet level names start with the worksheet name then exclamation point then the range. 'my Sheet'!Test could refer to 'my sheet'!$a$1:$b$99 But since it starts with the worksheetname, it's not available on other sheets (unless you qualify it nicely). So I can have a workbook level name called Test, and worksheet level names of Test, too. == One more thing to check. Insert|Name|Define Do you see a worksheet name to the right of that box? If yes, then you've been using worksheet names. If not, then it must be something else! Troy Thiele wrote: I have been given a workbook with many formulas. I have been trying to name cells and apply those names to references in formulas on other sheets within the same workbook. Although it works fine if I am on the same sheet, I get a message indicating that Microsoft cannot find any references if I try to apply the names on different sheets. Ideas as to why this is occuring? Thanks, Troy -- Dave Peterson ec35720@msn.com |
| 389 | 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? |
| 406 | Eric, Protection is really intended to prevent changes to data, not hiding it. Also, I've tried pasting some cells with a hidden column into Word in both XL97 and XP, and the hidden column remained hidden, so I can't reproduce your situation. Say more about how you're doing this. Some things that come to mind are: Move the costs column out of the range of cells that you'll be copying/pasting (Select the column, hold Shift as you drag it by the edge laterally -- the formulas will adjust as needed), or move it into another sheet altogether (Cut the column, and paste it into another sheet). Are you wanting to prevent the costs from showing when others do the copy/pasting (that is, make sure that no one working with the sheet can reveal the costs), or are you wanting to simply get it pasted into Word yourself, without the costs showing in the Word document? Regards from Virginia Beach, EarlK ------------------------------------------------------------- "Eric" <eric.goodrich@paccoast.com wrote in message news:101c901c217de$f526bb50$b1e62ecf@tkmsftngxa04... I have a worksheet of product prices....in which I've hidden the column that contains my cost. I've protected the sheet with a password so the costs can't be revealed. However, if I copy my columns....including the hidden column....and paste into word.....presto...there is my hidden column with all my costs. Any way around this security glitch? |
| 407 | Ian, You want duplicates to be allowed in column A, but where they exist, don't allow an entry in M for a new (duplicate) A value, in the same sheet. M must remain empty in any duplicate of A. And other lists must be checked for these dups of col A? I suspect that this will be more straightforward if you combine the tables. Is that practical in your situation? It's often easier to keep data combined, then extract what you need. Your formula will work then. You can always use an additional field (column) to differentiate rows that came from different tables, if needed. Other operations may be more straightforward too, with these tables combined. Worth looking at. If that isn't doable, it may be necessary to use a macro to look for dups in the other sheets in other workbooks. Data-Validation doesn't let you refer to other sheets, as you've found out. Regards from Virginia Beach, EarlK ------------------------------------------------------------- "ian berger" <i_berger@yahoo.com wrote in message news:dc7301c217e1$ae4e4240$95e62ecf@tkmsftngxs02... I have a question about values common to 2 lists, and have read many interesting postings from a variety of searches (also read MVP Pearson’s suggestions). Regretfully, I have not located any similar issues. So if someone would help I would be greatly appreciative. If cell A200 = abc and M200 = 10, then any new entry in column A which is not unique, say “abc” has a restriction on the other list (cell in same row; column M). Example: if someone types in “abc” in cell A3, then M3 wouldn’t accept data input. I am counting the values in column M, and only one instance per unique value in column A is permitted. There are several shared workbooks and one unshared archive workbook which all need to be checked to prevent incorrect data. Maybe it should be described as preventing duplicates on values common to 2 lists spanning several workbooks. This works when I put in validation custom in the cells in column M, but doesn’t span all the workbooks. =COUNTIF ($A$1:$A$50,A1)=1 |
| 408 | Hello all, I hope someone can help me. My boss is all over me about figuring out how to create a cell in Excel that will tell me the total percent earned on a particular job. Simple example: $1500.00 in expenses $2500.00 in revenue = $1000.00 total profit (my question ???) What is the percent of profit on the job and how do I write a formula in a cell for this so she can use it in the future? My brain is fried and I can't figure out the formula. Any help would be great. Thanks, SkipperTW SkipperTW@yahoo.com |
| 410 | Pct Profit = Profit / Revenue Format as Percent. Regards, Fred. "SkipperTW" <SkipperTW@yahoo.com wrote in message news:aeu1pe$s6p$1@slb6.atl.mindspring.net... Hello all, I hope someone can help me. My boss is all over me about figuring out how to create a cell in Excel that will tell me the total percent earned on a particular job. Simple example: $1500.00 in expenses $2500.00 in revenue = $1000.00 total profit (my question ???) What is the percent of profit on the job and how do I write a formula in a cell for this so she can use it in the future? My brain is fried and I can't figure out the formula. Any help would be great. Thanks, SkipperTW SkipperTW@yahoo.com |
| 411 | 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? |
| 423 | Hi all, I have a very large (~25Mb) Excel spreadsheet that contains a bunch of data and formulas (duh :). The use of it is that a user needs to enter a limited number of parameters, and get a report based on the data and formulas in the sheet. This is a VB app, and talking to Excel via automation is a pain - it takes quite a while to load the spreadsheet. I'd prefer not to touch the spreadsheet itself, so I thought I'd be using ADO through OLE DB and xls driver to do the job. But I can't find the documentation on how to, say, get a value of a single cell? I realize that technically this approach works with sheets or ranges, but I can do something like select * from [sheet1$d1:d2] and get the value from cell d2. But I am puzzled as to how I can get at d1, for example? |
| 427 | ADO assumes that the Excel data is in the format of a database table. That is, the first row in the referenced range contains the field names that you can use to reference each column in the range. If you want to get ADO to return the information in the first row, use something like the following, where rs is the object variable referring to your recordset: MsgBox rs.Fields(0).Name -- John Green - Excel MVP Sydney Australia "Grisha Golberg" <junta@komkon.org wrote in message news:uh5gn9rnaesm49@corp.supernews.com... Hi all, I have a very large (~25Mb) Excel spreadsheet that contains a bunch of data and formulas (duh :). The use of it is that a user needs to enter a limited number of parameters, and get a report based on the data and formulas in the sheet. This is a VB app, and talking to Excel via automation is a pain - it takes quite a while to load the spreadsheet. I'd prefer not to touch the spreadsheet itself, so I thought I'd be using ADO through OLE DB and xls driver to do the job. But I can't find the documentation on how to, say, get a value of a single cell? I realize that technically this approach works with sheets or ranges, but I can do something like select * from [sheet1$d1:d2] and get the value from cell d2. But I am puzzled as to how I can get at d1, for example? |
| 428 | By the way, this will only work if the top row contains text that forms a valid field name. It will not access numeric data in the first row, -- John Green - Excel MVP Sydney Australia "Grisha Golberg" <junta@komkon.org wrote in message news:uh5gn9rnaesm49@corp.supernews.com... Hi all, I have a very large (~25Mb) Excel spreadsheet that contains a bunch of data and formulas (duh :). The use of it is that a user needs to enter a limited number of parameters, and get a report based on the data and formulas in the sheet. This is a VB app, and talking to Excel via automation is a pain - it takes quite a while to load the spreadsheet. I'd prefer not to touch the spreadsheet itself, so I thought I'd be using ADO through OLE DB and xls driver to do the job. But I can't find the documentation on how to, say, get a value of a single cell? I realize that technically this approach works with sheets or ranges, but I can do something like select * from [sheet1$d1:d2] and get the value from cell d2. But I am puzzled as to how I can get at d1, for example? |
| 431 | Jenny, Not sure about your second question, but in regard to the first question, look in Help under Specifications for questions like this. For Excel 97 and 2000 you should find a 32,000 character limit for each cell: Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar. HTH, Brian Jenny wrote: Are there any restrictions on the amount of data you can put into one cell in a worksheet? If so are there any ways to increase the amount of data you can insert? Tried all the usual ways but no luck! Also when converting a word table to a spreadsheet is there a global way to stop excel converting certain figures into dates or will we have to amend every affected cell? Grateful for any enlightenment! Jenny |
| 436 | A B C D M N O 1 8/1/02 9/1/02 10/1/02 2 Account 1 3 Close Month 7/1/2002 4 License Fee $20,000 5 Annual Maint $24,000 2,000 2,000 2,000 6 Installation $23,000 Formula in Row 5, column E and beyond =IF (M$1$D3,$D5/12,"") In the sample shown above, the value of $2,000 is placed in cells M5, N5, etc. since the date in M1 is greater than the date in D3. If the date in D3 is changed to 8/1/02, the 2,000 entries are shifted right one month. This is exactly what I want but if no date is entered in D3, I want the nothing or zero entered in row 5 but the formula shown above doesn't give me that result. (It puts 2,000) in every cell in Row 5) I suspect that I need nested IF statements using the ISBLANK function but I'm not having any success with this. For what it's worth, this is a cash forecasting worksheet that lists all pending sales and their forecasted close dates and revenue projections. Changing the Close Date helps us construct different cash positions. Entering no date in the Close Month cell would eliminate that particular account from the forecast. Any help would be appreciated. Scott C. |
| 438 | Hi Scott try: =IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"") -- "Scott Cardais" <Scott@Cardais.com wrote in message news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10... | A B C D M N O | 1 8/1/02 9/1/02 10/1/02 | 2 Account 1 | 3 Close Month 7/1/2002 | 4 License Fee $20,000 | 5 Annual Maint $24,000 2,000 2,000 2,000 | 6 Installation $23,000 | | Formula in Row 5, column E and beyond =IF | (M$1$D3,$D5/12,"") | | In the sample shown above, the value of $2,000 is placed | in cells M5, N5, etc. since the date in M1 is greater | than the date in D3. If the date in D3 is changed to | 8/1/02, the 2,000 entries are shifted right one month. | | This is exactly what I want but if no date is entered in | D3, I want the nothing or zero entered in row 5 but the | formula shown above doesn't give me that result. (It puts | 2,000) in every cell in Row 5) | | I suspect that I need nested IF statements using the | ISBLANK function but I'm not having any success with this. | | For what it's worth, this is a cash forecasting worksheet | that lists all pending sales and their forecasted close | dates and revenue projections. Changing the Close Date | helps us construct different cash positions. Entering no | date in the Close Month cell would eliminate that | particular account from the forecast. | | Any help would be appreciated. | | Scott C. |
| 439 | Hi Scott One method would be =IF($D3="","",IF(M$1$D3,$D5/12,"")) then if D3 is empty, the formula returns nothing "". You may decide you want to have 0, rather than "" as the result. -- Roger Govier Technology 4 U W98SR2 XL2K On Fri, 21 Jun 2002 02:07:22 -0700, "Scott Cardais" <Scott@Cardais.com wrote: A B C D M N O 1 8/1/02 9/1/02 10/1/02 2 Account 1 3 Close Month 7/1/2002 4 License Fee $20,000 5 Annual Maint $24,000 2,000 2,000 2,000 6 Installation $23,000 Formula in Row 5, column E and beyond =IF (M$1$D3,$D5/12,"") In the sample shown above, the value of $2,000 is placed in cells M5, N5, etc. since the date in M1 is greater than the date in D3. If the date in D3 is changed to 8/1/02, the 2,000 entries are shifted right one month. This is exactly what I want but if no date is entered in D3, I want the nothing or zero entered in row 5 but the formula shown above doesn't give me that result. (It puts 2,000) in every cell in Row 5) I suspect that I need nested IF statements using the ISBLANK function but I'm not having any success with this. For what it's worth, this is a cash forecasting worksheet that lists all pending sales and their forecasted close dates and revenue projections. Changing the Close Date helps us construct different cash positions. Entering no date in the Close Month cell would eliminate that particular account from the forecast. Any help would be appreciated. Scott C. |
| 440 | Hi Dave: I pasted this into my June 02 columns and beyond and it resulted in entering 2,000 in June even if the close date was August. Just to be clear, if the close date is August and the annual amount is 24,000; I want 2,000 entered in September and beyond AND if the close date is blank, I want nothing entered in any months. Thanks very much for your help. -----Original Message----- Hi Scott try: =IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"") -- "Scott Cardais" <Scott@Cardais.com wrote in message news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10... | A B C D M N O | 1 8/1/02 9/1/02 10/1/02 | 2 Account 1 | 3 Close Month 7/1/2002 | 4 License Fee $20,000 | 5 Annual Maint $24,000 2,000 2,000 2,000 | 6 Installation $23,000 | | Formula in Row 5, column E and beyond =IF | (M$1$D3,$D5/12,"") | | In the sample shown above, the value of $2,000 is placed | in cells M5, N5, etc. since the date in M1 is greater | than the date in D3. If the date in D3 is changed to | 8/1/02, the 2,000 entries are shifted right one month. | | This is exactly what I want but if no date is entered in | D3, I want the nothing or zero entered in row 5 but the | formula shown above doesn't give me that result. (It puts | 2,000) in every cell in Row 5) | | I suspect that I need nested IF statements using the | ISBLANK function but I'm not having any success with this. | | For what it's worth, this is a cash forecasting worksheet | that lists all pending sales and their forecasted close | dates and revenue projections. Changing the Close Date | helps us construct different cash positions. Entering no | date in the Close Month cell would eliminate that | particular account from the forecast. | | Any help would be appreciated. | | Scott C. . |
| 441 | Dave: My mistake. Your formula DOES work. I had to make one small change to reference the proper cell in my spreadsheet. Thanks very much. Scott C. -----Original Message----- Hi Scott try: =IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"") -- "Scott Cardais" <Scott@Cardais.com wrote in message news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10... | A B C D M N O | 1 8/1/02 9/1/02 10/1/02 | 2 Account 1 | 3 Close Month 7/1/2002 | 4 License Fee $20,000 | 5 Annual Maint $24,000 2,000 2,000 2,000 | 6 Installation $23,000 | | Formula in Row 5, column E and beyond =IF | (M$1$D3,$D5/12,"") | | In the sample shown above, the value of $2,000 is placed | in cells M5, N5, etc. since the date in M1 is greater | than the date in D3. If the date in D3 is changed to | 8/1/02, the 2,000 entries are shifted right one month. | | This is exactly what I want but if no date is entered in | D3, I want the nothing or zero entered in row 5 but the | formula shown above doesn't give me that result. (It puts | 2,000) in every cell in Row 5) | | I suspect that I need nested IF statements using the | ISBLANK function but I'm not having any success with this. | | For what it's worth, this is a cash forecasting worksheet | that lists all pending sales and their forecasted close | dates and revenue projections. Changing the Close Date | helps us construct different cash positions. Entering no | date in the Close Month cell would eliminate that | particular account from the forecast. | | Any help would be appreciated. | | Scott C. . |
| 442 | Roger: Thanks. This works, too. Different approach from Dave but it works just as well. Thanks very much. Scott C. -----Original Message----- Hi Scott One method would be =IF($D3="","",IF(M$1$D3,$D5/12,"")) then if D3 is empty, the formula returns nothing "". You may decide you want to have 0, rather than "" as the result. -- Roger Govier Technology 4 U W98SR2 XL2K On Fri, 21 Jun 2002 02:07:22 -0700, "Scott Cardais" <Scott@Cardais.com wrote: A B C D M N O 1 8/1/02 9/1/02 10/1/02 2 Account 1 3 Close Month 7/1/2002 4 License Fee $20,000 5 Annual Maint $24,000 2,000 2,000 2,000 6 Installation $23,000 Formula in Row 5, column E and beyond =IF (M$1$D3,$D5/12,"") In the sample shown above, the value of $2,000 is placed in cells M5, N5, etc. since the date in M1 is greater than the date in D3. If the date in D3 is changed to 8/1/02, the 2,000 entries are shifted right one month. This is exactly what I want but if no date is entered in D3, I want the nothing or zero entered in row 5 but the formula shown above doesn't give me that result. (It puts 2,000) in every cell in Row 5) I suspect that I need nested IF statements using the ISBLANK function but I'm not having any success with this. For what it's worth, this is a cash forecasting worksheet that lists all pending sales and their forecasted close dates and revenue projections. Changing the Close Date helps us construct different cash positions. Entering no date in the Close Month cell would eliminate that particular account from the forecast. Any help would be appreciated. Scott C. . |
| 443 | I have posted a question relating this query titled "Printing date in Excel 97" (was on pg15 on 21/06). I was given a visual basic solution but I was wondering if it could be done using formulas. The VB way has been temperamental and does not work now. I want to be able to print an non-updateable date into a cell when data is enterred into another cell. E.g. data is entered into A3, then a date automatically appears in J3. I want to be able to do this are multiple rows. Can anyone help. Thanks in advance. |
| 452 | Hi, AFAIK both functions return predicted value(s) based on doing lineair regression on x and y data. If I use this dataset: x y 1 7 2 6 3 5 4 4 5 3 6 3 I get these results: x trend fcast 4 4.238095238 4.238095238 As far as I can see, the only difference between them is that in the Trend function one can specify whether or not the constant of the fit line should be set to zero and that TREND can be used as an array formula. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- I have created a table for sales history for the past 5 years eg 96,97,98,99,2000. with appropriate dollar figures. When I "Trend" for 2001 I get X value When I "Forecast" for 2001 I get Y value. (a difference of around 10,000,000.) What is the difference between the two and why should I use one over the other. Clarification of the two would be much appreciated. cheers. . |
| 453 | Dan Here's one way to do it: =MAX(IF(NOT(ISERROR(C2:E2)),C2:E2)) The formula is an array formula and must be entered with <Shift<Ctrl<Enter instead of <Enter also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in curly brackets { }. Don't enter these brackets yourself. -- Best regards Leo Heuser MVP Excel "drwinterton" <daniel_winterton@hk.ml.com skrev i en meddelelse news:112fe01c21910$d482aca0$3bef2ecf@TKMSFTNGXA10... I am doing some financial calculations. I have excel linked to bloomberg, a financial database. Bloomberg populates cells C2, D2, and E2 with the 3-month, 6-month, and 1-year volatility of a stock. In cell F, I have: =max(c2,d2,e2) However, if a certain stock hasn't been around for long enough, then bloomberg spits a "Value#" in the corresponding cell (for example, a stock has been around 9 months. It will have a 3- and 6-month volatility, but no 1-year volatility) Apparently, the MAX function does not work if one of the cells is "value#". Any suggestions? Thanks, Dan Winterton |
| 459 | Thank you all! Works like a champ! -M -----Original Message----- =""""&D1&"""" works That is four quotes. --Brian Taylor Refinate,(C) 2001 www.adetaylor.com "MattS" <mattschillerberg@106group.com wrote in message news:11bf701c21898$800ac400$35ef2ecf@TKMSFTNGXA11... I'm sure this is a fairly easy question...but I don't use excel much and could use the help. I need to add quotation marks to a large column of numbers. ie. "1" "2" "3" etc. Is there a simple formula that I can use? If column D has the numbers can I make column 5 = """&D1&""" or something like that. When I do this I get "&D1&"....however, when I use the formula "'"&D1&"'" then I get '1' which is close...but I need the double quotes. Any help would be very appreciated. Thanks! . |
| 466 | Hi Dave I guess I wasn't as clear as I could have been. The issue is on the smae computer - there are two instances of Excel on it, one loaded directly on the computer and the other loaded under Citrix on the same computer. I did check the international settings, but wasn't sure how this would effect one and not the other instance of Excel. Anyone else have any ideas? -----Original Message----- I think that excel gets the format for dates from a Windows setting. It sounds like one pc is different than the other and not recognizing the date. I'm in the US and if I type 12/31/2001, I get a date (expected). But if I type 31/12/2001, I get the text 31/12/2001--not really a date. On the "bad" pc, if you format the cell as General (Format|Cells|Number Tab), do you get a number? (I get 37621 for 12/31/2001 (using the 1900 date system). If you don't get a number, then that cell really doesn't have a date in it (no matter what it looks like). And since the value isn't numeric (it's a string), the =text() has no affect on it. If this looks like the case, then close excel and try: Start|Settings|Control Panel|Regional Settings Applet|Date Tab. Check (change to) the format. Start Excel. I'm not sure if it'll react to the change or if you'll have to change something for it to notice. Hit F2 and then enter on one of the cells. Did it fix your problem? If yes, maybe you can convert a whole column of dates at once. Select your column and do a Data|Text to columns. Follow the wizard and make sure you choose the correct (mdy?) format on step 3 of the wizard. Good luck, James Goodchild wrote: Hi all We have a Windows 2000 server running terminal services and Citrix. Because of a requirement from a custom built program, MS Office 2000 was installed to the local hard drive of the Citrix server as well as to the Citrix neighborhood. Everything loads fine and other than one formatting issue, everything seems to work. Here is the problem, in Excel 2000 in the local copy if you type the following date 12/31/01 in cell A1 and then enter the formula =TEXT(A1,"Mmm. Dd yyyy")in cell B2, the result displays as 12/31/01. It should display as Dec. 01 2001. If we try the same formula from Excel 2000 under the Citrix neighborhood install on the same server, it works fine. Unfortunately, the custom application is a reporting tool that makes calls to Excel to generate graphs and charts for the report and requires the local version. Anyone got any ideas on how to address the format issue? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 473 | Hi I have this formula: =IF($E160,VLOOKUP($E16,Rates,5)+($H16*VLOOKUP ($E16,Rates,6))+($I16*VLOOKUP($E16,Rates,7)),"") which works perfectly. Now the client says that the maximum value of "$I16" is 4 even if a greater amount has been entered, but the amount enterd must stay the same. ie. if a user enters "7" in $I16 then the value must be "4" in the above formula but must remain "7" in the actual cell. Any help would be much appreciated. thanks |
| 476 | Change $I16 to 4 in your formula. HTH Jason Atlanta, GA -----Original Message----- Hi I have this formula: =IF($E160,VLOOKUP($E16,Rates,5)+($H16*VLOOKUP ($E16,Rates,6))+($I16*VLOOKUP($E16,Rates,7)),"") which works perfectly. Now the client says that the maximum value of "$I16" is 4 even if a greater amount has been entered, but the amount enterd must stay the same. ie. if a user enters "7" in $I16 then the value must be "4" in the above formula but must remain "7" in the actual cell. Any help would be much appreciated. thanks . |
| 477 | Not having the rate tables, etc. cannot test this, but it may be worth a try =IF($E160,VLOOKUP($E16,Rates,5)+($H16*VLOOKUP ($E16,Rates,6))+(MAX($I16,4)*VLOOKUP($E16,Rates,7)),"") HTH? "Bruce" <bruce@nha.co.za wrote in message news:1120c01c2192c$d2a80f80$39ef2ecf@TKMSFTNGXA08... Hi I have this formula: =IF($E160,VLOOKUP($E16,Rates,5)+($H16*VLOOKUP ($E16,Rates,6))+($I16*VLOOKUP($E16,Rates,7)),"") which works perfectly. Now the client says that the maximum value of "$I16" is 4 even if a greater amount has been entered, but the amount enterd must stay the same. ie. if a user enters "7" in $I16 then the value must be "4" in the above formula but must remain "7" in the actual cell. Any help would be much appreciated. thanks |
| 478 | Bruce, you can replace I16 in your formula by MAX(MIN($I$16,4)), i.e. =IF($E160,VLOOKUP($E16,Rates,5)+($H16*VLOOKUP($E16,Rates,6))+(MAX(MIN($I16,4))*VLOOKUP($E16,Rates,7)),"") -- Regards, Peo Sjoblom "Bruce" <bruce@nha.co.za wrote in message news:1120c01c2192c$d2a80f80$39ef2ecf@TKMSFTNGXA08... Hi I have this formula: =IF($E160,VLOOKUP($E16,Rates,5)+($H16*VLOOKUP ($E16,Rates,6))+($I16*VLOOKUP($E16,Rates,7)),"") which works perfectly. Now the client says that the maximum value of "$I16" is 4 even if a greater amount has been entered, but the amount enterd must stay the same. ie. if a user enters "7" in $I16 then the value must be "4" in the above formula but must remain "7" in the actual cell. Any help would be much appreciated. thanks |
| 479 | Try changing the $I16 to MIN(4,$I16). John Hill "Bruce" <bruce@nha.co.za wrote in message news:1120c01c2192c$d2a80f80$39ef2ecf@TKMSFTNGXA08... Hi I have this formula: =IF($E160,VLOOKUP($E16,Rates,5)+($H16*VLOOKUP ($E16,Rates,6))+($I16*VLOOKUP($E16,Rates,7)),"") which works perfectly. Now the client says that the maximum value of "$I16" is 4 even if a greater amount has been entered, but the amount enterd must stay the same. ie. if a user enters "7" in $I16 then the value must be "4" in the above formula but must remain "7" in the actual cell. Any help would be much appreciated. thanks |
| 481 | Using XL 97 on Win 95. I inherited a simple workbook (no macros to worry about). 1 page with raw data, another page with graphs linked to the raw data (for an "official" looking report). A few simple formulas, but nothing complex in the workbook. I haven't touched any of that- I want to make an easier data entry page, so I figured I'd build the interface, then copy the data over. The I can change the references from the "report" page to my new raw data page. So, I created a new sheet & renamed 'KRef' to hold some lists that I want in comboboxes related to data entry. Then I created a new sheet & renamed 'KEntry' and added 3 comboboxes. When I enter linked ranges, most of them work fine: KRef!A1:A4 KRef!D1:D12 _but_ if I press the '8' key above the alpha keys (inside the linked range property box), it crashes XL. I can replicate this either as part of correct syntax: KRef!A1:A8 <crash or incorrect syntax KRef!8<crash It does not wait for a further event- it crashes the moment I press '8' (e.g. it doesn't wait for me to leave the linked range property) The message I get is: [Excel] "This program has performed an illegal operation and will be shut down...if the problem persists, contact the program vendor" [Details] - EXCEL cause an invalid page fault in Module VBE.DLL as 0137:6500e1d0 (etc) My first thought was that I had a sticky alt or shift or control key, so I switched keyboards- but it still happens, even with a different keyboard. I tried using the keypad, and that 8 works ok (e.g. does not crash XL). I always thought that the same signal was sent to the CPU from the two "8" keys, but maybe not? Either way, it shouldn't be crashing- anyone have any ideas? Thanks in advance, Keith |
| 482 | On sheet 4 I have a checkbook and on sheet 5 I have a report , which is printed each week. So I have 52 weeks of checkbooks and 52 reports. the checkbook repeats every 47 rows and the report repeats every 64 rows. I am using the indirect on some cells which works fine to return 1 value, from 1 cell on sheet 4, however I need to do a sum array also for some of the values. I am using =indirect("sheet4!C&((ROW(H49)+57)64)*47-38) This gets me to the cell where I want to start the Sum {=SUM(SHEET4!C7:C45=SHEET5!G22)*(SHEET4!G7:G45))} I have tried everything and cannot get them to nest and work properly The reason for nesting them is so I can copy and paste each of the 52 weeks, rather than enter the formula's in each cell for every week , I would probadly make a lot of mistakes. Thanks for any help Roger |
| 491 | Hi Roger, Click on the cell with the formula. In the formula box, with the mouse, you can select parts of your formula, press [F9] and Excel will calculate just the portion of the formula selected. WARNING! use the Esc key to finish or the selection will be replaced by the value. You must select a "full function" to get a result. Be off by 1 "(" and it will error out. This allows you to debug parts of a formula. Why go through this? The indirect function is looking for a cell address in different cell. Your formula looks like is returning a value higher than the number of rows. I got 318810 (c318810 doesn't exists) John "Roger" <rlm@donet.com wrote in message news:f46c01c2192f$56ee4b90$2ae2c90a@hosting.microsoft.com... On sheet 4 I have a checkbook and on sheet 5 I have a report , which is printed each week. So I have 52 weeks of checkbooks and 52 reports. the checkbook repeats every 47 rows and the report repeats every 64 rows. I am using the indirect on some cells which works fine to return 1 value, from 1 cell on sheet 4, however I need to do a sum array also for some of the values. I am using =indirect("sheet4!C&((ROW(H49)+57)64)*47-38) This gets me to the cell where I want to start the Sum {=SUM(SHEET4!C7:C45=SHEET5!G22)*(SHEET4!G7:G45))} I have tried everything and cannot get them to nest and work properly The reason for nesting them is so I can copy and paste each of the 52 weeks, rather than enter the formula's in each cell for every week , I would probadly make a lot of mistakes. Thanks for any help Roger |
| 494 | hello-i've hit a wall G27: 5:00 PM G28: 2:00 AM G29: 1/0/1900 15:00 this is what i get when i subtract G27 from G28 using the formula =G27-G28+IF(G28G27,1) any ideas? |
| 501 | Yes there was, and the -3 at the end of this formula accounts for that =LEFT(A2,LEN(A2)-3). Had there not been a space & I wanted to get rid of the last two characters, I would put -2 at the end rather than -3. Regards, Patti "Kntwt2rtr" <kntwt2rtr@aol.com wrote in message news:20020620210532.04676.00001292@mb-ff.aol.com... Just for clarification . . . is there a space before the text? |
| 503 | Taking EarlK's lead, here is a way to get the quotes into the same cell without using equations or extra cells. Enter in FormatCellsNumberCustom: \"#\" Apply that format to any selection. --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 "EarlK" <earlk@livenet.net wrote in message news:uhpeQCbGCHA.2520@tkmsftngp13... Matt, If you'd like to see the quote marks around your original numbers, and not use the extra (E) column, you can do it with number formatting. This is going to be tacky, as we have to use two apostrophes (which look pretty much like a quote mark anyway if you have a proportionally spaced font). The Custom number format does not seem to allow including a quote mark as text. :) Select the column, Format-Cells-Number-Custom. Type: "''" # "''" That's a quote mark ("), two apostrophes ('), a quote mark, etc. With a proportionally spaced, font, it looks as it should. This approach, if you need it, will also allow you to use the numbers as numbers, not text, in case you need to sum them, etc, and have your quote marks too. You won't need the extra column. Regards from Virginia Beach, EarlK ------------------------------------------------------ ------- "MattS" <mattschillerberg@106group.com wrote in message news:11bf701c21898$800ac400$35ef2ecf@TKMSFTNGXA11... I'm sure this is a fairly easy question...but I don't use excel much and could use the help. I need to add quotation marks to a large column of numbers. ie. "1" "2" "3" etc. Is there a simple formula that I can use? If column D has the numbers can I make column 5 = """&D1&""" or something like that. When I do this I get "&D1&"....however, when I use the formula "'"&D1&"'" then I get '1' which is close...but I need the double quotes. Any help would be very appreciated. Thanks! |
| 511 | Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . |
| 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. |
| 527 | Here is one approach. This will not re-protect the sheets. You will have to use the code from the previous response for that. Sub Get_Password() On Error GoTo errPassword Dim strPassword As String Dim i As Integer '*** GET THE PASSWORD FROM USER strPassword = InputBox("Enter Password", "Unprotection Password") '*** TRY TO UNPROTECT THE FIRST SHEET Sheets(3).Unprotect Password:=strPassword '*** IF NO ERROR THEN UNPROTECT THE REST OF THE SHEETS For i = 4 To 50 Sheets(i).Unprotect Password:=strPassword Next i Exit Sub errPassword: '*** DISPLAY A MESSAGE IF PASSWORD INCORRECT AND EXIT MsgBox "You entered and incorrect password!" End Sub -----Original Message----- Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . . |
| 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. |
| 535 | ade, Backslash. Sheesh. I'd tried that, and it didn't work, but I was using a forward slash. And it's in the help, too. Matt, go with ade's solution. :) Regards from Virginia Beach, EarlK ------------------------------------------------------------- "adetaylor" <ngbtaylor@adetaylor.com wrote in message news:MW_Q8.14435$Fv1.1146092@newsread2.prod.itd.earthlink.net... Taking EarlK's lead, here is a way to get the quotes into the same cell without using equations or extra cells. Enter in FormatCellsNumberCustom: \"#\" Apply that format to any selection. --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 "EarlK" <earlk@livenet.net wrote in message news:uhpeQCbGCHA.2520@tkmsftngp13... Matt, If you'd like to see the quote marks around your original numbers, and not use the extra (E) column, you can do it with number formatting. This is going to be tacky, as we have to use two apostrophes (which look pretty much like a quote mark anyway if you have a proportionally spaced font). The Custom number format does not seem to allow including a quote mark as text. :) Select the column, Format-Cells-Number-Custom. Type: "''" # "''" That's a quote mark ("), two apostrophes ('), a quote mark, etc. With a proportionally spaced, font, it looks as it should. This approach, if you need it, will also allow you to use the numbers as numbers, not text, in case you need to sum them, etc, and have your quote marks too. You won't need the extra column. Regards from Virginia Beach, EarlK ------------------------------------------------------ ------- "MattS" <mattschillerberg@106group.com wrote in message news:11bf701c21898$800ac400$35ef2ecf@TKMSFTNGXA11... I'm sure this is a fairly easy question...but I don't use excel much and could use the help. I need to add quotation marks to a large column of numbers. ie. "1" "2" "3" etc. Is there a simple formula that I can use? If column D has the numbers can I make column 5 = """&D1&""" or something like that. When I do this I get "&D1&"....however, when I use the formula "'"&D1&"'" then I get '1' which is close...but I need the double quotes. Any help would be very appreciated. Thanks! |
| 553 | Phil - 1/ You could look up Data Type Summary in VBA help, which leads to a description of the various data types. 2/ You could set up a moving average in your worksheet. Say you have values in B2:B100, and you want a moving average of six consecutive numbers. In C7, enter this formula =average(B2:B7) And drag this down to C100. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <1075a01c21858$c6327760$9be62ecf@tkmsftngxa03, Phil said... Hi, I'm using excel to plot some experimentally determined data, however the resulting chart has glitches due to rounding errors. To what accuracy does excel store numbers? And, is there a smoothing type of function for plotted data, whereby say, 6 consecutive numbers, for example, are averaged to give a smoother curve? Thanks Phil |
| 555 | Jason I expect your problem lies with how you are writing your criteria. If the column you want to filter by is, say, G, and your column headings are in row 1 and your data starts in row 2, then your criteria should be written as "=G291234" without the quotes. When you filter by numbers, your criteria range has your column headings as the first row and your criteria in the subsequent rows. But when you use a formula in your criteria, then your criteria range has to consist of a blank row as your first row and one or more rows of criteria as the subsequent rows. Note that the column headings row is not used in your criteria range if your criteria has formulas. I know this can be confusing. Get back to me if you need further help with this. HTH Otto "Jason" <jaybeck99@hotmail.com wrote in message news:#Pylw0iGCHA.2400@tkmsftngp09... I am attempting to filter some data using the advanced filter. I can filter by numbers (i.e. 91234) but if I want to use the advanced filter to filter something 91234 it does not give me any results. it works the same for the Custom filter. Any ideas? Jason |
| 564 | I was wondering if anyone out there knows how to create user-defined formulas in Excel. Thanks, Pete |
| 566 | Hi Pete: You need to write your formula as a function and put it in a standard module. If you want the function to be available to all workbooks, you would generally put it in your Personal Macro workbook. An example: Function DoubleSquared(iInt As Long) As Long DoubleSquared = (2 * iInt) ^ 2 End Function Now, if you put the number 3 in cell A1, and put the formula: =DoubleSquared(A1) in another cell, the result will be the square of twice the number in A1; that is, 36. -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Pete" <derounpr@hotmail.com wrote in message news:f98701c21a42$2611a710$2ae2c90a@hosting.microsoft.com... I was wondering if anyone out there knows how to create user-defined formulas in Excel. Thanks, Pete |
| 569 | =SUM(K1+K2+K3+K4) I have a formula that adds these numbers 54.5 55.75 49 57.5 The correct answer should be 216.75, but it keeps saying 216.8.... Is it rounding up?? Why won't it say 216.75 ? Thanks Chuck |
| 570 | Hi Chuck: You probably need to format the cell for the right number of decimals. Look at <Format <Cells on the menu. -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Chuck" <clm701@hotmail.com wrote in message news:uha917odp29k92@corp.supernews.com... =SUM(K1+K2+K3+K4) I have a formula that adds these numbers 54.5 55.75 49 57.5 The correct answer should be 216.75, but it keeps saying 216.8.... Is it rounding up?? Why won't it say 216.75 ? Thanks Chuck |
| 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 |
| 579 | John - Jason's suggestion might have worked, except it seems that the charting engine in Excel isn't as good with named ranges as Excel's worksheet engine. It won't work with names that use INDIRECT and some other select functions within their RefersTo formulas. This is a known problem, or at least I've often had the problem and have discussed it on these newsgroups. You could set up a slightly different approach: put the column in B10, the starting row in B11, and the ending row in B12. Define your name "catx" as =OFFSET(Sheet2!$A$1,Sheet2!$B$11-1,Sheet2!$B$10-1,Sheet2!$B$12-Sheet2!$B$11+1 ,1) That's all in one line, watch the dreaded newsreader word wrap. Fortunately, the charting mechanism accepts defined formulas that use OFFSET. In some cases, you can use Index in the Define Names dialog, use a range on a worksheet to store the values of this defined range in, then plot the worksheet range in the chart. This way, the chart doesn't choke on the INDIRECT function. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <1108e01c2196e$77a600a0$9be62ecf@tkmsftngxa03, John Vera said... Hi. Try as I might, I can't make it work. I changed the sheet and file names, but still it says there's an error when I type in the range name (=Mybook.xls!catx in your example) thanks for your effort! John -----Original Message----- Try defining a name that uses INDIRECT. For example, for the x values, go to Insert Name Define and in the "Refers to:" box put: =INDIRECT("Sheet2!"&Sheet2!$B$11&":"&Sheet2!$B$12) Call it something like catx and hit OK. Go into the Source Data Series box of your chart and at the bottom in the "Category (X) axis labels" put: =Mybook.xls!catx Changes the file name and worksheets names to match your setup. Apply the same logic above when for the Values in each of your series. HTH Jason Atlanta, GA -----Original Message----- Hi again. WHat happens is that I have cells with cell names on them as a string ( B11 says "$F$12", without quotes; B12 says "$F$16", without quotes). I want to use the "indirect" command to establish $F$12 and $F$16 through B11 and B12 as the x range for my xy graphs, and other similar cells for the y range. Problem is, I can't use indirect in the x, y ranges because it says it is not a valid function. Any Ideas? Thanks, JOhn . . |
| 582 | Hi Dave - I just responded to an earlier post by John. The problem with charting from a named range, is that while it seems as though it should work, and while the same named range works in a worksheet, Excel's charting engine chokes on named ranges that use INDIRECT in their RefersTo formula. A few other functions also cause problems. I suggested a slightly different approach using OFFSET, which works fine with charts. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... Hi John Try using a Named range to refer indirectly to the cells storing the addresses (InsertNameDefine), should work. -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "John Vera" <emperorj@hotmail.com wrote in message news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | 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 |
| 583 | Thanks Jon, didn't know that. I assumed that as a chart can read a dynamic range, it would accept other named ranges that uses the INDIRECT. -- "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:#U$mtjmGCHA.2452@tkmsftngp08... | Hi Dave - | | I just responded to an earlier post by John. The problem with charting from | a named range, is that while it seems as though it should work, and while the | same named range works in a worksheet, Excel's charting engine chokes on | named ranges that use INDIRECT in their RefersTo formula. A few other | functions also cause problems. | | I suggested a slightly different approach using OFFSET, which works fine with | charts. | | - Jon | ------- | Jon Peltier, Microsoft Excel MVP | /jonpeltier/Excel/index.html | _______ | | In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... | | Hi John | | | Try using a Named range to refer indirectly to the cells storing the | addresses (InsertNameDefine), should work. | | | -- | **Possible solution has NOT been tested** | | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | | "John Vera" <emperorj@hotmail.com wrote in message | news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | | | | 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 | | | |
| 584 | Hi, I am trying to convert this macro that it works on the column selected instead of column A:A. I just like to highlight the column in question and then run the macro. Your help is very much appreciated Andonny Sub ConvertTextToNumber() Dim Cell As Range, Rng1 As Range Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) For Each Cell In Rng1 If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then Cell = CDbl(Cell) End If Next Cell End Sub |
| 585 | Hi Andonny If you are converting numeric text constants to numbers then a Loop is a pretty slow method, as loops are generally slow. Try the code below: Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Range("IV65536").Copy Rng1.PasteSpecial , xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub Just select the entire Column and run the code. It assumes that cell IV65536 is empty. -- "Andonny" <wje@multiline.com.au wrote in message news:#5hmxioGCHA.2324@tkmsftngp09... | Hi, | I am trying to convert this macro that it works on the column selected | instead of column A:A. | I just like to highlight the column in question and then run the macro. | | Your help is very much appreciated | Andonny | | Sub ConvertTextToNumber() | Dim Cell As Range, Rng1 As Range | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | For Each Cell In Rng1 | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | Cell = CDbl(Cell) | End If | Next Cell | End Sub | | |
| 590 | Hi Dave, Thanks a million for your reply. This is great. Even though the code I mentioned is slow it also does something which I need quite often. It places the minus from data extracted from JDEdwards to the front. Like 22- to -22 which is a good thing in my case. So if I could get the previous code to work with only selected columns would help me a great deal. Then I could use both in various situations. Thanks Andonny <DavidH@OzGrid.com wrote in message news:W5fR8.16$TG5.28441@vicpull1.telstra.net... Hi Andonny If you are converting numeric text constants to numbers then a Loop is a pretty slow method, as loops are generally slow. Try the code below: Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Range("IV65536").Copy Rng1.PasteSpecial , xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub Just select the entire Column and run the code. It assumes that cell IV65536 is empty. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Andonny" <wje@multiline.com.au wrote in message news:#5hmxioGCHA.2324@tkmsftngp09... | Hi, | I am trying to convert this macro that it works on the column selected | instead of column A:A. | I just like to highlight the column in question and then run the macro. | | Your help is very much appreciated | Andonny | | Sub ConvertTextToNumber() | Dim Cell As Range, Rng1 As Range | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | For Each Cell In Rng1 | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | Cell = CDbl(Cell) | End If | Next Cell | End Sub | | |
| 592 | Hi Andonny I have modified my code to reverse any imported numbers like: 55- I have also modified your original code to work on only the selection. Sub ConvertTextToNumber() Dim Cell As Range, Rng1 As Range Set Rng1 = Intersect(ActiveSheet.UsedRange, Selection) For Each Cell In Rng1 If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then Cell = CDbl(Cell) End If Next Cell End Sub ========================================= Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Rng1.EntireColumn.Insert Rng1.Offset(0, -1).FormulaR1C1 = _ "=IF(RIGHT(RC[1])=""-"",SUBSTITUTE(RC[1],""-"","""")" _ & "*-1,RC[1]*1)" Rng1.Offset(0, -1) = Rng1.Offset(0, -1).Value 'OPTIONAL Rng1.EntireColumn.Delete Application.CutCopyMode = False End Sub I think you will find the second more effcient as it works on the range as a whole and not one cell at a time. -- "Andonny" <wje@multiline.com.au wrote in message news:OquvRTpGCHA.2808@tkmsftngp10... | Hi Dave, | Thanks a million for your reply. This is great. Even though the code I | mentioned is slow it also does something which I need quite often. It places | the minus from data extracted from JDEdwards to the front. Like 22- to -22 | which is a good thing in my case. So if I could get the previous code to | work with only selected columns would help me a great deal. Then I could use | both in various situations. | | Thanks | Andonny | | | <DavidH@OzGrid.com wrote in message | news:W5fR8.16$TG5.28441@vicpull1.telstra.net... | Hi Andonny | | If you are converting numeric text constants to numbers then a Loop is a | pretty slow method, as loops are generally slow. Try the code below: | | Sub ConvertTextToNumberFaster() | Dim rCell As Range, Rng1 As Range | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | If Rng1 Is Nothing Then | MsgBox "No constants" | Exit Sub | End If | | Range("IV65536").Copy | Rng1.PasteSpecial , xlPasteSpecialOperationAdd | Application.CutCopyMode = False | End Sub | | | Just select the entire Column and run the code. It assumes that cell | IV65536 | is empty. | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | "Andonny" <wje@multiline.com.au wrote in message | news:#5hmxioGCHA.2324@tkmsftngp09... | | Hi, | | I am trying to convert this macro that it works on the column selected | | instead of column A:A. | | I just like to highlight the column in question and then run the macro. | | | | Your help is very much appreciated | | Andonny | | | | Sub ConvertTextToNumber() | | Dim Cell As Range, Rng1 As Range | | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | | For Each Cell In Rng1 | | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | | Cell = CDbl(Cell) | | End If | | Next Cell | | End Sub | | | | | | | | |
| 597 | Hi Justin you will need to be a bit more specific. Values are all you see from formulas by default. As a guess try using Ctrl+` -- "Justin" <r4al98gy@aol.com wrote in message news:1124601c21a9d$cde21a50$9ae62ecf@tkmsftngxa02... | How can I do this? Thanks in advance. |
| 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 . |
| 612 | Just to add to this thread: SpecialCells requires error handling in case the range does not contain constants, e.g.: On Error Resume Next Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) On Error GoTo 0 ' Rest of code... "" <DavidH@OzGrid.com wrote in message news:W5fR8.16$TG5.28441@vicpull1.telstra.net... Hi Andonny If you are converting numeric text constants to numbers then a Loop is a pretty slow method, as loops are generally slow. Try the code below: Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Range("IV65536").Copy Rng1.PasteSpecial , xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub Just select the entire Column and run the code. It assumes that cell IV65536 is empty. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Andonny" <wje@multiline.com.au wrote in message news:#5hmxioGCHA.2324@tkmsftngp09... | Hi, | I am trying to convert this macro that it works on the column selected | instead of column A:A. | I just like to highlight the column in question and then run the macro. | | Your help is very much appreciated | Andonny | | Sub ConvertTextToNumber() | Dim Cell As Range, Rng1 As Range | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | For Each Cell In Rng1 | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | Cell = CDbl(Cell) | End If | Next Cell | End Sub | | |
| 616 | Thanks, but I need them to be "really" blank, as I have numeric references to all cells in the C column (which get #values! with the if code). Any other ideas? thanks for replying, John -----Original Message----- If your talking about "cosmetically" blank (nothing displayed), You could start your formulas with: =IF(A1="","",your formula here) HTH RD "John Vera" <emperorj@hotmail.com wrote in message news:fd7201c21b93$d74e2380$a5e62ecf@tkmsftngxa07... Hi again. My spreadsheet is designed in such a way that you paste a column of data in. I need to make the rest of the columns that process the data extend or reduce automatically according to the # of rows the data column has. for example: Data set is column A1:A30 I need to make the C column extend down to C30 too. If the data set is A1:A15, I need col. C to extend down to C15 only, leaving C16:C30 blank. Any ideas? Help very much appreciated, John . |
| 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 . |
| 621 | Unless you are talking about using VBA code to do this, just using worksheet functions - then the formula that displays the results would have to be in the cell where the results would be displayed. In this case, the formula would have to be in column C. Column A would have not role. in C1 =If(len(trim(D1))=0,"",D1) would be an example. John Vera <emperorj@hotmail.com wrote in message news:12cc101c21ba9$faeaf3c0$35ef2ecf@TKMSFTNGXA11... Hi again. Is there any way to make a cell change the contents of another cell or cells? for example, I'd like to have a column A verify whether column D is blank or has a value, and then make column C be blank or have a value (depending only on the D values). Thanks in advance, John |