Computer Support Forum

Solved: Trouble formatting date in excel spreadsheet

Question: Solved: Trouble formatting date in excel spreadsheet

Hi everyone, I was having some trouble with the format of the date in my excel document.

Basically what i want to do is type in for example todays date (Aug 18), and then when clicking below onto the next row, I would like to see the date come up above as (08/18/2005).

Instead the problem im getting is the date comes up as (08/18/2018)????

Ive tried right-clicking the entire column, where the date is being entered, and using "Format Cells" to set the appropriate date format. I have also verifed the date is set at 2005, so far nothing works.

I welcome any ideas/suggestions???

Thanx everyone

Relevance 100%
Preferred Solution: Solved: Trouble formatting date in excel spreadsheet

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Solved: Trouble formatting date in excel spreadsheet

10 more replies
Relevance 92.66%

I am trying to enter a whole number that looks like this "123" into a cell and have it look like this "00123". I've even tried to custom format it and I can't get it to work. Please help
 

Answer:Solved: Need help formatting Excel spreadsheet

Hello smeegle, select the cells that you want to format and the on the main menu select Format>Cells>Number>Custom
and type in the box
00000
this will give you the format that you have shown.
 

2 more replies
Relevance 82%

G'day,

I have another question regarding conditional formatting in Microsoft Excel!

I have a list of cells which contain text specifying fortnight periods. For example, one cell has '19th June, 2008 - 3rd July, 2008', another cell has '3rd July, 2008 - 17th July, 2008', and so on.

I was hoping to include a formula so that when today's date is within the range of one of those fortnight periods, the cell containing the fortnight period would take on a particular format. For example, today is the 1st of July, 2008, so the cell containing the '19th June, 2008 - 3rd July, 2008' cell would take on a red background indicating that it is the current fortnight.

If anyone is able to guide me to a solution, I would greatly appreciate it.
 

Answer:Solved: Conditional Formatting Dependant On Date Range In Microsoft Excel

16 more replies
Relevance 80.36%

I have a spreadsheet that shows all of the upcoming events at my work (I've attached a very basic example of the way the spreadsheet is set out).

When an event passes, I'd like the spreadsheet to automatically change the formatting of the rows that contain events that have already passed.

For example, today being 14.12.09, an event for yesterday (13.12.09) would be highlighted in red to show that the day has passed.

If anyone can provide any detail on how to achieve this, I would greatly appreciate it.
 

Answer:Solved: Conditional Formatting Based On Date Range In Microsoft Office Excel 2007

Select your data, then Format > Conditional Formatting.

Condition 1; Formula is:

=$A5<$D$2

Click Format > Patterns > red colour > OK.

Click OK.
 

2 more replies
Relevance 79.54%

With a single click or keyboard shortcut, I want to enter today's date in format DD/MM/YY in an excel spreadsheet.Can anyone help please

Answer:Todays date in Excel spreadsheet

Control + semi colon (Ctrl ;)

7 more replies
Relevance 79.13%

I AM TRYING TO AUTO SUM AS FOLLOWS: =SUM(C2:C5). WHEN I POPULATE CELLS C2 THRU C5 INDIVIDUALLY, CELL C6 DOES NOT RECOGNIZE THE INPUT AND DOES NOT CALCULATE AND/OR SHOW THE "ROLLING TOTAL" OR "FINAL TOTAL" IN C6. WHAT IS THE PROBLEM AND WHAT CAN I DO TO OBTAIN THE RESULT I NEED?

Answer:Excel 2003 spreadsheet formatting problem

If C6 contains =SUM(C2:C5) then it should work. Check for typo's, extra or missing characters and so forth. What, if anything, appears in C6 when you populate the cells C2 to C5? Always pop back and let us know the outcome - thanksmessage edited by Derek

8 more replies
Relevance 78.72%

I am trying to keep track of due dates on my spreadsheets. I have some dates that expire annually and the others biennially.

Answer:how can i track a due date on ms excel 2010 spreadsheet

You'll need to explain what you mean by "keep track of due dates".For example, are you asking to have them turn a specific color when they get within a month of coming due? Are you asking for pop up messages when a due date is imminent?We need details, my friend, details.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more replies
Relevance 77.9%

I have an Excel table that has records containing up to 32 different dates (D thru AI). I need to have a field populated to show the next upcoming date and a method to show the column title it was pulled from.

The only method I know is to create three fields;
(1) one for the Next Upcoming Date,
(2) another field next to it to populate with the Next Upcoming Date's Column Title, and
(3) a helper field to store information to help with the calculation to obtain the upcoming date's column title.

It would be better if the upcoming date and the date's column title were combined into one field.

The calculation I used (but haven't completed yet) is extremely long and I feel there must be a better way to achieve this but I'm not very experienced with Excel.

Next Upcoming Date calculation: =SMALL(IF(($D2:$AI2>TODAY()),$D2:$AI2),1)

Helper Column calculation: =MATCH(AJ2,D2:AI2,0)

Next Upcoming Date's Column Title calculation: =IF(AK2=1,D1,IF(AK2=2,E1,IF(AK2=3,F1,IF(AK2=4,G1,IF(AK2=5,H1,IF(AK2=6,I1,IF(AK2=7,J1,IF(AK2=8,K1))))))))
NOTE: This is the one I haven't finished because I will have to add an IF clause to it for each date. UGH!

Can anyone help me achieve this using a simpler method?
 

More replies
Relevance 76.67%

I have extracted data via sql from one application and need to upload it into a newer version of the same testing software. The problem: The data from the older version uses html tags for all formatting, when uploading the test cases to the new version, the tags load as literal text.

What I need is the formating to upload as it was formated within the older application.

Can anyone help? I am not a programmer.

Thank you.
 

More replies
Relevance 73.8%

I have a worksheet that I am currently using to keep track of Ebay items that I am bidding on. Column "A" is used for the "Start Date/Time" with Column "B" being the "End Date/Time" of the bid items. This morning my formatting for the date/time I'm using will not work for new data being entered in new XL rows. What is going on and what must I do to correct it? The only thing that has change is, I shut the computer down for approximately an hour while a thunder storm came through. When booted up, I found the problem.
 

Answer:Excel Date Formatting

6 more replies
Relevance 73.8%

Is there any way to format a date to change(color) after a specified date? I am looking to make a visual alarm for dated material. I want to be alerted when it reaches a specified date. I'm not sure if this can be done in Excel. Any advice would be helpful.
 

Answer:Date formatting in Excel

Use Conditional Formatting on the Format menu. If Cell Vaule Is, Greater Than, "Date" or "Cell Reference". Click the format button and you can change attributes assocaited with that cell.

Hope that Helps,

Drew
 

3 more replies
Relevance 73.8%

I am facing a problem regerding date formatting in Excel file. I have set the formatting properties for date as default for the whole DATE column but as i enter the date in a new row of the same DATE column, the file shows me a different format totally different from the above ones & unable to understand.
e.g

Before Hitting 'enter' After hitting 'enter'
DATE DATE

31-07-2007 31-07-2007
03-08-2007 39149
the blue coloured number is the result i am coming up with.

please help me sort out this problem
 

Answer:Date formatting in Excel

16 more replies
Relevance 73.8%

In a file we have hundreds of records with date format mm/dd/yy. In order to process we need the format to be mmddyy. Is there any easy way to format the date in this way?

Answer:date formatting in excel

Hi,Select the cells and FormatCells... - select 'Custom' and enter mmddyy in the 'Type:' box. (Note - no quotes around the mmddyy)If you want a macro to do it try this:Sub DateFormat()
Dim rngCell As Range

For Each rngCell In ActiveSheet.UsedRange
If IsDate(rngCell.Value) Then
rngCell.NumberFormat = "mmddyy"
End If
Next rngCellRegards

5 more replies
Relevance 73.8%

Hi I have a ton of standard dates that need to be trasformed from, for example "5/2/2009", into "20090502" format, please let me know if there a way to do that in Excel.

Answer:formatting DATE in Excel

Hi,Assuming that the dates such as "5/2/2009" have been recognized as dates by Excel, and are not just text, then all you need to do is to re-format the relevant cells.Select cell or cells, right-click, select 'Format Cells...'In the format dialog box select the number tabthen select 'Custom' (at the bottom of the list)In the 'Type:' box enter yyyyddmm (no quotation marks required)click OKIf the cell does not change to 20090502 then post back, and include what it does show.Regards

7 more replies
Relevance 72.98%

Hi, I'm new here so be kind!

I'd like to know how to format my date in Excel in the following format: "Su, 02.04.06" (i'm english, so that "dd, dd.mm.yy". I've developed the following formula to do it for me, but i'd like to do it using cell formatting as opposed to formulas:

=CONCATENATE(LEFT(TEXT(A1,"ddd"),2),", ",TEXT(A1,"dd.mm.yy"))

This assumes that the relevant date serial is in cell A1.

So can anyone help me understand the Excel custom formatting for this?

Thanks in advance,

Mark.
 

Answer:Excel date formatting issue

hi mb400,

Format the cell like this:

Format \ Cells \ Number \scroll down to Custom \ enter thefollowing in the box on the right immediately below Type:

ddd, dd.mm.yy

This will give you 'Sun' not 'Su' which is what I think you meant.
Replace the dots with slashes to get Sat, 01/04/06 etc.

You don't need your Concatenate formula.

lol
Hew
 

3 more replies
Relevance 72.98%

I have a quick question for an excel expert. I have date as 20040314How do I format it to be 03/14/2004?Please help.

Answer:Excel Date Formatting Question

Try this:= DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))Then copy, paste special, values.MIKEhttp://www.skeptic.com/

