When cells with dates don't format
I have some cells which contain dates. These dates are a link from another data source (Bloomberg) and when I try to formatt the cells, I cannot. They will only display the date in mm/dd/yyyy formatt. Any ideas? Thanks
Woody13
excel.misc
78881 articles. 
5 followers.
6 Replies
774 Views
My first guess is that the cells are coming in as text. Look for an
apostrophe (" ' ") in front of the data in the cell. If not, try using "text
to columns" (select the entire column, click Data, Text to Columns, Finish).
Then, attempt to re-format the cells (or entire column) as a date with the
form that you want.
Also, make sure that the cells aren't protected (they shouldn't be if
this is your own spreadsheet).
Steve in Ohio
"Woody13" wrote:
> I have some cells which contain dates. These dates are a link from another
> data source (Bloomberg) and when I try to formatt the cells, I cannot. They
> will only display the date in mm/dd/yyyy formatt. Any ideas? Thanks
StephenKnappHi Woody sounds to me they are really in the workbook as text, not as a date ... if you right mouse click on one and choose format / general you should see a number somewhat like 38314 (for Nov 23, 2004). If you don't see a number like this, select an unused cell somewhere (maybe on another sheet) and choose copy, select your cells and choose edit / paste special - ADD, click OK now your dates should be dates and you can format them via format / cells. hope this helps cheers JulieD "Woody13" <Woody13@discussions.microsoft.com> wrote in message news:8DF9711C-6E2B-436F-9163-52EB0534D4D4@microsoft.com... >I have some cells which contain dates. These dates are a link from another > data source (Bloomberg) and when I try to formatt the cells, I cannot. > They > will only display the date in mm/dd/yyyy formatt. Any ideas? Thanks
JulieD1=+blp(D70&" Corp","Maturity",,,BLP|M!'0640P1AQ6 Corp,[MATURITY]') This is
the formula in the cell. If you look at the part after ,,,BLP you can see
there is a ' before and at the end so it may come in as text, but I have
other formulas that are the same way and they do not come in as test. I tried
the Text to Columns but it just writes the formula out in the cell. Any
other suggestions? Thanks
"Stephen Knapp" wrote:
> My first guess is that the cells are coming in as text. Look for an
> apostrophe (" ' ") in front of the data in the cell. If not, try using "text
> to columns" (select the entire column, click Data, Text to Columns, Finish).
> Then, attempt to re-format the cells (or entire column) as a date with the
> form that you want.
> Also, make sure that the cells aren't protected (they shouldn't be if
> this is your own spreadsheet).
>
> Steve in Ohio
>
> "Woody13" wrote:
>
> > I have some cells which contain dates. These dates are a link from another
> > data source (Bloomberg) and when I try to formatt the cells, I cannot. They
> > will only display the date in mm/dd/yyyy formatt. Any ideas? Thanks
Julie, i checked and they are coming in as text, however, do to the constraints of the sheet I need to have them formatted where the cell is. What is odd, is that I can use these text dates in calculations. For example I can subtract one date from the other to get # of days between them. I didn't think you would be able to do that if it was pure test. I don't know. "JulieD" wrote: > Hi Woody > > sounds to me they are really in the workbook as text, not as a date ... if > you right mouse click on one and choose format / general you should see a > number somewhat like 38314 (for Nov 23, 2004). If you don't see a number > like this, select an unused cell somewhere (maybe on another sheet) and > choose copy, select your cells and choose edit / paste special - ADD, click > OK > > now your dates should be dates and you can format them via format / cells. > > hope this helps > cheers > JulieD > > "Woody13" <Woody13@discussions.microsoft.com> wrote in message > news:8DF9711C-6E2B-436F-9163-52EB0534D4D4@microsoft.com... > >I have some cells which contain dates. These dates are a link from another > > data source (Bloomberg) and when I try to formatt the cells, I cannot. > > They > > will only display the date in mm/dd/yyyy formatt. Any ideas? Thanks > > >
you can get the value for the data with the function datavalue and format this number as a date in the format you want. "Woody13" wrote: > I have some cells which contain dates. These dates are a link from another > data source (Bloomberg) and when I try to formatt the cells, I cannot. They > will only display the date in mm/dd/yyyy formatt. Any ideas? Thanks
GaryRoweGary, VALUE did it. Thanks to all of you. "Gary Rowe" wrote: > you can get the value for the data with the function datavalue and format > this number as a date in the format you want. > > "Woody13" wrote: > > > I have some cells which contain dates. These dates are a link from another > > data source (Bloomberg) and when I try to formatt the cells, I cannot. They > > will only display the date in mm/dd/yyyy formatt. Any ideas? Thanks
Similar Artilces:
Displaying active cell in upper left corner
When selecting a range by name, if the range is not in the cells displayed at the time, Excel then shows the first cell of the range at the bottom of the display. This is also true if I have hyperlinked to a particular cell as in an index. How do I get the active cell shown at the upper left corner of the display instead? :) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Sub setscroll()'no need to activate first ActiveWindow.ScrollRow = [a5].Row End Sub This ...
set cell margins
Does anyone know how to set cell margins in Excel? I'm preparing a long list of items for a committee review, and I think it would be more user/reader friendly with some white space. The columns are mixed numbers, dates, and text, but the largest cells are text. June Format>Cells>Alignment. Look at the various options in "Horizontal" and "Vertical" Top, bottom, centered, justified, right indent, left indent and a gang of others. Also row heights and column widths can be manipulated. Gord Dibben Excel MVP On Sun, 5 Dec 2004 10:35:28 -0800, "Junebug...
Creating a Chart from Cells that use a List Box
Hi, I am trying to create a number of charts (e.g., pie, line etc) based on data that is slelected from a list box (i.e., data validation). What i would like to do is create the charts before any data is entered (if that makes sense) and as data is entered the charts will display this. ...
Smartlist's Account Trx should show document dates of submodules
Hi, When you use the Smartlist object Account Transactions and try to find the document date of those transactions coming from the sub modules - Smartlist will not be able to show it. There is a field Document Date, but it will show 00/00/0000. The created date is also not very useful as it takes the system/user date when the transaction was actually posted. Not all the time will the document date be equal to the transaction date. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click ...
Form problems in a table with merged cells
I have a spreadsheet I've been using for several years in which I had set up a ribbon button to show a data entry form using the table headers. I merged some cells in an area adjacent to the table and the form would no longer work. Clicking the ribbon button simply gave the message that the command could not be used in a table or range containing merged cells. I unmerged the offending cells but it still doesn't work. I'm stumped - can anyone please help> -- John ...
Date formatting on charts
I have a simple line chart (in PPT 2007) with dates going along the x-axis. They are m/d/y format - I cannot seem to change them to another format for the life of me! I am going to format-axis-number-date and cannot change the format at all! Does anyone know how this procedure - so simple in PPT 2003 can be achieved? Help and thanks! -- Marc Press with your right mouse button on the chart and choose Format Axis. From the new menu go under Number - you'll have a list of different formats available. Kristiina I help with Excel and PowerPoint http://www.officetodo...
Tools/Options/Charts-Active cells is dimmed. Want to select leave
I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in chart, but still showed zeros. When I went to Tools/Options/Charts, the Active Cells area was dimmed and I could not select "leave gaps". Any suggestions? Thanks, Hi, Was the chart selected when you did Tools>Options ? Cheers Andy teds wrote: > I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in > chart, but still showed zeros. When I went to Tools/Options/Charts, the > Active Cells area was dimmed and I could not select "leave gaps". Any > suggestions? &...
how can I split a single cell diagonally in Excel 2000
Anyone out there know a way to split a single excel cell diagonally in order to have it contain 2 pieces of information? Forrest, As far as I know, you can not split a cell diagonally so that it can contain 2 pieces of information. However, you can merge cells which might give you the effect that you want. To do so, go to the standard toolbar and hit Format -> Cells -> Alignment Tab -> Text Control and work with the merged cells option. ---- Regards, John Mansfield http://www.pdbook.com "Forrest" wrote: > Anyone out there know a way to split a single excel ce...
VBA code to insert pictures in a column of cells
I have a folder of 99 pictures with filenames: image01.jpg, image02.jpg up to image99.jpg. All of these images are small, and the same size. I want to insert them (in order) in a column of cells (A1, A2, to A99) using VBA. The images should be embedded, not linked. Resizing cells to the right size would be a nice extra, but not necessary - it's easy in this case to resize manually. Thanks for looking at my question - and I hope to hear some good suggestions! ...
recurring dates
I need to be able to set set a deadline based on prior dates. These can go back years. I would like to set up a current 'next appointment, for instance ever 90 days, based on a date of arrival. For example, if date of arrival is 2/13/03, how do I make a formula that is effective now. In other words, the cell updates to a new deadline every ninety days, and stays current. Thanks a bunch. Maybe this which calculates the number of 90 day periods that have elapsed since the start date. =(FLOOR((A1-TODAY())/90,1)+1)*90+A1 Understand though that working with dates is tricky. There i...
Date Formulas #3
How can I make a cell correct the year of a date Hi Charlene not really sure what you're after here if you have 1/1/05 and you want to display just 2005 then right mouse click on the cell and choose format cells / on the numbers tab, choose custom and then type yyyy in the white line and and click OK. however, this just displays the date showing only the year, if you want to extract the year only to another cell you can use =year(A1) where A1 contains the date if you're after something else, please type a few examples of the data you have and what you want to see. Cheers JulieD...
Automatically match destination formatting?
I have a spreadsheet where I would like everything I paste into to it to match the formattting of the cells around it--not retain the formatting it had in its original location. I don't want to have to select "match destination formatting" every time--I want it to default to that. Any ideas? Thanks. I cannot find a way to make Excel paste automatically using destination formatting. A workaround is to use a macro to do the paste: Sub PasteWithDestinationFormatting() ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _ False, NoHTML...
Hiding Columns based on cell value
I am a novice when it comes to writing VBA code, I would greatly appreciate any help I can get in figuring out my ?. I would like to write a macro that automatically hides columns of data based on the value of a cell (I2) with a picklist. Cell I2's picklist is monthly values (formatted as Jan-10 though Dec-10 but real values are 1/1/2010 through 12/1/2010). I have a range that contains work week end date values (1/8/2010 to 12/31/2010) in L6:BK6. I would like to have the macro hide columns that are less than date value chosen in I2. For example, if a user selects "...
Insert date automatically
I have a column with values such as: 3/25 3/24 3/29 3/21 3/21 3/21 And wish to automatically add todays date if the value of the cell is blank in the mm/dd format. I have used an =If(Column:Column="",TODAY()) in a column next to it and formatted it in mm/dd to get the desired result. But how can I take this value (todays date) and insert it back into the original column automatically and without changing the other valuse? As always thank you! Rick Hi, Rick: Here's one way. Step 1 is to make a backup of your original file so you have a fallback in case something goes ...
Custom Formatting in a Button
Hello guys, It sounds simple but I have not been able to find any help on th topic. I do lots of formatting in Excel XP. I do not want to alway right-click on the cell(s) and go to the Format menu. My Q is can I place an icon in the tool bar (I know how to do that that will have the custom fomat I need for the cell. Thank you. Your help is much appreciated -- Artful Dodge ----------------------------------------------------------------------- Artful Dodger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1656 View this thread: http://www.excelforum.com/showth...
C or C++ Utility for wrting .doc format
dear all, my final output is to write MS Word .doc file my input is some text and Images . and i have to write using either c or c++ , can you tell me any third party Libs or is there any API provided by Windows or MSVC or .... if there is nothing and i have understand the complete file format and write it as binary file then please help me how can i get the format details and i have Image decoder all ready developed .... so there is no problem of decoding the images Regards, Karunesh >karunesh" <karunesh.ind@gmail.com> wrote in message > me how can i get the format deta...
How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...
Is there Formula to Tab to certain cells
Is there a formula to tab through certain cells? I have a form with about 15 cells that I would like to access easily through the tab key....is there any way to do that? One way to explicitly control the exact "next cell of focus" selection, is to select the cells in the desired order of travel, and then preserve this ordered movement by creating a named range. This old post describes the steps that can be taken to create such a "named range". http://tinyurl.com/39vzv -- HTH, RD ============================================== Please keep all correspondence within ...
Conditional formatting error
I have a form with multiple date fields, whenever the date entered is within 6 months of today's date, I have conditional formatting set to show the date in red. Taken From Dialog Box: "Field Value is - Less than - Date()-182.5" There are instances where the formatting is being applied and others where it is not. In some cases on the same form. I don't know where to go to get the form code to past here so help there would also be appreciated. Thanks, Dan Hi. To get the code, open the form in design view, click on one of the date fields, open t...
The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...
Prevent change to refs in second worksheet when drag cells in firs
I have a complex calendar in Excel. Each week, admins Ctrl-drag cells among different locations in the primary calendar worksheet. I have a second worksheet that needs to list those cell values in a fixed configuration, e.g. the value in Sheet1!A1 needs to always appear in the cell of Sheet2!G10. Sheet2!G10 contains the formula: “Sheet1!$A$1”. If the user drags Sheet1!A1 to Sheet1!A3, the formula in Sheet2!G10 gets automatically updated to “Sheet1!$A$3”. I have tried all combos of locking and protecting to prevent this updating. Is there a way to do this? Try referencing the other shee...
How do I split a single cell in Excel ?
I have a calendar with one day in each cell. I need to split a cell horizontally. Is this possible ? How do I split an individual cell ? hi, you can draw a line for corner to corner in a cell but you cannot "split" a cell like that ie not possible. >-----Original Message----- >I have a calendar with one day in each cell. I need to split a cell >horizontally. Is this possible ? How do I split an individual cell ? >. > Not splitting a cell--but putting information on multiple lines with in the same cell??? If that's ok, use alt-enter to force that new lin...
=if() statement -- test for any text in cell
Hi, I'm trying to convert the following statement . . . =if(A1=______ , "+", "") to . . . a search for any text, that might happen to be in cell A1. I don't know what the best option is for filling in the blank after A1= Thanks So much hor helping me if possible, Jim You could reverse your argument to =IF(A1="","","+") It will depend on whether you expect there to be anything else in the cell. It won't work if there is a number. There will be a way - I just ain't got it yet -- Russell...
how to print cells based on "Yes".
If the first cell in row 1 of Column "A" is "Yes", how do I get preselected contiguous or non contiguous cells in the same row to print? If the answer is "no", I do not want cells in that row to print. Thanks for anyones help! Francis Knight Hi Francis You can use Data>AutoFilter to filter on that column and print. Only the visible cells will print -- Regards Ron de Bruin http://www.rondebruin.nl "Francis Knight" <Francis Knight@discussions.microsoft.com> wrote in message news:43968233-50D0-4038-B31B-3C089DA99A7B@microsoft.com... &...
Sort
I am trying to sort my sales table, it doen't seem to work for me. A comment comes up: this operation requires the merged cells to be idendically sized. Can you help? Thanks Easiest way is to *unmerge* the cells, and then use "Center Across Selection" instead. <Format> <Cells> <Alignment> tab, And expand the "Horizontal" window, and choose, "Center Across Selection" -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ==============================...
When selecting a range by name, if the range is not in the cells displayed at the time, Excel then shows the first cell of the range at the bottom of the display. This is also true if I have hyperlinked to a particular cell as in an index. How do I get the active cell shown at the upper left corner of the display instead? :) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Sub setscroll()'no need to activate first ActiveWindow.ScrollRow = [a5].Row End Sub This ...
set cell margins
Does anyone know how to set cell margins in Excel? I'm preparing a long list of items for a committee review, and I think it would be more user/reader friendly with some white space. The columns are mixed numbers, dates, and text, but the largest cells are text. June Format>Cells>Alignment. Look at the various options in "Horizontal" and "Vertical" Top, bottom, centered, justified, right indent, left indent and a gang of others. Also row heights and column widths can be manipulated. Gord Dibben Excel MVP On Sun, 5 Dec 2004 10:35:28 -0800, "Junebug...
Creating a Chart from Cells that use a List Box
Hi, I am trying to create a number of charts (e.g., pie, line etc) based on data that is slelected from a list box (i.e., data validation). What i would like to do is create the charts before any data is entered (if that makes sense) and as data is entered the charts will display this. ...
Smartlist's Account Trx should show document dates of submodules
Hi, When you use the Smartlist object Account Transactions and try to find the document date of those transactions coming from the sub modules - Smartlist will not be able to show it. There is a field Document Date, but it will show 00/00/0000. The created date is also not very useful as it takes the system/user date when the transaction was actually posted. Not all the time will the document date be equal to the transaction date. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click ...
Form problems in a table with merged cells
I have a spreadsheet I've been using for several years in which I had set up a ribbon button to show a data entry form using the table headers. I merged some cells in an area adjacent to the table and the form would no longer work. Clicking the ribbon button simply gave the message that the command could not be used in a table or range containing merged cells. I unmerged the offending cells but it still doesn't work. I'm stumped - can anyone please help> -- John ...
Date formatting on charts
I have a simple line chart (in PPT 2007) with dates going along the x-axis. They are m/d/y format - I cannot seem to change them to another format for the life of me! I am going to format-axis-number-date and cannot change the format at all! Does anyone know how this procedure - so simple in PPT 2003 can be achieved? Help and thanks! -- Marc Press with your right mouse button on the chart and choose Format Axis. From the new menu go under Number - you'll have a list of different formats available. Kristiina I help with Excel and PowerPoint http://www.officetodo...
Tools/Options/Charts-Active cells is dimmed. Want to select leave
I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in chart, but still showed zeros. When I went to Tools/Options/Charts, the Active Cells area was dimmed and I could not select "leave gaps". Any suggestions? Thanks, Hi, Was the chart selected when you did Tools>Options ? Cheers Andy teds wrote: > I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in > chart, but still showed zeros. When I went to Tools/Options/Charts, the > Active Cells area was dimmed and I could not select "leave gaps". Any > suggestions? &...
how can I split a single cell diagonally in Excel 2000
Anyone out there know a way to split a single excel cell diagonally in order to have it contain 2 pieces of information? Forrest, As far as I know, you can not split a cell diagonally so that it can contain 2 pieces of information. However, you can merge cells which might give you the effect that you want. To do so, go to the standard toolbar and hit Format -> Cells -> Alignment Tab -> Text Control and work with the merged cells option. ---- Regards, John Mansfield http://www.pdbook.com "Forrest" wrote: > Anyone out there know a way to split a single excel ce...
VBA code to insert pictures in a column of cells
I have a folder of 99 pictures with filenames: image01.jpg, image02.jpg up to image99.jpg. All of these images are small, and the same size. I want to insert them (in order) in a column of cells (A1, A2, to A99) using VBA. The images should be embedded, not linked. Resizing cells to the right size would be a nice extra, but not necessary - it's easy in this case to resize manually. Thanks for looking at my question - and I hope to hear some good suggestions! ...
recurring dates
I need to be able to set set a deadline based on prior dates. These can go back years. I would like to set up a current 'next appointment, for instance ever 90 days, based on a date of arrival. For example, if date of arrival is 2/13/03, how do I make a formula that is effective now. In other words, the cell updates to a new deadline every ninety days, and stays current. Thanks a bunch. Maybe this which calculates the number of 90 day periods that have elapsed since the start date. =(FLOOR((A1-TODAY())/90,1)+1)*90+A1 Understand though that working with dates is tricky. There i...
Date Formulas #3
How can I make a cell correct the year of a date Hi Charlene not really sure what you're after here if you have 1/1/05 and you want to display just 2005 then right mouse click on the cell and choose format cells / on the numbers tab, choose custom and then type yyyy in the white line and and click OK. however, this just displays the date showing only the year, if you want to extract the year only to another cell you can use =year(A1) where A1 contains the date if you're after something else, please type a few examples of the data you have and what you want to see. Cheers JulieD...
Automatically match destination formatting?
I have a spreadsheet where I would like everything I paste into to it to match the formattting of the cells around it--not retain the formatting it had in its original location. I don't want to have to select "match destination formatting" every time--I want it to default to that. Any ideas? Thanks. I cannot find a way to make Excel paste automatically using destination formatting. A workaround is to use a macro to do the paste: Sub PasteWithDestinationFormatting() ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _ False, NoHTML...
Hiding Columns based on cell value
I am a novice when it comes to writing VBA code, I would greatly appreciate any help I can get in figuring out my ?. I would like to write a macro that automatically hides columns of data based on the value of a cell (I2) with a picklist. Cell I2's picklist is monthly values (formatted as Jan-10 though Dec-10 but real values are 1/1/2010 through 12/1/2010). I have a range that contains work week end date values (1/8/2010 to 12/31/2010) in L6:BK6. I would like to have the macro hide columns that are less than date value chosen in I2. For example, if a user selects "...
Insert date automatically
I have a column with values such as: 3/25 3/24 3/29 3/21 3/21 3/21 And wish to automatically add todays date if the value of the cell is blank in the mm/dd format. I have used an =If(Column:Column="",TODAY()) in a column next to it and formatted it in mm/dd to get the desired result. But how can I take this value (todays date) and insert it back into the original column automatically and without changing the other valuse? As always thank you! Rick Hi, Rick: Here's one way. Step 1 is to make a backup of your original file so you have a fallback in case something goes ...
Custom Formatting in a Button
Hello guys, It sounds simple but I have not been able to find any help on th topic. I do lots of formatting in Excel XP. I do not want to alway right-click on the cell(s) and go to the Format menu. My Q is can I place an icon in the tool bar (I know how to do that that will have the custom fomat I need for the cell. Thank you. Your help is much appreciated -- Artful Dodge ----------------------------------------------------------------------- Artful Dodger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1656 View this thread: http://www.excelforum.com/showth...
C or C++ Utility for wrting .doc format
dear all, my final output is to write MS Word .doc file my input is some text and Images . and i have to write using either c or c++ , can you tell me any third party Libs or is there any API provided by Windows or MSVC or .... if there is nothing and i have understand the complete file format and write it as binary file then please help me how can i get the format details and i have Image decoder all ready developed .... so there is no problem of decoding the images Regards, Karunesh >karunesh" <karunesh.ind@gmail.com> wrote in message > me how can i get the format deta...
How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...
Is there Formula to Tab to certain cells
Is there a formula to tab through certain cells? I have a form with about 15 cells that I would like to access easily through the tab key....is there any way to do that? One way to explicitly control the exact "next cell of focus" selection, is to select the cells in the desired order of travel, and then preserve this ordered movement by creating a named range. This old post describes the steps that can be taken to create such a "named range". http://tinyurl.com/39vzv -- HTH, RD ============================================== Please keep all correspondence within ...
Conditional formatting error
I have a form with multiple date fields, whenever the date entered is within 6 months of today's date, I have conditional formatting set to show the date in red. Taken From Dialog Box: "Field Value is - Less than - Date()-182.5" There are instances where the formatting is being applied and others where it is not. In some cases on the same form. I don't know where to go to get the form code to past here so help there would also be appreciated. Thanks, Dan Hi. To get the code, open the form in design view, click on one of the date fields, open t...
The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...
Prevent change to refs in second worksheet when drag cells in firs
I have a complex calendar in Excel. Each week, admins Ctrl-drag cells among different locations in the primary calendar worksheet. I have a second worksheet that needs to list those cell values in a fixed configuration, e.g. the value in Sheet1!A1 needs to always appear in the cell of Sheet2!G10. Sheet2!G10 contains the formula: “Sheet1!$A$1”. If the user drags Sheet1!A1 to Sheet1!A3, the formula in Sheet2!G10 gets automatically updated to “Sheet1!$A$3”. I have tried all combos of locking and protecting to prevent this updating. Is there a way to do this? Try referencing the other shee...
How do I split a single cell in Excel ?
I have a calendar with one day in each cell. I need to split a cell horizontally. Is this possible ? How do I split an individual cell ? hi, you can draw a line for corner to corner in a cell but you cannot "split" a cell like that ie not possible. >-----Original Message----- >I have a calendar with one day in each cell. I need to split a cell >horizontally. Is this possible ? How do I split an individual cell ? >. > Not splitting a cell--but putting information on multiple lines with in the same cell??? If that's ok, use alt-enter to force that new lin...
=if() statement -- test for any text in cell
Hi, I'm trying to convert the following statement . . . =if(A1=______ , "+", "") to . . . a search for any text, that might happen to be in cell A1. I don't know what the best option is for filling in the blank after A1= Thanks So much hor helping me if possible, Jim You could reverse your argument to =IF(A1="","","+") It will depend on whether you expect there to be anything else in the cell. It won't work if there is a number. There will be a way - I just ain't got it yet -- Russell...
how to print cells based on "Yes".
If the first cell in row 1 of Column "A" is "Yes", how do I get preselected contiguous or non contiguous cells in the same row to print? If the answer is "no", I do not want cells in that row to print. Thanks for anyones help! Francis Knight Hi Francis You can use Data>AutoFilter to filter on that column and print. Only the visible cells will print -- Regards Ron de Bruin http://www.rondebruin.nl "Francis Knight" <Francis Knight@discussions.microsoft.com> wrote in message news:43968233-50D0-4038-B31B-3C089DA99A7B@microsoft.com... &...
Sort
I am trying to sort my sales table, it doen't seem to work for me. A comment comes up: this operation requires the merged cells to be idendically sized. Can you help? Thanks Easiest way is to *unmerge* the cells, and then use "Center Across Selection" instead. <Format> <Cells> <Alignment> tab, And expand the "Horizontal" window, and choose, "Center Across Selection" -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ==============================...