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 |