7 more replies
Relevance 72.98%

In previous versions of Excel if I have a column formatted for any date format, if I type in a 3 letter month code and the date (ie dec 16) the date is automatically formatted as December 16, 2013.
 
I just tried in Office 365 and it changes the date to December 1, 2016, dec 15 gets converted to December 1, 2015 etc.
 
Anyone have any idea how to get the shortcut functionality back, or if it is even possible?

More replies
Relevance 72.98%

I have an application that outputs comma delimited files (CSV) which I have been putting into Excel. However the date format that it outputted by the application is mm/dd/yyyy and does not include leading zeros:5/25/2010,,"ITEM 1","DESCRIPTION 1",-38.15,,5/25/2010,,"ITEM 2","DESCRIPTION 2",-36.52,,5/26/2010,,"ITEM 3","DESCRIPTION 3",2141.04,,5/26/2010,,"ITEM 4","DESCRIPTION 4",-264.02,,5/27/2010,,"ITEM 5","DESCRIPTION 5",-77.52,,5/27/2010,,"ITEM 6","DESCRIPTION 6",-774.76,,5/27/2010,,"ITEM 7","DESCRIPTION 7",-90.00,,5/28/2010,,"ITEM 8","DESCRIPTION 8",-4.34,,5/28/2010,,"ITEM 9","DESCRIPTION 9",-75.83,,5/28/2010,,"ITEM 10","DESCRIPTION 10",-77.14,,5/31/2010,,"ITEM 11","DESCRIPTION 11",-7.91,,5/31/2010,,"ITEM 12","DESCRIPTION 12",-3.71,,5/31/2010,,"ITEM 13","DESCRIPTION 13",-18.25,,5/31/2010,,"ITEM 14","DESCRIPTION 14",-26.50,,5/31/2010,,"ITEM 15","DESCRIPTION 15",-44.00,,6/1/2010,,"ITEM 16","DESCRIPTION 16",-86.43,,6/1/2010,,"ITEM 17","DESCRIPTION 17",-7.95,,6/2/2010,,"ITEM 18","DESCRIPTION 18",-6.43,,6/2/2010,,"ITEM 19","DESCRIPTION 19",-25.75,,5/27/2010,,"ITEM 20","DESCRIPTION 20",... Read more

Answer:Excel - Date Formatting Troubles

There is nothing about the CSV that is causing your issue, as it pastes in fine for me. Based on your description, Excel believes your contents are numbers instead of dates.

If you past the contents into Excel, does it automatically go into separate columns? Are you pasting the contents in Excel, then using Text to Columns or are you importing? When doing the conversion are you telling Excel you want the column to be a date in MDY format?

Try copying your code, then use Paste Special > Text in a new tab in Excel. Highlight column A1, Then use Data > Text to Columns. Choose Delimited, next; Check "Comma", next; click on column that has the dates in it, then click on the Date MYD button, then click Finish.

Do you still have a problem with 16-19 & 21 showing as numbers?

5 more replies
Relevance 72.98%

I have a column of dates (around 800 rows) with dates entered in as 2010/09/15 as date format. I need to convert these to GENERAL format and have them entered as 20100915.Does anyone know how I can do this quickly and easily? I'm not familiar with VBA at ALL!! If you could walk me through it step by step I'd really appreciate it!!Thank you all for your help!

Answer:Excel date 20100915 without formatting

Drag this formula down alongside your dates:=YEAR(A1)&TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00")Next, do a Copy...PasteSpecial...Values to eliminate the formula.Then format the cells as GeneralClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 72.98%

Hi,I am having a seemingly popular issue regarding the format of dates when exporting data to Excel. I have been trying to export timesheet data from a program called Replicon into Excel, however, once exported, I cannot change the format of the dates. I have tried the DATEVALUE and the Text-to-Columns suggestions and neither have worked.When exported to Excel, the dates are in this format - May 30, 2013Any other ideas? Thanks, I appreciate the help!

Answer:MS Excel date formatting after export

What happens if you try adding 0 to the dates?=A1+0Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 72.98%

Do any of you gurus know why when I enter my dates as 3/30/04 they automatically change to 38076? I have tried to format the cell the way I want it but they won't change. Did I hit a key command that changed the format without knowing it? The strange thing is when I began working with the spreadsheet it was fine. Then I entered a date a few days later and they all changed to the 38076 format.

Please help if you can.

jhorsley
 

Answer:Excel Date Formatting Problem

>> Did I hit a key command that changed the format without knowing it?

Do they show up right in the formula bar? (What I'm thinking is a long shot because it should've affected col widths too, but) you may have switched to Formula view.

Try pressing the key below ESC/left of [! over 1] while holding down CTRL.

If not, can you fix them by FormatPainting from an OK cell in another workbook?

HTH,
Andy
 

3 more replies
Relevance 72.98%

I am setting up an Excel spreadsheet to include the date of birth of past relatives. When I put a date of say 22/09/1938 it will convert to 22 September 1938. A problem arises when I insert a date from the 1800's. I tried to insert a date of 25/08/1886 and it stays as that instead of converting to 25 August 1886. Can anyone suggest a remedy?

Answer:Problem with date formatting in Excel

.I don't think the date function will work correctly with dates earlier than 01/01/1900. Perhaps someone with more knowledge of Excel may know a work around for this problem.Peter.

8 more replies
Relevance 72.98%

I'm trying to format the date entry in a cell (in MS Excel 2003) (e.g. from 20-Jan-2012 to 1/20/2012)using the "Format" feature, but it doesn't work. I wonder what's the problem.
 

Answer:Date Formatting in MS Excel 2003

Hi, welcome to the forum

Have you tried the Custom format?

Choose under Format Cell the Date format then click Custom and put is as you require, should work

I've attached a sample with three formats to explain it
 

3 more replies
Relevance 72.98%

I am exporting data from an online calendar as a csv file. After cleaning up the data I am then saving it as an Excel file to import into an Access table.Here is my dilemma:All of dates and times need to be formatted in ways that are not the formatting options in Excel or Access. How can I create rules to overwrite Excel formats to get the formatting I need?Thanks!

Answer:how to overwrite excel date formatting

Excel formats to get the formatting I need?What are the formats that you need?MIKEhttp://www.skeptic.com/

4 more replies
Relevance 72.98%

Greetings! I am working in a spreadsheet with variables for first name, last name, and birth date (mm/dd/yyyy). My goal is to create a new 10-digit variable that combines the first letter of the first name, first letter of the last name, and the eight digits of the birth date without the slashes.

