Cell format requires manual edit
I have a problem with changing the format of a pure number in a cell formatted as a text. After the type is changed to a number (actually it's changed to special - 000000), the cell refuses to change until after the cell is edited (with or without changes) and the ENTER key is pressed. Is there an easy way around this? A similar thing happens with a cell has a special enter character. Editing the cell and pressing enter changes the cell format to word wrap and to recognize the special character, though the character is not usually desired. I know how to delete the special characters with a find and replace, but it illustrates how excel refuses to change the cell formats until after you edit each cell. That can take a lot of time with thousands of entries, like the document database that I'm working with. Any suggestions? Thanks in advance, Andy Landen
landen991
excel
39879 articles. 
2 followers.
4 Replies
678 Views
You can force Excel to see text as numbers (provided, of course, they're numbers in the cells). See "Data Isn't Recognized..." http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com <landen99@gmail.com> wrote in message news:1127851146.963389.28900@o13g2000cwo.googlegroups.com... >I have a problem with changing the format of a pure number in a cell > formatted as a text. After the type is changed to a number (actually > it's changed to special - 000000), the cell refuses to change until > after the cell is edited (with or without changes) and the ENTER key is > pressed. Is there an easy way around this? > > A similar thing happens with a cell has a special enter character. > Editing the cell and pressing enter changes the cell format to word > wrap and to recognize the special character, though the character is > not usually desired. I know how to delete the special characters with > a find and replace, but it illustrates how excel refuses to change the > cell formats until after you edit each cell. That can take a lot of > time with thousands of entries, like the document database that I'm > working with. Any suggestions? > > Thanks in advance, > Andy Landen >
ng1Amazing how I discover a quick solution immediately after posting the message. Click on a problem cell and you see a yellow exclamation mark. Click on it and choose convert to number. On a massive scale, select a group of cells using the shift key, click on the yellow exclamation mark, and then choose convert to number.
I still have a problem with some of the data. It fits the criteria specified at the link: http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm, but it doesn't respond to the blank cell arithmetic idea. Editing the cell, I find an extra "space" after the numbers, but find and replace doesn't see it. I just resolved the issue by using the LEFT function to grab the appropriate number of digits and then convert the resultant values to numbers. Thanks for the link and the help. Andy Landen
Hi, Andy. =TRIM(a1) will also remove any non-printing characters from a cell. You might want to use that next time in case there's more than one space. Glad you got it sorted! ************ Anne Troy www.OfficeArticles.com <landen99@gmail.com> wrote in message news:1127853648.401646.76100@g14g2000cwa.googlegroups.com... >I still have a problem with some of the data. It fits the criteria > specified at the link: > http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm, > but it doesn't respond to the blank cell arithmetic idea. > Editing the cell, I find an extra "space" after the numbers, but find > and replace doesn't see it. I just resolved the issue by using the > LEFT function to grab the appropriate number of digits and then convert > the resultant values to numbers. Thanks for the link and the help. > > Andy Landen >
Similar Artilces:
Edit/Replace using VBA in Excel
Hi I'm a newbie and trying to implement some VBA code that automaticall runs a the edit replace tool in a number of sheets. The problem I hav is that I want the What:= & Replace:= values to refer to cells on a index sheet (i.e not the activesheet). Can anyone please help. Thanks -- Message posted from http://www.ExcelForum.com Fully qualify the range object: What:=ThisWorkbook.Worksheets("Index Sheet").Range("A3").Value Replace:= ThisWorkbook.Worksheets("Index Sheet").Range("A4").Value HTH, Bernie MS Excel MVP "Froojam >" &l...
Need data result in last cell of array that contains blank cells
Example: A1=200 B1=300 C1=400 D1=[blank] E1=[blank] F1=[blank] G1=(last cell from above that contains data...i.e. C1) I'm a bit of an Excel novice so could really use a little help. Thanks! Assuming that range will only contain numbers, try something like this: =LOOKUP(10^99,A1:F1) or...if you want to avoid an error when there are no numbers: =IF(COUNT(A1:F1),LOOKUP(10^99,A1:F1),"") Does that help? *********** Regards, Ron XL2003, WinXP "smesurfer" wrote: > Example: > > A1=200 > B1=300 > C1=400 >...
How do i extract worksheet cells with the same contents?
Office Pro Microsoft Excel . I import three separate lists into one worksheet and want to extract only cells with similar contents to a separate worksheet. Define "similar". If it is "the same" and those entries are in the same column, then you could use data --> autofilter on that column and copy the rows left in view to the other worksheet. "doncuba" wrote: > Office Pro Microsoft Excel . I import three separate lists into one worksheet > and want to extract only cells with similar contents to a separate worksheet. ...
DLGC_WANTMESSAGE doesn't works well for multi-line edit controls
I don't want ESC to dismiss my dialog box when focus is on the edit control. So I subclass the edit control called CMyEdit and add a handler to the WM_GETDLGCODE message and always return DLGC_WANTALLKEYS. I wish all keyboard input could be intercepted in my CMyEdit class now. But unfortunately when the edit control is a single-line control, keyboard input VK_RETURN and VK_ESCAPE are both intercepted. While when to multi-line edit controls only VK_RETURN could be intercepted, but press ESC still dismiss the dialogbox. Why doesn't DLGC_WANTALLKEYS intercepte all keyboard input? Thank ...
Pictures within a cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have created a list of the items in a collection of mine. I have 'drag & dropped' a picture of each of these items to a cell in a single column. <br><br>When viewing the picture properties, the option to "move and size with cell" is selected. However, if I select the sheet and sort by any column header, the pictures stay in their original position while the data in the surrounding columns re-orders according to the sort criteria. <br><br>I would appreciate any advice a...
Cell reference
For example, I know I can refer to another worksheet in a cell formula as such: =Data!C6 However, Is there a way to refer to the name of the sheet based on the name of the sheet being a variable (ie. the contents of another cell?) THanks Matt Lawson Hi =INDIRECT("'" & A1 & "'!C6) where A1 stores your sheet name "Matt Lawson" wrote: > For example, I know I can refer to another worksheet in a cell formula as such: > =Data!C6 > > However, Is there a way to refer to the name of the sheet based on the name > of the sheet being a var...
Pattern formatting for textboxes
Is there a way to have pattern formatting instead of solid colors for textboxes on forms? Thank you. -- Message posted via http://www.accessmonster.com On Wed, 07 Mar 2007 15:08:11 GMT, chris1 via AccessMonster.com wrote: > Is there a way to have pattern formatting instead of solid colors for > textboxes on forms? Thank you. In Access, no. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail ...
Finding the rightmost occupied cell
If I have a range of cells across a row, where only some of the cells on the left of the range are occupied, is there a way I could extract the value in the rightmost of the occupied cells (Excel 2003)? -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) If your data is in A19:N19, for example: =LOOKUP(2,1/(A19:N19<>""),A19:N19) Yes, I know it looks odd, but it works. Bob Umlas Excel MVP "Paul Hyett" <vidcapper@invalid83261.co.uk> wrote in message news:XNB8JtTJZ8NKFwYx@blueyonder.co.uk... > If I have a range of...
Keep adding to one cell to carry a total in another
Hi I want to be able to use one cell to keep adding new numbers to that will accumulate the total in another cell. Ex: I use A1 as the cell to input a new number that will carry a total in A2 any help would be appreciated Try this in the sheet module. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target <> Range("A1") Then Range("A1").Select Exit Sub End If Application.EnableEvents = False Range("A2").Value = Range("A1").Value + Range("A2").Value Target.Select Application.Enabl...
Can Cell X modify Cell Y?
Example: If cell A1 equals 1, then cell B1 should have the value 5, otherwise B1 should be 6. A condition in cell A1 is changing the value of cell B1... is that possible? Yes, of course. B1 contains this formula: =IF(A1=1,5,6) On Tue, 8 Feb 2005 12:03:02 -0800, "alMandragor" <alMandragor@discussions.microsoft.com> wrote: >Example: > >If cell A1 equals 1, then cell B1 should have the value 5, otherwise B1 >should be 6. > >A condition in cell A1 is changing the value of cell B1... is that possible? ...
Outlook 2003 'internet format' puzzler
In Outlook 2003, I have all the mail options set to HTML format for my emails. When I create a new contact directly from Outlook, and right-click on the email address field and select 'properties', it says the Internet Format is "Let Outlook decide the best sending format", which is what I want. The mystery is what happens when the new contact is created by Intellisync Lite when I sync my Sony Clie PDA. A new contact created this way shows that the Internet Format for that new contact's email address is "Send using Outlook's Rich Text Format", which I don...
Copy actual URL from hyperlink cell
I have a spreadsheet with thousands of records. One column contains hyperlinks either created manually using the Insert->Hyperlink action or via a =HYPERLINK() dynamic formula. I need to return the URL for the cyperlink column into another column so that I can use it for some other purpose. The new cells would simply contain text like http://website/folder/file.htm. I couldn't find a function that returned the URL, so I'm wondering if there is another way to get the info? A macro possibly? Thanks for your help IAN Ian Not sure if I understand, but the code below will iterat...
Formula Cell Error
Hi all, i have a problem with the following code, would appreciate if anyone can give me some help on this. Basically everything works fine except for this line: Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")" The cell will reflect "=SUM('J2':'J3')" instead of "=SUM(J2:J3)". --------------------------------------------------------------------------------------------- Sub Test() For i = 2 To 100 Step 1 Cells(i, 1).Select If Cells(i, 1).Interior.ColorI...
Formatted text in multiple lines in CListCtrl #3
I would like to know how can I display formatted text (Different fonts and colors) in multiple lines in a single cell in CListCtrl large icon view. I have seen samples on codeproject/codeguru that deals with changing text color/font. But I would like to display formatted text (e.g: Some bold text and some different color in multiple lines in a single item) along with an icon in each cell. How can I achieve this using CListCtrl? If not, can you please suggest any alternative solutions? The following article on codeproject suggests on how to change the row height in an owner drawn list control...
I need to count cells with conditional format
Hi, I have a table with cells that have conditional formats. If one condition is met then that cell become red. Not all cells are red on a column. I want to count only the red cells on that column. Is it possible? Thx. -- FlorianG Have you tried the COUNTIF function, using the same criteria set in the conditional formatting? Otherwise, the only way I know of to count red cells is through a macro. Simon Shaw www.accounttech.ca "Florian" wrote: > Hi, I have a table with cells that have conditional formats. > If one condition is met then that cell become red. Not all cells...
Date format question
Hi all, I am running a mde file on multiple XP workstations with Access 2002. What is really weird is on some machines, everything works well, but on some I get some formatting errors in certain queries. For example, I have a query that I am formating a long date to a short date, using the following line in the query: Date: format([Shipping],"Short Date") I am getting a Microsoft Access error that states: The expression On Click you entered as the event property setting produced the following error: Function is not available in expressions query expression 'Format([Shipp...
Tabbing from cell to cell
When I press the tab key in a brand new worksheet the cursor moves from A1 to L1 then to W1...anyone? Thanks! Connie An effect similar to the one you describe can occur if the "Transition navigation keys" checkbox is active. Goto Tools>Options>Transition and see if the box is checked "ConnieF" wrote: > When I press the tab key in a brand new worksheet the cursor moves from A1 to > L1 then to W1...anyone? > Thanks! > Connie ...
Modifying UnProtected Cells
I have a user who Is using Excel XP. SHe has created a workbook with Some Protected cells. She emails to another user who is using Excel 2000 - this user is unable to modify any cells. The error states that the cells are all protected. A user that has Excel XP or 2003 is able to modify the unprotected cells. I have installed all the updates to Office 2000 - any other suggestions? THanks in advance! Gladys: Likely the cells aren't being protected/unprotected properly. Cells are protected by default. Check this: http://www.officearticles.com/excel/cell_locking_in_microsoft_excel.htm *...
How to copy specific number of rows from cells to cells?
There is a given number in cell U1, which equals to 9, so I would like to copy 9 cells starting from cell V3, which should copy from V3 to V11 and paste text only into A3 to A11. If the given number in cell U1 is 6, then I would like to copy 6 cells starting from cell V3, which should copy from V3 to V11 and paste text only into A3 to A8. Does anyone have any suggestions on how to code this macro in excel 2003? Thanks in advance for any suggestions Eric Hi Eric Try.. Range("V3").Resize(Range("U1").Value).Copy Range("A3") -- Jacob (MVP -...
Check if content in cell is an integer
How do I check if the content in a cell, when divided by 4, returns an integer? It should give me 150 if it is an integer and display "NA" if it is not an integer. For example, the formula should check if the content A2 divided by 4 is an integer and display the necessary answer in cell C2. Hopefully the formula can be use to check any corresponding cell in this manner. I.E check if content in cell A3 when divided by 4 is an integer and display the necessary in cell C3, check if content in cell A4 when divided by 4 is an integer and diaplay the necessary in cell C4 and so on...
Saving and Excel sheet in a format such as JPG
I would like to save an excel sheet in a format such as JPG or something like that - for the reason of emailing invoices. I have tried cutting and pasing from Excel to Publisher, but I would think there would be an easier thing to do. Any idea? Thanks, Jeremy Select your range shift-edit|Copy Picture Then try pasting into Publisher. If that doesn't work, you could paste into Paint, save as .jpg and then import into Publisher (maybe???, I don't use Publisher). Jeremy R wrote: > > I would like to save an excel sheet in a format such as JPG or something like > that - f...
can i somehow put two cells into one column or split a column
Data>text to columns to split =A1&B1 in a third cell with concatenate 2 (or more) -- Regards, Peo Sjoblom (No private emails please) "Chrono" <Chrono@discussions.microsoft.com> wrote in message news:E211310C-11BC-423A-92AF-5F0863940E4D@microsoft.com... > ...
Formatting Cell Comments
Is there a way to pre-format cell comments? I am using Excel 2000 & 2003 and I'm looking for some sort of option t set the format of cell comments so I don't have to go into each an every comment and format it. And I'm not looking to use a macro for this (via PERSONAL.xl workbook). Is there a way to do this? Thank -- malik64 ----------------------------------------------------------------------- malik641's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2419 View this thread: http://www.excelforum.com/showthread.php?threadid=39622 Cell Comme...
subtracting 2 cells but only if 1st cell is greater than 0
In cell e11 I would like to subtract cell d11-c11 unless the calue in c11 is , or = to 0, in which case, I would like e11 to be 0. i have tried severeral =if and sumif formulas and can't get anything to work. Any suggestions? hi if i understand your post, you want c11 subtracted from d11 if c11 is greater than or equal to 0 otherwize 0. try this..... =IF(OR(C11>0,C11=0),D11-C11,0) post back if i misunderstood. Regards FSt1 "Maureen" wrote: > In cell e11 I would like to subtract cell d11-c11 unless the calue in c11 is > , or = to 0, in which case...
round off a cell
I have cells that contain times. Is there a way to format the cell to round the time to the nearest 15 minutes? Mike Hi Mike try =ROUND(A1*4*24,0)/(4*24) Frank Mike wrote: > I have cells that contain times. Is there a way to format the cell > to round the time to the nearest 15 minutes? > > Mike ...
Hi I'm a newbie and trying to implement some VBA code that automaticall runs a the edit replace tool in a number of sheets. The problem I hav is that I want the What:= & Replace:= values to refer to cells on a index sheet (i.e not the activesheet). Can anyone please help. Thanks -- Message posted from http://www.ExcelForum.com Fully qualify the range object: What:=ThisWorkbook.Worksheets("Index Sheet").Range("A3").Value Replace:= ThisWorkbook.Worksheets("Index Sheet").Range("A4").Value HTH, Bernie MS Excel MVP "Froojam >" &l...
Need data result in last cell of array that contains blank cells
Example: A1=200 B1=300 C1=400 D1=[blank] E1=[blank] F1=[blank] G1=(last cell from above that contains data...i.e. C1) I'm a bit of an Excel novice so could really use a little help. Thanks! Assuming that range will only contain numbers, try something like this: =LOOKUP(10^99,A1:F1) or...if you want to avoid an error when there are no numbers: =IF(COUNT(A1:F1),LOOKUP(10^99,A1:F1),"") Does that help? *********** Regards, Ron XL2003, WinXP "smesurfer" wrote: > Example: > > A1=200 > B1=300 > C1=400 >...
How do i extract worksheet cells with the same contents?
Office Pro Microsoft Excel . I import three separate lists into one worksheet and want to extract only cells with similar contents to a separate worksheet. Define "similar". If it is "the same" and those entries are in the same column, then you could use data --> autofilter on that column and copy the rows left in view to the other worksheet. "doncuba" wrote: > Office Pro Microsoft Excel . I import three separate lists into one worksheet > and want to extract only cells with similar contents to a separate worksheet. ...
DLGC_WANTMESSAGE doesn't works well for multi-line edit controls
I don't want ESC to dismiss my dialog box when focus is on the edit control. So I subclass the edit control called CMyEdit and add a handler to the WM_GETDLGCODE message and always return DLGC_WANTALLKEYS. I wish all keyboard input could be intercepted in my CMyEdit class now. But unfortunately when the edit control is a single-line control, keyboard input VK_RETURN and VK_ESCAPE are both intercepted. While when to multi-line edit controls only VK_RETURN could be intercepted, but press ESC still dismiss the dialogbox. Why doesn't DLGC_WANTALLKEYS intercepte all keyboard input? Thank ...
Pictures within a cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have created a list of the items in a collection of mine. I have 'drag & dropped' a picture of each of these items to a cell in a single column. <br><br>When viewing the picture properties, the option to "move and size with cell" is selected. However, if I select the sheet and sort by any column header, the pictures stay in their original position while the data in the surrounding columns re-orders according to the sort criteria. <br><br>I would appreciate any advice a...
Cell reference
For example, I know I can refer to another worksheet in a cell formula as such: =Data!C6 However, Is there a way to refer to the name of the sheet based on the name of the sheet being a variable (ie. the contents of another cell?) THanks Matt Lawson Hi =INDIRECT("'" & A1 & "'!C6) where A1 stores your sheet name "Matt Lawson" wrote: > For example, I know I can refer to another worksheet in a cell formula as such: > =Data!C6 > > However, Is there a way to refer to the name of the sheet based on the name > of the sheet being a var...
Pattern formatting for textboxes
Is there a way to have pattern formatting instead of solid colors for textboxes on forms? Thank you. -- Message posted via http://www.accessmonster.com On Wed, 07 Mar 2007 15:08:11 GMT, chris1 via AccessMonster.com wrote: > Is there a way to have pattern formatting instead of solid colors for > textboxes on forms? Thank you. In Access, no. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail ...
Finding the rightmost occupied cell
If I have a range of cells across a row, where only some of the cells on the left of the range are occupied, is there a way I could extract the value in the rightmost of the occupied cells (Excel 2003)? -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) If your data is in A19:N19, for example: =LOOKUP(2,1/(A19:N19<>""),A19:N19) Yes, I know it looks odd, but it works. Bob Umlas Excel MVP "Paul Hyett" <vidcapper@invalid83261.co.uk> wrote in message news:XNB8JtTJZ8NKFwYx@blueyonder.co.uk... > If I have a range of...
Keep adding to one cell to carry a total in another
Hi I want to be able to use one cell to keep adding new numbers to that will accumulate the total in another cell. Ex: I use A1 as the cell to input a new number that will carry a total in A2 any help would be appreciated Try this in the sheet module. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target <> Range("A1") Then Range("A1").Select Exit Sub End If Application.EnableEvents = False Range("A2").Value = Range("A1").Value + Range("A2").Value Target.Select Application.Enabl...
Can Cell X modify Cell Y?
Example: If cell A1 equals 1, then cell B1 should have the value 5, otherwise B1 should be 6. A condition in cell A1 is changing the value of cell B1... is that possible? Yes, of course. B1 contains this formula: =IF(A1=1,5,6) On Tue, 8 Feb 2005 12:03:02 -0800, "alMandragor" <alMandragor@discussions.microsoft.com> wrote: >Example: > >If cell A1 equals 1, then cell B1 should have the value 5, otherwise B1 >should be 6. > >A condition in cell A1 is changing the value of cell B1... is that possible? ...
Outlook 2003 'internet format' puzzler
In Outlook 2003, I have all the mail options set to HTML format for my emails. When I create a new contact directly from Outlook, and right-click on the email address field and select 'properties', it says the Internet Format is "Let Outlook decide the best sending format", which is what I want. The mystery is what happens when the new contact is created by Intellisync Lite when I sync my Sony Clie PDA. A new contact created this way shows that the Internet Format for that new contact's email address is "Send using Outlook's Rich Text Format", which I don...
Copy actual URL from hyperlink cell
I have a spreadsheet with thousands of records. One column contains hyperlinks either created manually using the Insert->Hyperlink action or via a =HYPERLINK() dynamic formula. I need to return the URL for the cyperlink column into another column so that I can use it for some other purpose. The new cells would simply contain text like http://website/folder/file.htm. I couldn't find a function that returned the URL, so I'm wondering if there is another way to get the info? A macro possibly? Thanks for your help IAN Ian Not sure if I understand, but the code below will iterat...
Formula Cell Error
Hi all, i have a problem with the following code, would appreciate if anyone can give me some help on this. Basically everything works fine except for this line: Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")" The cell will reflect "=SUM('J2':'J3')" instead of "=SUM(J2:J3)". --------------------------------------------------------------------------------------------- Sub Test() For i = 2 To 100 Step 1 Cells(i, 1).Select If Cells(i, 1).Interior.ColorI...
Formatted text in multiple lines in CListCtrl #3
I would like to know how can I display formatted text (Different fonts and colors) in multiple lines in a single cell in CListCtrl large icon view. I have seen samples on codeproject/codeguru that deals with changing text color/font. But I would like to display formatted text (e.g: Some bold text and some different color in multiple lines in a single item) along with an icon in each cell. How can I achieve this using CListCtrl? If not, can you please suggest any alternative solutions? The following article on codeproject suggests on how to change the row height in an owner drawn list control...
I need to count cells with conditional format
Hi, I have a table with cells that have conditional formats. If one condition is met then that cell become red. Not all cells are red on a column. I want to count only the red cells on that column. Is it possible? Thx. -- FlorianG Have you tried the COUNTIF function, using the same criteria set in the conditional formatting? Otherwise, the only way I know of to count red cells is through a macro. Simon Shaw www.accounttech.ca "Florian" wrote: > Hi, I have a table with cells that have conditional formats. > If one condition is met then that cell become red. Not all cells...
Date format question
Hi all, I am running a mde file on multiple XP workstations with Access 2002. What is really weird is on some machines, everything works well, but on some I get some formatting errors in certain queries. For example, I have a query that I am formating a long date to a short date, using the following line in the query: Date: format([Shipping],"Short Date") I am getting a Microsoft Access error that states: The expression On Click you entered as the event property setting produced the following error: Function is not available in expressions query expression 'Format([Shipp...
Tabbing from cell to cell
When I press the tab key in a brand new worksheet the cursor moves from A1 to L1 then to W1...anyone? Thanks! Connie An effect similar to the one you describe can occur if the "Transition navigation keys" checkbox is active. Goto Tools>Options>Transition and see if the box is checked "ConnieF" wrote: > When I press the tab key in a brand new worksheet the cursor moves from A1 to > L1 then to W1...anyone? > Thanks! > Connie ...
Modifying UnProtected Cells
I have a user who Is using Excel XP. SHe has created a workbook with Some Protected cells. She emails to another user who is using Excel 2000 - this user is unable to modify any cells. The error states that the cells are all protected. A user that has Excel XP or 2003 is able to modify the unprotected cells. I have installed all the updates to Office 2000 - any other suggestions? THanks in advance! Gladys: Likely the cells aren't being protected/unprotected properly. Cells are protected by default. Check this: http://www.officearticles.com/excel/cell_locking_in_microsoft_excel.htm *...
How to copy specific number of rows from cells to cells?
There is a given number in cell U1, which equals to 9, so I would like to copy 9 cells starting from cell V3, which should copy from V3 to V11 and paste text only into A3 to A11. If the given number in cell U1 is 6, then I would like to copy 6 cells starting from cell V3, which should copy from V3 to V11 and paste text only into A3 to A8. Does anyone have any suggestions on how to code this macro in excel 2003? Thanks in advance for any suggestions Eric Hi Eric Try.. Range("V3").Resize(Range("U1").Value).Copy Range("A3") -- Jacob (MVP -...
Check if content in cell is an integer
How do I check if the content in a cell, when divided by 4, returns an integer? It should give me 150 if it is an integer and display "NA" if it is not an integer. For example, the formula should check if the content A2 divided by 4 is an integer and display the necessary answer in cell C2. Hopefully the formula can be use to check any corresponding cell in this manner. I.E check if content in cell A3 when divided by 4 is an integer and display the necessary in cell C3, check if content in cell A4 when divided by 4 is an integer and diaplay the necessary in cell C4 and so on...
Saving and Excel sheet in a format such as JPG
I would like to save an excel sheet in a format such as JPG or something like that - for the reason of emailing invoices. I have tried cutting and pasing from Excel to Publisher, but I would think there would be an easier thing to do. Any idea? Thanks, Jeremy Select your range shift-edit|Copy Picture Then try pasting into Publisher. If that doesn't work, you could paste into Paint, save as .jpg and then import into Publisher (maybe???, I don't use Publisher). Jeremy R wrote: > > I would like to save an excel sheet in a format such as JPG or something like > that - f...
can i somehow put two cells into one column or split a column
Data>text to columns to split =A1&B1 in a third cell with concatenate 2 (or more) -- Regards, Peo Sjoblom (No private emails please) "Chrono" <Chrono@discussions.microsoft.com> wrote in message news:E211310C-11BC-423A-92AF-5F0863940E4D@microsoft.com... > ...
Formatting Cell Comments
Is there a way to pre-format cell comments? I am using Excel 2000 & 2003 and I'm looking for some sort of option t set the format of cell comments so I don't have to go into each an every comment and format it. And I'm not looking to use a macro for this (via PERSONAL.xl workbook). Is there a way to do this? Thank -- malik64 ----------------------------------------------------------------------- malik641's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2419 View this thread: http://www.excelforum.com/showthread.php?threadid=39622 Cell Comme...
subtracting 2 cells but only if 1st cell is greater than 0
In cell e11 I would like to subtract cell d11-c11 unless the calue in c11 is , or = to 0, in which case, I would like e11 to be 0. i have tried severeral =if and sumif formulas and can't get anything to work. Any suggestions? hi if i understand your post, you want c11 subtracted from d11 if c11 is greater than or equal to 0 otherwize 0. try this..... =IF(OR(C11>0,C11=0),D11-C11,0) post back if i misunderstood. Regards FSt1 "Maureen" wrote: > In cell e11 I would like to subtract cell d11-c11 unless the calue in c11 is > , or = to 0, in which case...
round off a cell
I have cells that contain times. Is there a way to format the cell to round the time to the nearest 15 minutes? Mike Hi Mike try =ROUND(A1*4*24,0)/(4*24) Frank Mike wrote: > I have cells that contain times. Is there a way to format the cell > to round the time to the nearest 15 minutes? > > Mike ...