I take care of the letters with the formula "=LEFT(A1,1)&LEFT(B1,1)." However, I am unable to change the mm/dd/yyyy format to mmddyyyy and add it to the 2 letters without the date changing to its serial equivalent (e.g., 5/16/1973 becomes 26800). I want to use a formula to create the new variable without manually changing all of the dates.

I would appreciate any assistance. Thank you!
 

Answer:Help requested with Excel date formatting

Resolution to date formatting

After further research, I discovered the following answer to my request:

=LEFT(A1,1)&LEFT(B1,1)&TEXT((MONTH(C1)),"00")&TEXT((DAY(C1)),"00")&YEAR(C1)

Per this formula, the date fields need to be formatted as text to allow for the leading zeros. All is well!
 

3 more replies
Relevance 72.16%

How can I format an Access report to look like a spreadsheet?

It is cuurently formatting like this:

Location
Task
Hours

Location
Task
Hours

I would like this layout.

Location Location Location
Task Task Task
Hours Hours Hours

I know there has to be a way to do it without exporting to Excel.
 

Answer:Solved: Formatting Access to spreadsheet look

9 more replies
Relevance 71.75%

I have a simple spreadsheet TBH for which two columns of data have date fields:

End date and Reminder date

I would like to set a conditional format using the icon sets for the reminder date as follows:

1) If the reminder date is less than 90 but greater than 60 days of the end date then show me a green icon

2) If the reminder date is less than 60 but greater than 30 days of the end date then show me a amber icon

2) If the reminder date is 30 days or less than end date then show me a red icon

I have come across ways of using today and dates in advance but not before...please can someone help me.?
 

Answer:Conditional Formatting for a date range in Excel 2010

Hi

To use the icon set you'll need to calculate the differences in a new column in the spreadsheet and then apply the conditional formatting to the values in that new column. So if your Due dates are a Col A and your reminder dates are in Col B in Col C use =A2-B2 and drag this down to the end of the list. Then apply the conditional format to Col C.

Here is an example spreadsheet.
 

1 more replies
Relevance 68.88%

Hi, I am having trouble formatting a column to show currency. It will not show a dollar sign or the correct decimals. It was working before and I don't know if I did something or what. Right now it shows a cross symbol which moves around like an arrow if you right click.I am hoping this is something simple. Can anyone help?

Answer:Excel trouble formatting currency

Right now it shows a cross symbol The cross symbol is the usual form the cursor takes when over the body of the spreadsheet.It is an arrow when over the Ribbon.See here for how the cursors behaves:http://www.rferro.com/CIS110/resour... It will not show a dollar sign or the correct decimals.What does it show?There are two formats that display the dollar sign with decimalsthey are Currency and Accounting.Have you tried either of them?MIKEhttp://www.skeptic.com/

9 more replies
Relevance 67.65%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 66.83%

Hi everyone! I'm trying to set up a spreadsheet for my husband who is a competitive swim coach.

He needs to be able to look at it and see if he tells the kids to swim 10 one minute sets, how many minutes that will take. (Gets more complicated than that of course! ) So I set up a spreadsheet with the seconds across the top and the number of sets along the side and did a formula series in the time increments. i.e.: :25, :50, etc...

The problem is when it gets to 25 seconds x's 3 -- it needs to read 1:15 - and all I can get is :75. It needs to be on minutes and seconds like a clock.

Anyone know how I can get it to read correctly??

Thanks!

Rebecca
 

Answer:Solved: Excel Spreadsheet

enter seconds in a cell as:

00:00:25
00:00:50

etc

then right click the cells and click Format Cells, select Custom and enter mm:ss to just show minutes and seconds

is this what you meant?
 

2 more replies
Relevance 66.83%

Every month for several years I have accessed my own spreadsheet offline from desktop icon.
To utter consternation and increasing frustration, today OPEN brought up the file BUT can do nothing with it.
Same results with backups on different drives.

Consequently told browse to find program from which can OPEN. Obvious choice MSN Excel. Result: "Cannot find any version ...to open file for edit".

Further on I installed msn xlviewer. In Open process came box, "Office Source Engine (Process ID: 2120) running. Need to Close". How/where to do so no idea. I am on Vista Ultimate with !E7; note not included in list for this item.

FYI, I only have Office 2002 (with Excel 2002), but cannot even remember last time I used it for anything. Also note many MSN references to Office 2003, but hardly ever 2002.
Point is, HOW CAN I GET BACK WHERE I WAS & NOT BE REFERRED TO NEED FOR A PROGRAM (EXCEL)?
Only secondarily (and reluctantly) interested in making Excel function to restore ability to use my spreadsheet.

I will be most grateful for help in this unexpected problem.
 

Answer:Solved: Spreadsheet with/without MSN Excel

16 more replies
Relevance 66.83%

I have a spreadsheet that I need to sort by column (P) which has formulas in it that added data from each row across. I checked all my formulas and they are correct. I had to delete 3 separate rows so I highlighted each one and went to "insert/delete rows, columns" etc. and deleted them there. I then went to DATA and sorted by column (P) from highest value to lowest value and that computed correctly. The data in each row however shifted so each row is incorrect. I don't know how to fix this. Can someone PLEASE help!! This is the only spreadsheet I work with and have never had a problem. I know I must've done something wrong but I've been working on this for 2 days. Did I mention I hate excel due to my ignorance of how it works??? HELP!! Thanks in advance.
 

Answer:Solved: Excel Spreadsheet HELP!!

13 more replies
Relevance 66.83%

I have uploaded a sample of the spreadsheet I am trying to modify and I kinda have it working the way I want and I need these costs broken out the way I have them. I will try to explain what I want the best I can so bear with me.

I want "Total" to capture if it is an expense or income. (right now it is not)

I want "Balance" to reflect if it is an expense or income. (right now it is not)

I want any unpopulated cells to show nothing or ""

This is probably simple for an excel wizard but I only know excel good enough to get into trouble!

Any help is appreciated, thanks, Joe
 

Answer:Solved: Can someone help me with this Excel spreadsheet?

16 more replies
Relevance 66.01%

I have a large spreadsheet in which data is added weekly. At the top of the page I have several "windows" in a set of frozen rows which display results from the various columns. I would like to know if it is possible to have a function in a cell which would make a calculation using the last added row of data. For example, to compare a price to the previous week or to the highest figure in the column.

Regards

Daffer
 

Answer:Solved: Excel 2003 spreadsheet

10 more replies
Relevance 66.01%

Hello,

I am having a hard time getting my formula right to automatically work out what percentage will be earned on sales, which is dependant on what percentage of their annual target has been reached.

I have got the formula to work for one of the commission rates but my maths or formula isn't working on any of the other rates.... Could someone please have a look at my attached spreadsheet and correct the formula!

Very much appreciate your time

Thanks
 

Answer:Solved: Excel Spreadsheet - Commissions

Never Mind, I figured it out myself!!!
 

1 more replies
Relevance 66.01%

I have an Excel spreadsheet, shared over the network and anytime I try to make and save changes over the network, it tells me that the spreadsheet is read only.

I've checked the attributes of the folder, and the file itself, and it does not indicate that it is read only

I'm using Office 2007 Ultimate and Windows Vista on the machine I'm doing the editing on, and Office 2003 and Windows XP Home on the laptop where the share folder/file resides, if that makes a difference.

I've also checked permissions, and it's checked for Unrestricted Access
 

Answer:Solved: Excel spreadsheet says that it is read only

15 more replies
Relevance 66.01%

Hi,

I am using a code to compare two spreadsheet.
One of the function is to copy the modified records in a different worksheet, highlighting changes element in red.
The script works, but ONLY when the cell is modified to "blank"(no value), I get coloured in red the upper cell instead of the correct one.

Here the script, any help?

Sub GDV()
Dim WsA As Worksheet, WsB As Worksheet, WsC As Worksheet, WsD As Worksheet, WsE As Worksheet
Dim rFind As Range, c As Range
Dim I As Integer, ColCnt As Integer

Set WsA = Worksheets("OldExport")
Set WsB = Worksheets("NewExport")
Set WsC = Worksheets("Changes")
Set WsD = Worksheets("PosDeleted")
Set WsE = Worksheets("PosAdded")

ColCnt = WsA.Cells(1, Columns.Count).End(xlToLeft).Column

With CreateObject("Scripting.Dictionary")
For Each c In WsA.Range("A2", WsA.Range("A" & Rows.Count).End(xlUp))
If Not .exists(c.Value) Then
.Add c.Value, False
Set rFind = WsB.Columns(1).Find(What:=c.Value, LookIn:=xlValues)
If Not rFind Is Nothing Then
For I = 1 To ColCnt
If Not c.Offset(, I - 1) = WsB.Cells(rFind.Row, I) Then
If .Item(c.Value) = False Then
rFind.Resize(1, ColCnt).Copy WsC.Range("A" & Rows.Count).End(xlUp).Offset(1)
.Item(c.Value) = True
End If
WsC.Cells(Rows.Count, I).End(xlUp).Interior.ColorIndex = 3
End If
Next I
Else
MsgBox c.Value & " PosID has been canceled!"
c.Resize(1, ColCnt).Copy WsD.Range("A" &am... Read more

Answer:Solved: Excel VBA comparing spreadsheet

to who may be interested
I found a solution (workaround) here:
http://www.excelforum.com/excel-pro...adsheet.html?p=2818323&viewfull=1#post2818323
 

3 more replies
Relevance 66.01%

Just got this after downloading google drive. I am new to excel. I enter a number in a cell and goes in. My question is this. How do you move that number say to the centre of the cell? Thanks
 

Answer:Solved: google excel spreadsheet

Click the Horizontal icon then select Center:
 

3 more replies
Relevance 66.01%

howd'y

I'm just wondering how to convert this spread sheet file (.wks) to excel 2007..

i know thats a lot of years in between the two programs but there must be a way to convert it.. or is there?

so i do need to open and re use the spread sheet but i don't know how i got that program in the first place.. so yer.

ive tried to convert it using excel 07' and was a fail. only came up with all differnt characters.

thanks for reading.
 

Answer:Solved: converting spreadsheet (.wks) to excel 07

Is this of help ??? http://forums.techguy.org/business-applications/779694-solved-works-vs-excel-formats.html
 

3 more replies
Relevance 66.01%

We are playing a silly game that is teaching me about trading on the Stock Market. I am trying to figure out how to work my spreadsheet up so that it will tell me when to buy and when to sell my stocks in certain companies. I have the basic spreadsheet with category, business, bought, sold, buy, sell but I am having a problem. I need a column to automatically tell me the lowest amount seen in "buy" and the highest amount seen in "sell". Is there a way to do this? I used to think I was proficient in Excel but this gives new meaning to "tearing your hair out" for me. I'm sure that there are pivot tables and all sorts of other things to utilize but I'm not sure how when I'm only wanting to see things a certain way. Can you help?
 

Answer:Solved: Excel Help with trading spreadsheet

11 more replies
Relevance 65.6%

Hi,

After a power outage a particularly important file gets the error "Excel cannot open the file.xlsx beacause the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. The file format has not changed. Is there a safe free program that can uncorrupt a file? I have tried Open and repair, changing the format, and using open office. Also tried using recova but the file was not lost.
 

Answer:Solved: Excel 2007 spreadsheet gets error "Excel cannot open the file .xlsx.." How ca

https://support.microsoft.com/en-us/kb/820741

and 5 ways are shown here http://www.wikihow.com/Recover-a-Corrupt-Excel-File
 

2 more replies
Relevance 65.19%

I have an Excel spreadsheet split into two ranges. I accidentally sorted only one range when I thought I was sorting the whole spreadsheet, then made other changes and saved the file. I could not undo back far enough. Is there anyway to re-sort so I can re-connect the two ranges and put them in the correct order?
 

Answer:Solved: Excel Spreadsheet Accidentally Mis-sorted

Hi, this is something that is 'back to the drawing board'
There is no way that Excel knows what the previous sorting was.
Your only option is to lookup the file using Windows Explorer, righclick on the file and if you're using Windows 7 or later there is a tab named 'Previous Versions' you can then see if you can find a file before you carried this out and recover it.

Good luck
 

2 more replies
Relevance 65.19%

I have a spreadsheet which contains entries in one particular column than need to be replaced and I am looking for a way to automate this task. In the column there are 4-digit numbers starting with "0001" on up. Each number stands for a given department, but the reader of the document would like to see the department name listed as well. So in other words, I need to replace "0001" with "0001 - Dept A," "0002" with "0002 - Dept B" and so forth.

I was going to build a macro for one replacement, then edit the corresponding VBA code to automate the rest of the list. The problem seems to be the leading zeroes!!! I can search for "0123" but I cannot search and replace for "0123" because then Excel finds nothing to replace. It will execute a search and replace for the string "123" but of course that is useless.

I tried reformatting the cells but that does not work. The original cells still carry the leading zero but the replace request still turns up nothing. Anyway, I do not really want to reformat anything, just replace.

This looked so simple at first. I am not a VBA guru by any means and I need to get this done as I learn. Could someone suggest some VBA code that would do this directly without building on a macro?
 

Answer:Solved: Replacing data in an Excel spreadsheet

16 more replies
Relevance 65.19%

I have a data base that is linked to an excel spreadsheet. I want to be able to open that sheet up by pushing a button on a menu form in access.

Currently I have a button that opens up the application but then I have to open the excel file after excel opens.

How can I get the file Quick Guide.xls to open right up by pressing a button on the access menu?
 

Answer:Solved: Access - Open Excel Spreadsheet

6 more replies
Relevance 65.19%

I was given a password for a locked Excel spreadsheet, but when I go to TOOLS>PROTECTION the "Unprotect Sheet" option is greyed out so I can't even get to a point where I can enter the password. Is there anything I can do to get around this?

Thanks.
 

Answer:Solved: Unlocking Spreadsheet in Excel 2003

Never mind. I realized that I had to have sharing turned off before I could unprotect it.
 

2 more replies
Relevance 65.19%

I have a spreadsheet with almost 3,000 lines in it that I'd like to add to a Dashboard in Excel - but I can't figure out how to do this within excel.

I know how to embed a spreadsheet from excel into a word document, HTML document, etc - but not within the same program.

I want a dashboard "rectangle" that is scrollable within itself to show all 3,000 lines of data without taking up more than 30 standard-sized rows.

This is an Asset Management spreadsheet with several dashboard items as pie charts, graphs, etc - but I'd also like all of the PC names listed on the same page without taking up all the space it normally would - it needs to be the same size as the other graphs.

Any help is appreciated! Thank you!!

-Drake
 

Answer:Solved: Embed a Spreadsheet in Excel (Dashboard)?

10 more replies
Relevance 65.19%

I have raised an Excel 2003 spreadsheet and wanted to show grid lines in it. I have followed the routine "Tools/Options/View" and made sure the grid lines box is checked, but when I print the document none are showing. Can anyone suggest how I can correct his please?
 

Answer:Solved: Gridlines do not show in Excel spreadsheet

9 more replies
Relevance 65.19%

whenever I try to send a spreadsheet using th "send to > mail recipient" excel freezes up?
 

Answer:Solved: Excel freezes up when trying to send a spreadsheet

How about other programs? Does Word do the same thing? What is your email client? Do you have any add-ins installed? Try opening Excel in safe mode (using the switch "/safe" from the run prompt: hit the flying windows button, click Run, type "excel.exe /safe") and trying it.
 

1 more replies
Relevance 65.19%

I have an excel spreadsheet I am entering data in on a monthly basis. I have to add names in the "A" column and then numbers in subsequent columns. I hope that makes sense as I know NOTHING about Excel except to enter data and maybe a SUM formula here & there. SO... I'm adding the new names and I get to line 97 and NOTHING goes into the field. It shows on top in the formula bar but that's it. My husband needs this info for a meeting tomorrow. Can anyone help...PLEASE??!!!

Thanks in advance
 

Answer:Solved: Excel Spreadsheet Help needed ASAP

12 more replies
Relevance 65.19%

I am thanking everyone in advance for their help. I've been trying to figure this out forever and I'm sure there is a way (I hope).

Attached is a photo of what I am trying to work on. I'm trying to keep stats of all of mine and my friends gears of war 2 games.

Gametype is either Execution or Warzone.
Map is the map played that game.
Win/Loss was if we won or loss.
Columns E-K have three numbers #/#/# The first number is how many kills the person got, the second number is how many times the person died and the third is what place the took on the team (1st-5th).

To make this easier, I will be basing the equation on my stats (I DontHate I, Column E)

Here is the first equation I'm trying to figure out:
IF the gametype is Execution(A) then I want the results of E to go to M2
IF the gametype is Warzone(A) then I want the results of E to go to M3
This is the equation I'm trying to figure out for column E is as follows:

I want the results of E to show up in M2 or M3 depending on the above IF equation. I want the sum of all the Execution Kill/Deaths to be put in M2 (based on spreadsheet right now that would be 21/10) I want the sum of all the Warzone Kill/Deaths to be put in M3 (24/10). I then want M2 and M3 added together and the results to be put in M4(I know this one is simple, just trying to explain EXACTLY what I'm trying to accomplish)
If there is a Null listed, that means the person didn't play and I want the equation to ignore that ... Read more

Answer:Solved: Need help with a couple MS Excel Spreadsheet equations.

16 more replies
Relevance 64.37%

Hi,

I have Excel 2003 SP2 on a users XP SP1 PC. When they have a few spreadsheets open they appear on the taskbar but when they try to tab between them or maximise them nothing happens. They can open the spreadsheets by going into the current one and selecting Windows and selecting the one they want.

There are always another number of applications running on the PC at the time but they can tab fine through them it just seems to be excel related.

thanks in advance for any assistance,
Ed
 

Answer:Solved: Unable to maximise excel spreadsheet from the taskbar

Quite odd - they appear on the taskbar but nothing happens when they click on them? Or do they not appear on the taskbar? The latter is an easy fix, go to Tools, Options, View, Show Windows in Taskbar.
 

2 more replies
Relevance 64.37%

Does anyone know how to open an Excel 2007 spreadsheet with OpenOffice? The file's extension is .xlsx
I did right click on the file and then click open with OpenOffice. At that stage it gives me a huge list of names to click to open and I am unsure which one to choose. Thanks for the help.
 

Answer:Solved: Excel 2007 spreadsheet how to open with OpenOffice?

Do you have any version(s) of Excel? If so, get this.

Failing that, you could open in Excel Viewer, then copy the data & paste to Calc.

I also found this -- although can't vouch for it personally.

HTH
 

2 more replies
Relevance 64.37%

Greetings forum,

I have an Access database main table that I try to update at least monthly with an Excel spreadsheet feed. The spreadsheet formatting is always the same, with occasional additions or deletions of rows...those changes are what I am trying to update the database with. The rows represent personnel and their personal information. People come and go...blah, blah, blah.

The problem is when I run the new reports and save them in Excel. I save them in the same place and rewrite over the old file with the same file name. Then I try to import external data into the Access database using this overwritten file as an append query. It worked the first time I set it up, but when I try to use it again as a saved import, I get an invalid file name for the overwritten file. Microsoft appears to be adding a hidden appendage to the file name when I overwrite the original file.

Has anyone had experience with this problem before?
 

Answer:Solved: Importing Excel spreadsheet into Access problem

I haven't heard of this problem.
Perhaps you could tackle it differently. I normally just import the file to a temporary table (which requires no save import) and then use an Append query to add the records to the table and then a delete query to clear the temporary table.
 

3 more replies
Relevance 64.37%

Hi all,
being a non-techie I apologize in advance for any ignorance I display
I searched the forums and found a similar question dating from 2005 but not a real solution. So here it goes:
The aim of the exercise is to divide the data from within an excel spreadsheet into many spreadsheets, each of which needs to be sent by email to different participants.

The background is as follows: the big spreadsheet is delivered to me by a statistical department that makes a selection of households to take part in a survey. These households are coming from all over the country and are identified by a three digit number (Column U). Next to the households there is the interviewer and his or her email address . The excel file is already ordered on the interviewers (Columns R and S).
Is there a way to automate this process?
A sample zip file with the spreadsheet to be divided is included
Thanks for any help.
Eddy
 

Answer:Solved: How to divide an excel spreadsheet into multiple spreadsheets

16 more replies
Relevance 64.37%

Hi,
I recently set-up one of my first spreadsheets and when I need to print it doesn't the gridlines. I trhink I have the print setup set for printing guidelines, yet when I do the print preview it only shows the numbers in the columns without the gridlines.
Thank you for any help.
 

Answer:Solved: MS Excel & printing spreadsheet grid lines.

xpshovelhd,

The directions may be a little different depending on which Outlook version you have but in Outlook 2007's Print Preview window, click the Page Setup button and go to the Sheet tab.

Make sure the Gridlines box is checked here.
 

3 more replies
Relevance 64.37%

Hello all,

I don't know if I'm posting in the right forum. This is an Excel problem I'm having at work.

I'm not overly Excel savvy, but I know enough to get by at work, and fix most problems myself.

I may just have some post maternity baby brain, but I can't fix this problem.

My manager has a spreadsheet with about 5 tables in, each with monthly totals for different areas, and each cell pulls data from half a dozen different spreadsheets and work books. Pulling cell refs from so many things makes it slow on opening, and management asked me to find a way to have individual months formulas calculate on the click of a button, rather than on opening the spreadsheet.

I did a little test sheet which worked fine, but when I did the same on managements spreadsheet it went a bit pete tong. I recorded a macro to input the formula, which when I recorded it, is fine - all totals are correct, but when I run the macro only half the formula appears.

I even tried a different method; taking out =sum, so the formula doesnt work on opening and having a macro that simply pops that bit back in, but half the formula disappears when I run it, even though the macro doesn't touch the rest of the formula.

Any clue as to why this happens, or am I just being a ditz and over complicating things?

Sorry if this is a bit confusing - I'm not great with excel, and I'm now at home without the spreadsheet so can't go into much detail.

Ta muchly.
 

Answer:Solved: Excel Spreadsheet macro/formula query

6 more replies
Relevance 63.96%

Running WinXP and Office 2003

Hello. This is similar to http://forums.techguy.org/business-applications/257309-exporting-word-form-data-excel.html

I tried using and tweaking that example but am having a hard time because there are several different things.

I will be having hundreds of word documents. The first page on each doc is just a form that a user fills out on the computer. I would like some help with the macro that would run and process a folder of these docs.

I would also like to have 3 sheets in the one excel file. The doc file will be filled out generally by 3 departments. Is there a way to have a drop menu in the doc to indicate which dept and also have this transfer to their respective sheets in the excel file? The rest of the doc fields should be the same. Also I need the hyperlink to the file in their respective cell.

Sorry I am new to code in excel and am starting to learn its power. Thanks for your help!
 

Answer:Solved: Exporting Word Form Data to Excel Spreadsheet

16 more replies
Relevance 63.96%

Hi All

I have a spreadsheet full of numbers formatted as currency. Some dollar amounts are positive and some are negative. I want all the negative numbers to be in red font. Instead of me searching for all the negative numbers (they are in the hundreds) and turning them all red manually, is there a simple macro that can do the trick? This will save me a lot of time. Since the numbers change from negative to positive and vice versa and the number of rows and columns also change, I'm having a hard time programming it.

Has anyone out there written a macro than can do this?

Thanks

Mario
 

Answer:Solved: Turning negative numbers to red font in an Excel spreadsheet

It's built into Excel - just highlight the cells, go to format cells.
Go to Currency. There are 2 boxes on the right - the lower one allows you to have negative numbers in parentheses ($3.00) or red $3.00 or a combination of those two or with a minus sign.
 

3 more replies
Relevance 63.96%

Hey everyone,

I'm trying to stretch my resources and minimize some data entry, using the tools at hand. However, I've hit a bit of a wall on inspiration - looking for some ideas.

I have an Excel Spreadsheet (about 8 worksheets) that I use to log some daily activities - however, because I have a lousy memory, I like to email myself from the road with the notes for my Log. Then, when I return to the office, I either re-type (or copy/paste) my email notes into the appropriate sections of my log file.

What I'd LIKE to do is create a form and rule in Outlook that would auto-fill my Excel worksheets based on the text of my email. I'm not worried about Triggers (the rules setup on that end is simple enough for me), I'm more looking for the best way to have Outlook interact with Excel in this way.

I send emails from the road on my Android phone.

Any ideas? Thanks!
 

Answer:Solved: Outlook Email to Auto-Fill Excel spreadsheet

8 more replies
Relevance 63.96%

I am pretty good with most commonly used Excel functions but this is one I can't get. Please help!!!!!

I would like to re-format the way the spreadsheet is set up on sheet 1 to look like it is on sheet 2. I would like all contacts to have all of their info read left to right starting in column A and the move across to the right. So in the end all Business names would be listed in column A and then all owners would be listed in column B. The only way I can figure to do this is to copy and paste each individual customers information.

Maybe there is some sort of formula to build to make things read the way I want? Or I am just not coping and pasting correctly?

Please help!!!!!
 

Answer:Solved: Switching Axis in a poorly setup Excel Spreadsheet

7 more replies
Relevance 63.96%

I am usually savvy about Windows, but this one has me stumped. I have taken on a volunteer project that requires me to add volunteer hours in an Excel Pivot Table. I am having calculation issues which I believe to be caused by a limit on the maximun number of rows. Reading Excel help is just confusing me more. My mentor showed me how to do this once, but I did not take notes and now I am a lost puppy. Given the above information, does anyone have a solution? Thanks for your time.
 

Answer:Solved: Excel 2003 help, Edit number of rows in a spreadsheet

7 more replies
Relevance 63.96%

Hi, I have Windows 7, Excel 2007. I'm printing an existing table. When I "print preview", it shows like it's supposed to, but when I print it out on my network printer, HP Laserjet 3390, the header & header title rows are not lined up & print weird. However, when I print them on the HP Officejet J5750, it prints out exactly as in "print preview". I've attached a word doc that shows both versions (I hope it's attached). Ideas anyone?
 

Answer:Solved: Excel 2007 spreadsheet doesn't print as viewed

16 more replies
Relevance 63.55%

Hi there,

I have a dbase created in the UK and I am storing dates in my database and I am getting a funny result when I perform a search and filter on from and to dates.

I have the following data:

Car 1 from 01/01/2006 - 30/01/2006
Car 2 from 01/02/2006 - 28/02/2006
Car 3 from 01/03/2006 - 30/03/2006

When I do a filter to report on cars from 01/01/2006 - 20/01/2006 only the first row is retrieved, however if I redo to the search from 02/01/2006 to 20/01/2006 expecting again only the first row. I instead get car 1 and car 2 reporting back as a match. It seems to be swopping the month round for some reason!?

If anyone could help me I would be eternally greateful.

I cannot for the life of me get this to work. I attach the code below:

On Error GoTo Err_report_Click

Dim stDocName As String
Dim stWhere As String

If CDate(searchstartdate) > CDate(searchenddate) Then
MsgBox "Please Enter an End Date Before the Start Date", vbCritical + vbOKOnly, "Report Error"
Else
stDocName = "DriverHistory"
stWhere = "driverNo =" & Me![driverNo]

' Check if we have a search start date
If Me![searchstartdate] <> "" Then
' This clause will be ANDed to the existing clause
stWhere = stWhere & " and "

' now check if we have and end search date
If Me![searchenddate] <> "" Then
' We have and end search date so we need to bracket the whole expression
stWhere = stWhere & "... Read more

Answer:Solved: Access 2003 Date Formatting Problem? - Can anyone help?

6 more replies
Relevance 63.14%

I need to write a macro that will copy data from one spreadsheet into a separate spreadsheet (not in the same file). Here is the scenario...

On the first spreadsheet I have a customer's name in one column and a customer's address in another column. On a separate spreadsheet a have those same customer's names without the addresses. On the second spreadsheet the customer's name may be listed more than once. What I need to do is add the corresponding address to the second spreadsheet beside the name of the customer (identical data from both spreadsheets). The reason copy and paste won't work is because the customer may appear numerous times on the second spreadsheet and their are approximately 50,000 rows of data.

The macro needs to basically say IF Spreadsheet 1, Column A (customer) equals Spreadsheet 2, Column G (customer) then ADD/REPLACE info from Spreadsheet 1, Column B (address) to Spreadsheet 2, Column H (currently blank). I just can't figure out how to make it do that. ANY HELP ANYONE CAN GIVE IS APPRECIATED!
 

Answer:Solved: Need Help Writing a Macro for Excel to Move Data From One Spreadsheet to Anot

7 more replies
Relevance 63.14%

I am fairly new to VBA and this is my first post so please be patient with me. I require the assistance of other vba programmers in coding the following:

1. The user selects a Report ID from the first List box "Report ID's", clicks the "Add Single" button, then clicks the "Print Report" button. - (I have coded this)

2. The code for the print report button needs to take the report id from the "print report" List box and looks in the "Report ID" column from the "Reports" sheet for a match. Once found, it grabs the corresponding compilation no. for that Report ID.

3. It then uses that compilation ID no. to search in the "Compilations" worksheet for a match; it then grabs the corresponding Keyword for that compilation ID/no.
4. Lastly it uses the keyword to search for a match in both "Keywords1" and "Keywords2" columns from the "Exhibits" worksheet. Once a match has been found in any of the columns, it then grabs that entire row/exhibit and prints it out in it out in a word document, with correct formatting, Labels and headings, for the user to edit.

I would appreciate any assistance that can be given and strongly emphasise that i am not looking for someone to code everything for me, instead to take patience and go through a process step by step if possible.

Thankyou to any users who provide assistance, your patience and time taken to help has been duely noted and much ap... Read more

Answer:Solved: Create a report in word based on excel spreadsheet data

16 more replies
Relevance 62.32%

Hello,

I've been using this site in the past while looking for fixes to various problems, recently registered and tried to help a little in my free time but now I am facing rather a big problem with which I am not sure what to do.

Firstly,
Please excuse me if you will deem it as a waste of time as I found few general ideas on how to go about fixing it:
http://forums.techguy.org/business-applications/1007467-excel-2003-wont-open-some.html post #9
http://forums.techarena.in/ms-office-support/749154-2.htm
http://forums.techarena.in/ms-office-support/1416245.htm
http://support.microsoft.com/kb/2570623

I am posting as I'm not sure that those fixes will work and I have 4 users from Finance Departament at a finance year end who desperately need to fix the problem.
What happens is, they have have a spreadsheet (.xls) on a shared network drive which is crashing upon opening if sharing is turned on. If sharing is disabled one person can work on a document.
2 users have Office 2007, other 2 use Office 2003. 3 of them are facing this problem, one with 2007 and two with 2003.

I am really clueless and would appreciate any troubleshooting ideas and/or potential fixes.
For the time being I'm off to remove KB2502786 and kb2541025 updates from user's machines (I they are on) and will post results. Hopefully I will get some replies in the meantime

Thanks for all the help in advance

[EDIT1]
Just checked with one affected user who is using Office 2007. KB2502786 and kb2... Read more

Answer:Solved: Excel 2003 spreadsheet crashes when opened from network location with sharing

9 more replies
Relevance 62.32%

I am currently working on a spreadsheet for a complex budget, and the spreadsheet is constantly expanding by way of additional columns (a new column for each day).

At the end of the columns that represent each day, there are five columns that show calculations based on the entries in the columns that are before them. One of those columns calculates the average of every entry in that row. In the sixth column, we want to be able to calculate the average of the last four day entries without having to manually adjust the range each time we expand the spreadsheet.

That's probably not particularly clear, so in summary, we have a column for every day of the last twelve months. Each cell shows a dollar amount. At the end of each row, we have five columns that display various calculations for each row. At the end of those calculations, we want a further calculation that will show us only the previous four days.

Needless to say, every time we add a column for a new day, the range (showing the average of the previous four days) expands to show the average for the last five days. Rather than this happening, we need the range to move forward, rather than expanding, essentially following the cell at the end of the row that shows the average of those four days.

In essence, we need a cell to calculate the average of four cells that are five cells behind it, without ever expanding its field of reference.

Apologies if this description isn't particularly helpful...it's a dif... Read more

Answer:Solved: Relative Cell Ranges In An Expanding Spreadsheet (Microsoft Office Excel)

8 more replies
Relevance 62.32%

Hi,

Im quite new to this excel programming thing and could really do with some help.

I need to send an automated email to 3 recipients (always the same 3 email addresses) when a number (formatted from a countdown of days to go) is 10 or less. Also i need a different automated email to be sent when a date is manually entered into a different cell.

I have managed to get the current date and time on my spreadsheet and used the format to work out the days to go to the deadline.

I have looked over all different types of forums but unfortunately because i'm still very green when it comes to excel i get lost and confused when trying to do this.

Is there anyone out there who can treat me as an alien and help me through this step by step.???
 

Answer:Solved: Send an automated email (outlook) from Excel spreadsheet dependent upon comle

10 more replies
Relevance 62.32%

Hi,

I have several completed templates that I need to summarize into a spreadsheet.

Is there a way for me to list down the files and the location of the excel spreadsheets and run a macro for copying the data I need from the files listed and pasting them into a spreadsheet format?

Here's what I am doing now, manually.

1. Open file1
2. Copy B2
3. Paste to SummaryFile on B1
4. Copy C2
5. Paste to SummaryFile on B2
6. Copy D2
7. Paste to SummaryFile on B3
8. Close file1

9. Open file2
10. Copy B2
11. Paste to SummaryFile on C1
12. Copy C2
13. Paste to SummaryFile on C2
14. Copy D2
15. Paste to SummaryFile on C3
16. Close file 2

I hope somebody can help.

Thanks,
Monica
 

Answer:Solved: Excel Macro needed for creating a spreadsheet from data on several but similar files.

7 more replies
Relevance 60.27%

I am entering $50.50 into a cell and 001 into others. Excel 2003 changes the $50.50 to $50.5 and the 001 to simply 1.

How do I change it so that it always retains the entries exactly as I typed them in?
 

Answer:Formatting Cells in a Spreadsheet

11 more replies
Relevance 60.27%

Hello,

I am creating a command button in Excel. I am trying to clean up some formatting issues, but am having some difficulty.

I would like to remove and background colors on a particular sheet as well as any borders.

I am using the followig for the background color:
Code:
ws.Range("A1:A10").Interior.Color = xlAutomatic
The issue there is that it completely changes everything to white. I would like it if I could still see the default (or normal) gridlines.

I am trying to remove the borders with:
Code:
ws.Range("A1:A10").Borders.LineStyle = xlAutomatic
That is just erroring out.

"Unable to set the border property of the linestyle propoerty of the borders class".

Any suggestions?

Thank you,
Roger
 

Answer:Solved: Excel Formatting

Range("A1:A10").Interior.ColorIndex = xlNone
Range("A1:A10").Borders.LineStyle = xlNone
 

2 more replies
Relevance 59.86%

I want to format an invoice so that the dates of the month change to the current month just by entering the date one time in the first cell. The invoice would include all the days of the month. How do I do this? Thanks for your help.

Answer:Formatting calendar dates on spreadsheet

Hi,You can try this:In cell A1 enter =DATE(YEAR(TODAY()),MONTH(TODAY()),1)This will return the first day of the current month & yearor just enter the date for the first of the required month.The date must be in a format Excel recognizes as a date.In cell A2 enter =A1+1This will give the second of the monthDrag the formula in cell A2 down to A28This will give the first 28 days.In cell A29 enter this formula: =IF(DAY($A$1+28)<>29,"",A28+1)This tests whether 28 days after the 1st of the month is 29 (or 1 - i.e. 1st of next month)If it isn't 29, it returns "", else it returns the dateThe same approach is used for A30 and A31A30 =IF(DAY($A$1+29)<>30,"",A29+1)A31 =IF(DAY($A$1+30)<>31,"",A30+1) Format all cells with the required date format.Hope this is the sort of thing you were looking for.Regards

4 more replies
Relevance 59.86%

Hi,

Does anyone out there know how to protect ONLY the formatting of an MS works spreadsheet. By this I mean I would like to be able to edit (cut and paste etc.) only the values in the cells and not the formatting. e.g if I have two cells one with a blue border and containing the value 1 and the other a red border containing the value 2. I would like to be able to select the first cell then hit copy, then select the second cell and hit paste. The result I would like would be two cells now both containing the value '1' but still retaining there original border colours, what I am getting is two cells containing '1' but now both have blue borders. i.e. when I hit copy on the first cell it copies not just its contents but also its formatting. Is there anyway that I can lock the formatting so that copy and paste operations only affect the cell contents. I ask as I have created a quite complicated template for accounting with a lot of varied formatting on different cells and I would like to be able to cut and paste values between any pair of cells without altering the formatting.
 

Answer:Protecting formatting in MS works spreadsheet

Instead of cutting and pasting by clicking the cell in the worksheet: select the cell, double click the contents in the formula bar, copy/cut, click the new cell, paste. You can cut down on the time by right clicking too.

OR

do your copy as normal, right click on the cell you want it to go into, select Paste Special and choose only what you want pasted. You can paste in the 'values' and not the formatting.
 

3 more replies
Relevance 59.45%

I am trying to open a spreadsheet that was created in Excel 97 using Excel 2000. The links and macros that are on the sheet seem to prevent it from opening. There are a load of error messages but I just cannot get the thing open. I have searched so many places, found the same problem but no answer.

Can anyone help?

Leanne
 

Answer:Excel 2000 problem when opening Excel 97 spreadsheet with links and macros

Welcome to TSG.
Can this file be opened fine in Excel 97? Any chance that it is corrupted?
What are some of the error messages you are getting?

 

3 more replies
Relevance 59.45%

Hi Folks,
I have a valued Excel 2003 spreadsheet that, I can currently only open in Excel 2007. However, an error message has been displayed that Excel was unable to read some of the document and it did a recovery. The file has now lost a few key sections. I have attached the error log below.

Any suggestions please?

Thanks,
Dave

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error033640_01.xml</logFileName>

<summary>Errors were detected in file 'C:\Users\User\Documents\tool sourcing.xls'</summary>
-<additionalInfo>

<info>Excel recovered your formulas and cell values, but some data may have been lost.</info>

</additionalInfo>

</recoveryLog>
 

More replies
Relevance 59.45%

I am trying to create a template to calculate the expected number of days for my shipment to arrive and I am cracking my head trying to put in the day as well i.e. for today [Aug-5-2002 Mon] How do I format it?

Anyone can help me? Thanks.
(btw, is it possible in the first place?)
 

Answer:[SOLVED] Day & Date in Excel 97 SR2

Right click the cell and select format cells, select custom from the list and put in mmmm-d-yyyy dddd and the resulting date will be in your format.
 

2 more replies
Relevance 59.45%

Conditional Formatting in Excel

Hi,

I would be grateful if someone could tell me how to do the following: I have a date/time field containing the format "ddmmyyy hh:mm", I want the conditional formatting to target the "hh:mm" portion of the cell, to enable me to pick out times between certain hour of the day. Unfortunately taking the day potion out on the cell is not an option.

Thanks in advance

mckenzie2403
 

Answer:Solved: Condional Formatting in Excel

6 more replies
Relevance 59.45%

Basically im trying to change the fill color in cells based on their StDev. 1StDev=Yellow, 2StDev=Orange, 3StDev=Red, BUT, each column has a different StDev, so im forced to do this manually, but theres gotta be an easier way. I have close to 200 columns alltogether...

I tried recording a macro but can't seem to figure out how to get the conditional formatting formula code to move over to the next column...anyway, i don't want to make it anymore confusing so i'll stop talking, any help?
 

Answer:Solved: Conditional Formatting in excel...

11 more replies
Relevance 59.45%

Hi Magicians

I want to cut and paste several columns of text and columns of numbers from Excel into a txt file without losing the formatting due to different widths of text in some columns?

I generally use Editpad for text files with Fixedsys but have tried unsucessfully with Courier etc and Notepad and Word...what am I missing please?

With thanks

Mike
Tasmania.
 

Answer:Solved: Excel to Txt Formatting Problem

I could of course misunderstood the problem, but can you not just save the excel spreadsheet as a tab delimited text file (file, save as, and change the 'save file as')?
 

2 more replies
Relevance 59.45%

I have a workbook with 3 sheets. Sheet "PS", column A contains a list of player names that stays static. Each week, each person in column A sends me a name from an available list. They are allowed to use each name only one time per year. The first name they send I put in column B, the second in column D, the next in column F, etc. Sheet "Data" has each player in a column. I have a formula (=PS!$B$4) etc. in each cell going down to pull the name from sheet "PS" corresponding to that week. I want to easily see duplicates, (which is against the games rules). I can use conditional formatting to change the font color and that does work. I have my Excel set to not show anything in 0 value cells. Because of the formulas, each cell below the entered names has a zero value which makes it a duplicate. I want to highlight the cells that contain a duplicate name. I just can't figure out the formula so I don't highlight all the cells with zeros as well. I'm very new to Excel so I apologize for the misuse of the proper terminology. I can certainly attach this workbook if it would help.
Thank you in advance for your help.
 

Answer:Solved: Excel Conditional Formatting

14 more replies
Relevance 59.45%

1
I haven't used table auto formatting in Excel 2003 so not sure if the same goes for 2007.

I have some data in a worksheet, select the data and chose the (Format as Table) and selected one of the available formats.

If I format a certain cell or range of data it will over-write the auto-format setting (font color, border color, cell color) What I want to do is to remove the (vertical) border between only two of the columns of the table (not all vertical borders). Using the (erease) tool will not erease the border. Drawing a line over the current line with the same color as the cells is not an option since the rows have interchanging colors. I need to erease it but have no idea how. Any thoughts?

2
When I add a hyperlink to a cell, the font automatically changes (size 11, color blue, underline). I don't mind the color and underline but I want it to stay with the size I choose (9) I looke everywhere (apparently not everywhere) and couldn't find the option to change the default hyperlink formatting. Also, if this is available, can I change it only for the (current) workbook not entire excel?

3
Conditional formatting with Icon sets. places icons in the cells based on the cell value, percentage or formula. The cell value has to be numerical, is there a way to change it to place the icon if I put a text formula (say tick if cell value is Yes and X if No)
Hmm that's all... maybe more later since I'm working my skills on professional looking sheets. ... Read more

Answer:Solved: Formatting - Excel 2007

6 more replies
Relevance 59.45%

Can anyone tell me how you can change your default font for comments in Excel?

Thanks!
~Mz
 

Answer:Solved: Excel comment formatting

8 more replies
Relevance 59.45%

I am trying to apply condtional formatting through VBA. Here is the code I am playing with at the moment.
Code:

Range("D2:D" & LastRow).FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR(D2<>0,D2<>""""),A2<>123,A2<>987,A2<>852,A2<>753,A2<>""User"")"
Range("D2:D" & LastRow).FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
Range("D2:D" & LastRow).FormatConditions(1).Interior.ColorIndex = 36
Range("D2:D" & LastRow).FormatConditions(1).Interior.TintAndShade = 0

When I test the conditional formatting on the worksheet itself, all appears to be working. However, when I try to apply the conditional formatting through VBA it is not working at all.

Can someone please point me in the right direction? I believe I am missing something simple, but I have not been able to place my finger on what I am doing wrong.
 

Answer:Solved: Excel VBA -> Conditional Formatting

8 more replies
Relevance 59.45%

When I enter a number into a cell i.e. 0412 246 123 and hit enter, the 0 drops off. I have gone into Format, Cells, Number, General, but it is still doing it.

There will be a simple fix, but I can't find it.

Please help.
 

Answer:Solved: Formatting Excel Cells

Highlight the cells you wish to change this too, right click then click Format Cells, then change the category to Text

I hope this helps
 

2 more replies
Relevance 59.45%

Quite an easy one for you super brains I think.

I'm trying to create a custom format for telephone numbers. I want the format to be:-

0123 4567 8901 2

The arrangement of the numbers will vary depending on the location of the number.

When I try to create a custom format it doesn't change the number, which currently has no spaces.

Thanks in advance
 

Answer:Solved: Excel cell formatting

16 more replies
Relevance 59.45%

Anyone know why I can't apply conditional formatting to a cell when the content of a cell is a formula? More importantly, how do I get around it? (Example - cell contains a formula to display "Yes" when other conditions exist. I want another cell to reference the "Yes" cell and display an icon based upon the reference cell being a "yes" or a "no")
 

Answer:Solved: Excel - conditional formatting

you should be able to
can you provide a sample spreadsheet and an exact description of what you are trying to achieve

what ICON are you trying to display ?
 

3 more replies
Relevance 59.45%

I may be able to solve this issue with conditional formatting, but I'm not sure exactly how, and I'm not sure it is the best method. I have cells with data that all rely on formulas. I've formatted the pages so that you don't see the zero results. I have other cells that will always have data but sometimes I want it visible and othertimes not. I can manually change the font color to white, but I have over 100 tabs all with various data that needs to be changed back and forth. Basically if Row 10 has a value in cell AW I want all of the text in that row visible, but if the forumla result in cell AW10 is zero I want all of the text white. I hope this makes sense, I would like to have this apply to over 100 tabs so I would like to have a macro do this for me. If that is best - I'm really not sure. The range of cells is A10:BF55, the value the condition is based on is always AW. And of course to complicate things a little further I want this to apply to most, but not all of my tabs. There are about 6 tabs that I don't want formatted. And the names of the tabs aren't a series (some of them are, but not all). I'll try to attach an example later.
 

Answer:Solved: Excel Conditional Formatting

10 more replies
Relevance 59.45%

Hi There,

In Excel 2010 I am trying to is apply conditional formatting to a range of cells, the conditions for formatting I am trying to implement are
If the cell is not blank and if the length of the text in the cell is not 11 then format

It works when applied to a single cell using
=AND(IF(($I9)<>"",TRUE,FALSE),(IF(LEN($I9)<>11,TRUE,FALSE)))

but when I try to apply it to a range (see below) it does nothing
=AND(IF(($I5:$I4000)<>"",TRUE,FALSE),(IF(LEN($I5:$I4000)<>11,TRUE,FALSE)))

Any advise would be greatly appreciated

Thanks in advanced

Chris
 

Answer:Solved: EXCEL Conditional formatting with AND

7 more replies
Relevance 59.45%

Hi Guys,

where can I find out what the format of the edit mask is that you see when you select Format, Cells, Number Format , Custom?

e.g. _-* #,##0_-;-* #,##0_-;_-* "-"_-;[email protected]_-

I've found that the first 3 blocks indicate the formating for +ve, -ve, and 0 but the above example has 4 blocks; what's the 4th block for? and can there be more 4 blocks?

Thanks
Hew
 

Answer:Solved: Cell Formatting in Excel

6 more replies
Relevance 59.45%

Hi, I have used a conditional format to change the colour of a cell (A30) depending on the value of another cell (B30), there are 6 possible values therefore I have created 6 rules for this cell (A30) I now want to copy the same conditional formatting to cell A31 but relating to B31, and so on. How do I do this. Many thanks.
 

Answer:Solved: Conditional formatting in Excel

if you have set the formula up in the conditional format - to be B30 and NOT $B$30
then you can copy
paste special
format
and copy to A31
 

3 more replies
Relevance 59.45%

Hi Guys,

I wonder if any of you can point me in the right direction.

I need to set up a conditional formatting. if the value in any of the cells in column C (row 3-73) is "Y" I would like the row in which a "Y" is found filled (from A-P

Thanks for reading

mad-martin
 

Answer:Solved: Excel Conditional Formatting

9 more replies