Over 1 million tech questions and answers.

Solved: Remove Cell Value If Cell Value Is Zero (Microsoft Office Excel 2007)

Q: Solved: Remove Cell Value If Cell Value Is Zero (Microsoft Office Excel 2007)

I'm working on a spreadsheet at the moment which displays a range of cells all containing values referenced from another spreadsheet (within the same workbook). This system works fine.

Every day, the original worksheet is updated. So, it has fields already arranged up until the end of the year. A row for every date. Now, needless to say, rows for dates in the future contain no values, and so when the spreadsheet I am working on now references those cells, it displays "$0.00" (which is correct, given I am dealing with financial figures).

Now, all of that works as expected, however, on the spreadsheet I am working on, all of those figures are displayed in a line graph. This line graph, at todays date, shows an enormous drop given that the fields for the rest of the year all show a zero balance.

What I need to do, is to get the remainder of those fields (every field that says "$0.00") to not display anything at all. So, if the value is $0.00, it would not display a value at all, and therefore not show anything on the graph.

Can someone tell me how I can achieve this? I'm sure it can be done with an "if" statement, but I'm not sure how to structure it.

Any help would be greatly appreciated.

Preferred Solution: Solved: Remove Cell Value If Cell Value Is Zero (Microsoft Office Excel 2007)

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.)

A: Solved: Remove Cell Value If Cell Value Is Zero (Microsoft Office Excel 2007)

=If(a1="","",Sheet1!a1) and drag it down.

Where a1 is the first cell in spreadsheet you are working on, and sheet1!a1 is the sheet within workbook containing figure.

Not sure if the graph will recognize the "blank' cell as blank or "0"
You could try that


Read other 3 answers

In cell j, I have formula =IF(SUMPRODUCT(ISNUMBER(SEARCH("VLXP",K2:AB2))+0)>=1,"Yes","No") that returns yes or no if VLXP is contained in any cell K2 through AB2 and it works correctly. What I would really like to do is then put into cell j the entire matching cell content or if not found return n/a. Is there a way to accomplish this maybe with VBA?

A:Solved: Excel if cell contains vlxp then put matching cell data in current cell

Read other 6 answers

hi, i have 2-excel cells in the same sheet, both contain manually entered numbers; cell-2 changes frequently; if the existing entry in cell-1 is < than the new entry in cell-2, cell-1 should immediately reflect this new value. how do you create this formula?

A:Solved: excel-replace content of cell-1 if cell-2 is > cell-1

Read other 8 answers

I have a sheet with 2 simple columns: Date and Price. I have imported the dates (##/##/####) and the prices ($###,###) by copy/pasting from the search results given to me by a niche database program I use. When the cells paste in, they all have the format "General".

When I try to format the "date" column into dates, it _does_ change the format as far as the cell is concerned, but the content of the cell doesn't adapt to the new format. For example, I have the date as 3/05/2001 and when I change it to a date format of MMM D, YYYY the content should change to March 5, 2001 but it doesn't. It is as if all the cells are forced to stay as text regardless of what the formatting is that I'm applying.

Same problem with the price column: if I change the format to include 2 decimal points, that format does apply to the cells, but the content of each cell remains without a decimal or anything following, as if the content is just text.

I have like 1000 rows in each column, and plan to do this analysis of the database's results frequently, so I'm hoping the answer isn't just to retype the data. There's got to be a way to copy/paste or export or something. Maybe I could copy/paste into notepad first to scrub out any formatting or locking from the niche database program?

A:Excel 2007 Cell Values Won't Take On Characteristics of Newly Applied Cell Format

Good news: Made some progress. In thinking that maybe each value had the textual single-quote forcing it to act like text, or maybe if I find/repaced all the dollar signs and commas that had been imported, I accidentally discovered that each and every value in my imported columns has a following space!

Bad news: Seems like Excel has a bug that thinks that if I say "Find=[singleSpace]" "Replace=[null]", then I should be given an error saying "Excel cannot find any data to replace". I think I'm doing the find/replace correctly because it worked on the dollar signs and commas.

Anybody know a workaround for the bug?

Read other 1 answers

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

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

Read other 8 answers

Using Excel 2003 in Windows XP

I would like to use the contents of one cell as the destination location for copying data.
For example
I have 2 worksheets 1) Results and 2) info
in info
A1 = 'ABC'
C1 = 'Results!O54' < this is calculated based on other data in sheet.

Using a macro, I'd like to copy contents of A1 to cell location 'Results!O54' more specifically to where ever C1 points... C1 will change based on other data in info sheet.

The macro record for action looks like this (but I would like the 'O54' to be based on contents of C1 which changes)

There is more to it then that but I think this is where I am stumped.

A:Solved: Excel: Uses contents of Cell to select a cell

Sheets("info").Range("A1").Copy Destination:=Sheets("Results").Range(Sheets("info").Range("C1").Value)

Read other 3 answers


I am using excel to create a POS software. I am facing trouble in one cell where I want it to use either one formula or another.

I have attached the file.
What I want:
In the form sheet, I have an invoice layout. The cell 'Line Total', I want it to calculate the product of "price/unit * quantity" OR "price/ctn. * ctn."
(CTN. is cartons or boxes). The current formula I am using is showing a '00' in the line total. The idea is that I want to either enter the number of CTNs. under the CTN field and get the line total OR enter the quantity under the quantity field and get the line total. So it is deriving the line total either based on the cartons or the (single) quantity.

Can anyone find a solution for this? Thanks

A:Solved: Excel 2007- Two formulas in one cell (Please Help)

Read other 10 answers

The formula:

='[Folder Worksheet.xlsx]QC!$E270

How would I make this cell read blank until something is put into QC!$E270?

A:Solved: Excel 2007 Help Cell Blank

=IF ( '[Folder Worksheet.xlsx]QC!$E270 ="", "" , '[Folder Worksheet.xlsx]QC!$E270)

I think you need
But the IF statement should work

Read other 3 answers

I have an Excel 2007 spreadsheet with simple "general" formatting, in which I log single numerical values (i.e. "1"). I have a general background color set (i.e. "grey") and there are no other formatting or linked cells. When I type in alpha-numeric entries (i.e. "1 hr.") the cells will appear correctly, however, when I type simple numeric values (i.e. "1" without the "hr.") the cell background color will change completely (i.e. bright red) and is not able to be changed or overriden until the numerical value is deleted or else modified to alpha-numeric terms.
I have tried re-formatting the cells themselves (i.e. instead of "general", formatting to "number" or "currency") and the problem still exists.
I haven't been able to find a solution to this, and, since I'm not intimately familiar with Excel, it has been a bit trying.
Hopefully you can be of assistance!
Thank you, in advance. I appreciate it!

A:Solved: Excel 2007 changes cell colors involuntarily

Welcome to the forum.

It sounds like you have conditional formatting turned on. Try going to the formatting tab and looking into it there.

Read other 3 answers

I work with Excel 2007. If under column A, I have 900 names of Restaurants, and under each name of restaurant, is a one-line address of the restaurant, what should be done so that the address can be moved to the cell to the right of the restaurant.

For example

This is the original data:

Column A
AAA Restaurant
AAA address
BBB Restaurant
BBB address
CCC Restaurant
CCC address
This is the desired result

Column A Column B
AAA Restaurant AAA address
BBB Restaurant BBB address
CCC Restaurant CCC address

Thanks is advance

A:Solved: Excel 2007 Move Data to Another Cell

Read other 7 answers

I am hoping that someone can help me with what may be a complex question.

I have a spreadsheet, similar to the demonstration file I have attached. In it, there are a range of columns. One set of columns (A - D), shows a list of numbers that are references to items in a store, and it also shows their location in the store, so it acts as a floor-plan. The next column (E) lists those items again to show the dollar amount that those items have produced in total sales (F).

So, what I'd like to do is to add a conditional formatting to show me the "cold" and "hot" spots on the floor-plan so I can see where sales are most concentrated. I would assume that this would be done with a colour scale (using the range of values in column F to work itself out)?

Either way, the figures in column F would change weekly, so the relevant cells in columns A - D would need to automatically change when they're matching cells in column E - F change.

I hope this makes sense, as it's difficult to explain, but any help that someone could provide would be greatly appreciated.

A:Solved: Creating Cell "Cold" And "Hot" Spots Based On Values In Microsoft Excel 2007

Read other 16 answers

Trying to pull the word "set" from a cell that has more words and characters than just "set. I also need it to account for variables such as lower case/upper case (i.e. SET, SeT, SEt, seT). The formula needs to pull the word "set", however it looks, into the adjacent cell, this way I can use the Sort tool to organize all cells.

I tried the INDEX formula, but it only looks for the word "set" without distinguishing it from the other characters.

I don't think the LEFT/RIGHT/MID functions will work, as the character count is going to be a variable from each cell (my judgment on these formulas is really shallow due to my lack of time to completely understand them).

Take a look at the attached spreadsheet and you'll see where I'm going.

Thanks in advance

A:Solved: Excel 2007 - Formula for Extracting Specific Characters from Cell

Read other 9 answers


I'm aware of conditional formatting for cells in Microsoft Excel 2003, but it doesn't appear to have an option to apply a format to a cell when it simply has an entry of any variety entered.

What I am looking for is something that applies a format to a cell as soon as a value is entered in to that cell. The value might be character or numerical, but as soon as there is something in the cell it should apply the format.

Can anyone advise the best way to achieve this?

Thank you!

A:Solved: Automatic Cell Formatting In Microsoft Excel 2003

Read other 16 answers

I'm attempting to write my first macro for an Excel 2003 workbook. I'm not completely code illiterate (I've got moderate skills with AutoLISP), but I'm new to VBA and am not yet an Excel power user, so please be gentle.

The macro I want to write will:
check that the selected cell's content is underlined before proceeding
copy the content of the currently selected cell into an external plain text .log file
.log file lines should be: year/month/day - time - username - cell contents
.log file names will probably need to be generated
clear the cell's content and formatting (particularly underline and text/background color)
Here's what I have so far:
Sub Unpost()
If Selection.Font.Underline = True
Then Selection.ClearFormats And Selection.Clearcontents
If MsgBox("The selected cell is not underlined...are you sure?", vbOkCancel) = vbOk
Then Selection.ClearFormats And Selection.Clearcontents
Else Exit Sub
End If
End If
End Sub
If I've written it correctly, it should currently do everything except log the cell contents. This, from what I've seen, is going to be the trickier part. I intend to use this macro 50+ times per weekday, so at some point the .log files will get too long to be useful, so I assume it will need to automatically create new logs (perhaps "year-month.log"). I've seen some useful info about appending to an external log here and here, ... Read more

A:Excel 2003 macro: log contents of selected cell, clear cell

You need to use the "File Scripting Object" to create and/or append text to a file. I've included a link below to get you started. If you are unable to figure it out on your own let me know and I'll write the code for you.



Read other 1 answers


I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers

Using EXCEL, I have a need to copy the cell contents from upper cells in col. A down a few rows in col A. There are various changes in data in col A as you will see below. The periods in the following info are used as placeholders only. B1, A2, A3, A4, etc. are blank. I need a formula because I have 60,000 records in the spreadsheet. Thanks in advance.

Here is how the data looks now.


Here is how I want the data to look

...A ...........B

A:[Excel] Copy And Paste Upper Cell To Lower Cell

With the workbook open press ALT + F11 to bring up the Visual Basic Editor. Once the VB editor opens, click INSERT --> MODULE and paste the code below into the blank module. Close the VB editor and select the first cell in column A containing your data you want to copy down. Click TOOLS --> MACRO --> MACROS and select the macro from the list and run it. This macro will copy all your data except for the last value in column A because without actually seeing your workbook, I have no way knowing which line to stop at. Therefore, the code will end when it reaches the last value in column A.


Public Sub CopyData()

Do Until ActiveCell.Row = Cells(Rows.Count, "A").End(xlUp).Row

ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select


End Sub


Read other 2 answers

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers

I am working with a huge spreadsheet
I need to repeatably copy and paste from some cells to another document
(saving in another format is not effective for this job)

The cells that concern me are "text" - some of the cells in that column have been formatted as text and contain the ' - they have the single quote for left alignment
This is making my cut/paste job quite tedious
I would like to remove the quote mark so I don't have to not select it on copy or have to delete it on paste

I have tried selecting the column and making the number and alignment General (have tried many many things) and cannot make them go away.
I have tried a replace all to no avail

Is there anyway to globally (throughout this workbook) remove all of the single alignment quote marks?

Thank you

A:Solved: remove single alignment quote mark excel cell

Read other 10 answers


I have another conditional formatting issue in Microsoft Excel!

In a spreadsheet I'm working on at the moment I have a range of cells that I will, at times, manually give a green background. This is simply a visual aid. These cells also contain a value (a dollar amount). So, in one row, there might be three green cells and four normal cells (with no background), and at the end of that row there would be a cell that needs to calculate the total dollar amount of the cells in the row with a green background.

Can anyone provide me with instructions and information on how to accomplish this?

A:Solved: Conditional Formatting Dependant On Cell Background Colour In Microsoft Excel

Read other 16 answers

I have an Excel 2010 workbook comprising a simple organization membership list, consisting of names, addresses, etc., for nearly 2,000 members, grouped into 5 chapters' sheets, in a single Excel 2010 file. Every time I sit down to enter updates for any member, or add new members, etc., I always start by saving my most recent version of the workbook with the date I last updated it in the filename, and then when I finish my updates for the day, I save the current day's updated workbook with the current day's date in the filename, etc. So after a while, I've got quite a stack of versions of the same workbook, albeit with slightly different data in some of the cells, from version to version.

Near the end of the year, it'd be neat to be able to compare the beginning and end of year versions, cell by cell, to see what changed.

Ideally, I'd like to sort of "subtract" one workbook from the other, cell by cell, and display something in the results that shows cells that differ btwn workbooks and cells that don't.

Obviously, I could just print the two versions, and plow through them both, side by side. But they're nearly 2,000 rows deep, all things considered, and with all the data we compile for each member, each row goes up to column AT! That's a lotta data to compare visually cell by cell.

Is anything like what I'd like to do possible?


A:Can I compare 2 Excel spreadsheets cell by cell?

I think this might help if I read this correctly. I used this before to compare three lists I made a long time ago, then when I needed to update them I didn't want to sift through hundreds of entries.

Compare Two Lists in Excel - Easy Excel Tutorial

From MS https://support.microsoft.com/kb/213367

Also 4 Ways to Compare Data in Excel - wikiHow

Read other 1 answers

I have two cells, Cell "A" and cell "B", that have a formula in each. Cell "A" has a value that is correct and Cell "B" has a value that is correct. I now have a third cell (cell "C") with a formula that takes the values of cell "A" and cell "B" and multiplies them. The value of the product is wrong in cell "C" as compared to a value performed by a calculator. Cell "C" reports 51,550.64 whereas the calculator reports 51,540. What is the problem.


A:Excel cell to cell multiply problem

I'm willing to bet that the number you are entering into the calculator are rounded off while the number that Excel is using is not truly rounded off. Even though Excel may display a certain number in a cell due to its format, it is probably using the true value of the number which probably includes several decimal places. What numbers are showing in cells A and B? How are cells A and B formatted? What happens if you increase the number of decimal points in these cells...do the cell number become larger? If so, then Excel is likely using the true values of the cells instead of the display values in its calculations. Provide details of how you are obtaining your cell values so we can confirm that this is happening.

Try the following

TOOLS --> OPTIONS and choose the Calculation Tab. Put a check in the box marked "Precision as Displayed."
NOTE: This will affect all other calculations on the workbook causing changes to other values on the sheet!


Read other 3 answers

Is there a way to copy a col of formula's/math cells to a new col without excel modifying the coordinates?

Scenario: 150 cells of diff equations "located from a1 thru a150" you wish to move to d1 thru d150.

Problem: Excel modifies the coordinates of each pasted formula rendering garbage.

Individual f2, highlite,copy and then paste takes too much time if there are thousands of formulas to move.
thanks, nt

A:Copy cell to cell without excel modifying

Use absolute instead of relative references, ie:



EDIT: better explanation. You have 5 in A1, and the formula =A1 in B1. If you copy B1 to C1, C1 will read =B1. But if you make B1 =$A$1 and then copy it to C1, it'll hold.

Read other 1 answers

Good Day, I have in one cell formula and drop down list as option. Formula will work, if other cell will have option 1 (in cell will be shown text automatically), and will not operate if will be choosen option 2. 9if user will choose option 2, he needs to choose item from drop down list)
And every time, I use something from drop down list, formula dissapear. How can I have both in one cell, drop down list and formula. Pls assist.

Read other answers


I am using MS Excel 2007.

My situation is as this

I run a report every week for some items and their value, I compare 2 conditions. Condition 1 gives the whole list with Column A as the name of the item and Column B with value of the item.

Condition 2 is for the same items but the problem is that when I extract the data from SAP it excludes the item name that has 0 or less value, so i don't get the whole list of items.

And both conditions go in the same woorkbook and sheet when I run the report (I copy them into one workbook. The report is for comparing between condition 1 and condition 2.

The problem is that I have to go through both lists of items and manually add the items that are missing in condition 2 and add a value of zero next to them.

I need a macro that compares the conditions and not only add the missing item name but also add zero to the cell next to the item added ( to the right)

A:MS Excel 2007 Compare Columns and add to next cell

Sorry for bothering... I must have put this in the wrong place

I solved the problem... Needed an iPod with some of the best of Yanni and a couple of cigarettes to come up with a solution

I was thinking that the "IF" formula is the solution but what I needed actually was advanced filtering.

Item name on Column A and value in Column B

Sorted both Conditions by Column A

Condition 1 has the full list of items.. and Condition 2 had a smaller list but needed to have all items and the missing ones needed to have 0 value in B.

I had a new workbook and copied Columns A & B from Condition 2 into the new workbook. Then went to condition 1 and copied column A only under the cells copied from Condition 2.

Used advanced filtering on Column A to show unique records only.

The result is a list of items with their values in column B untouched and the ones that were missing in Condition 2 had blank value.

Now I can run a macro to automate this process...

Sorry for having this put in the wrong place, and it seemed that I was able to solve this on my own after all.

Well the bright side is that there is a one in a million chance that someone out there need the same thing that I need and actually see a solution

Read other 1 answers

I.e. without multiplying the contents into every cell that I pass?

A:In Excel 2007, can I drag a cell to another column or row?

I know the answer now, it's too go to a border, wait until the cursor changes to the thin crosshairs, and then drag.

Read other 1 answers

I have display issue for Lenovo Ideapad 500-14ISK when I use Excel 2007.Every time I save, it always happen distorted cell display.If I scroll up / down the cell, the cell display will work normally.I am using Windows 10 (64 Bits). I have installed the latest display driver for AMD Radeon and Intel 520.How to solve this problem ? Thank you. 

Distorted cell display.JPG ?370 KB

Read other answers

Is it possible to produce the following example sequence using excels autofill feature ?


If not can anyone suggest another way of doing it ?



A:Excel 2007 - use autofill on cell ranges

Read other 12 answers

Hello Turbodante

A most unexpected answer - I shall try it out and let you know

Thank you for your help and quick response.



A:Excel 2007 - use autofill on cell ranges

Yes, please do let us know how you get on.

BTW, there is no need to start a new thread for the same problem - i shall inform the mods to get these merged.

Read other 1 answers

Greetings -


Target Complete Money
1 1/1/09 1/7/09 50.00
2 1/5/09 1/12/09 100.00
3 1/7/09 1/14/09 50.00
4 1/17/09 1/17/09 25.00
5 2/2/09 1/26/09 25.00
6 2/5/09 150.00
7 2/6/09 150.00
8 2/5/09 75.00

Currently i have the following formula to calulate how much money that I might make.

This works fine as I have no blank cells in column A

If I try to apply this formula to column B I get an incorrect number as I have blank cells in there with no values.

Is there a way to continue to use this formula but have it check for blank cells and if it finds any, don't add those values??

A:Excel 2007: Date/Sum Macro/Blank Cell

Welcome to the board.

How about the attached? Contains 1 "master" dynamic named range & 2 others based on the "master". Use E2 to define the month you're after.

Read other 3 answers

I am trying to trim the spaces from the data in a cell, but the function appears instead of the trimmed data. Here are the details: The contents of cell H9 is " 19004-2329". The contents of cell I9 is the function =TRIM(H9). The "Show Formulas in cells instead of their calculated results" variable in Excel Options is not checked. If I toggle CNTRL ` I can turn the "Show Formulas..." variable on and off, but the function remains in cell I9, instead of the resulting trimmed data. Any ideas on what else to try? Thanks.

A:Excel 2007 cell displays formula rather than result

Make sure the cell is not formatted as text.


Read other 2 answers

I have a couple of questions regarding the below code and the attached spreadsheet. What do I have to do to make this macro execute at the time indicated in col m of the spreadsheet? The dates are going to be different for each row.
Also how do I get cell data [a4] [h4] [g4] [m4] [n4] to populate from the worksheet into the email?

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Andean Funding Closing Document has not been recieved" & vbNewLine & vbNewLine & _
"Andean Tracking Number: [a4]" & vbNewLine & _
"Requested Amount: [h4]" & vbNewLine & _
"Case Number: [g4]" & vbNewLine & _
"Closure Document Due NLT Date: [m4]" & vbNewLine & _
"Staff Coordinator: [n4]" & vbNewLine & _
"Please contact OGL immediately to correct this situation" & vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine & _
"Judy De Santis" & vbNewLine & _
"Office of Global Enforcement" & vbNewLine & _
"Latin America Caribbean Section" & vbNewLine & _
"Office: 202-307-4609" & vbNewLine & _
"Cell: 202-345-9257" & vbNewLine & _
"Fax: 202-30... Read more

A:Excel 2007 -How do I get cell data to populate email?

Read other 6 answers

This is my first post to this site

I am using Excel 2007. In cell A2 there is text with trailing char(160)'s in it. In cell A3 I type =substitute(A2,char(160),"") and when I press enter it displays the formula rather than the result.

I believe the original file was a xlsb, I saved as an xslx, but attained the same result. Don't know if file type is an issue.

Why does excel behave like this?


A:Excel 2007 cell displays formula rather than result

Try pressing CTRL+~ (Control and tilde) to see if that resolves the issue. It is a deliberate feature to allow you to edit the formulae more easily, also of great help to IT schoolkids who have to prove they put the formulae in by taking a screenshot.

Read other 1 answers

I've been asked to look into visually representing our storage locations in Excel.
In short, I have 1 Area containing 12 Locations. All but one of the Locations consist of several Rows, and each Row has a capacity, up to a max of 58 Units. The total Units is around 3,500.

I've been looking at using Conditional Formatting - particularly Colour Scales and Data Bars.
I'm now looking at an alternative to Data Bars.

In column A, I have a series of values - say 10, 20, 30, 40, [...] 100.
In column B I have the formula:

(obviously, replicated for the other values in A)
I'll also stick an IF ISERR on that in case someone enters a value above the max.
What I'm hoping to do is colour those black squares red, and leave the white ones untouched. I know I can't do this within the formula, or with the cond formatting, but I'm assuming it should be possible in code, using a Do Until loop or something?
It's just a demo, so I might use something other than the wee boxes in the end, I'm not sure yet.

My adventures in VBA are limited to recording macros or finding bits on the internet, and then jigging about with the code in the VBA window. It (pretty much) makes sense to me, but I wouldn't be able to write it from scratch.
Thanks in advance.


A:Excel 2007 - formatting part of a formula cell with VBA?

Read other 6 answers

I have display issue for Lenovo Ideapad 500-14ISK when I use Excel 2007.
Every time I save, it always happen distorted cell display.
If I scroll up / down the cell, the cell display will work normally.
I am using Windows 10 (64 Bits). I have installed the latest display driver for AMD Radeon and Intel 520.
How to solve this problem ? Thank you.
Mod's Edit: System model added to the front of Subject line to improve visibility / clarity.

Distorted cell display.JPG ?370 KB

A:Ideapad 500-14ISK - Distorted cell display when using Excel 2007

Hi freddysh, I'm not sure if this is a compatibility issue, since Office 2007 has not been tested for Windows 10. What you can try to do is re-install Office 2007 or try running the program under compatibility mode. Here's the link from Microsoft about Office 2007 compatibility with Windows 10.   Hope this helps. Cheers, 

Did someone help you today? Press the star on the left to thank them with a Kudos!If you find a post helpful and it answers your question, please mark it as an "Accepted Solution"

Read other 1 answers

I am trying to save myself lots of time calling my reps to ask them to fill in the missing information from monthly reporting. I have conditional formatting to highlight the fields, but they still ignore them. I would like to set it so they can't save or close the file if any row within the table has data in column A, but column B is blank. My idea is to run VBA code on save and on close that tells them they cannot save or close until they fill in the highlighted cells. I have no idea how to make this happen. I am familiar with VBA, but no expert for sure. Any help is appreciated. Thank you very much.

A:VBA Excel 2007 Require Entry in Cell B2 if isblank(A2)=false

An easier option would be to check the cell color because the conditional formatting colors the cells if they need to be filled in. If any cells within the given range are yellow, then they get a message telling them they can't save the file until they fill in the yellow cells. Any guidance you can provide on how to accomplish this would be greatly appreciated. Thanks.

Read other 2 answers

How can I do it?

I basically need THIS to work, but I can't compare two items in the same if apparenlty...

=IF(K12>2400 and K13=0,2400,K12)

I am comparing the totals of daily hours to see if regular hours ended up over 40 (2400 minutes) and whether or not the daily overtime is still zero before I use the regular hours to figure overtime in a timecard system.

Unfortunately that won't work, nor does using & beween because then it concatenates it and always ends up false.


- Drake.

A:Excel 2007 - need to compare TWO cells to determine a value for the current cell...

Read other 6 answers

i do call data analysis in excel 2007 involving more than 1 lac call record.

i need to exctract certain text (eg.Oracle) before a word (eg. install,repair,reload,reinstall) and even after these word if any.

maybe 10 characters before and after the word "PLEASE INSTALL ORACLE"

as in call records we have fields as Problem/Issue raised and Resolved.

Please help..!!!

A:Excel 2007 - Extracting Characters before & after a specific word from a Cell

Hi, welcome to the fourm.

I suggest you write a macro.

In this macro you search for the position you word starts:

You use the Instr() function

Dim pos as integer
pos = instr(1,<your full text string>,<you word>)

"This is your text string and you need to know where the word is"


pos = instr("This is your text string and you need to know where the word is","text")

pos will return the value 14, the beginning position of the word "text"
try this in Excel as macro

Sub test()
MsgBox InStr(1, "This is your text string and you need to know where the word is", "text")
End Sub

After you have the pos value then you can work with the different string functions to extract or create your desired text

Read other 3 answers

When using conditional formatting with lots of conditions, the cell either does not highlight until it is touched, or in some cases just a tiny amount of highlilghting appears at the top of the cell, and once it is touched the entire cell highlights. Other worksheets in this workbook work fine, but on this worksheet the highlighting is acting differently.

Read other answers


I have data in a table, and am using VLOOKUP to search through the 1st column for a word, and return all data in that row in the cells following, an example of the formula I am using in each cell is:

=VLOOKUP(CELL containing search term,table$:table$,Column number,FALSE)

There is nothing wrong with the formula, but wanted to give some context.

My issue is that some of the rows original data is formatted as 0,000.00 and others 10,000 and others 0.00%

Using VLOOKUP brings in the value only, but i need to include the formatting.

I have tried using conditional formatting by assigning an extra column value (1, 2 or 3 depending on the formatting of that row) and then setting rules which change the formatting of the cells depending on this value. BUT the issue is that the 3 rules are applied in order, so it works to a point, but the rules are not reset when the sheet recalculates (when submitting the search term from the list) so once it gets to the last rule, it sticks.

If anyone has an alternative to VLOOKUP that would keep the formatting or any advice on the conditional formatting and making the rules start again from 1 that would be great.

A:Excel 2007: Retaining source cell formatting when using VLOOKUP

I can't think how to achieve this without resorting to the help of a bit of VBA code, to check the cells formating after a match is found.

Read other 3 answers

I am running Windows Vista Business SP1 and Excel Enterprise 2007. I work in finance, so having all of Excel's keyboard shortcuts available is critical to me. I often use the shortcut [ctrl]+1, which pops up the "format cell" window. However, this shortcut does not work anymore for me. All my other [ctrl]+ shortcuts work well, however, so I am confused as to what is causing the problem.

Can anyone help? Thanks in advance.

A:Solved: Excel 2007 issue: "format cell" keyboard shortcut (CTRL+1) not working

Read other 16 answers

A strange thing is happening on one of my Excel spreadsheets.

If I put a value in cell A and another in B and use cell C for the result of my calculation, cell C produces a blank. Cell C, however, does show what is input directly to it.

Please! Why is that happening and what is the cure?

A:Excel 2007 Home and Studentl: calculation results in blank cell

It will help us to help you if you post the exact formula you use to get the result of your calculation.

Read other 9 answers

I'm running a spreadsheet for yearly budget using EXCEL 2010. Everything has run smoothly until today. If I enter values on one sheet, it shows up on all sheets. I've gone through the settings for EXCEL but cannot find a setting to change this. Anyone have a suggestion on how to correct this?

Tech Support Guy System Info Utility version
OS Version: Microsoft Windows 7 Professional , Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i7 CPU 860 @ 2.80GHz, Intel64 Family 6 Model 30 Stepping 5
Processor Count: 8
RAM: 8183 Mb
Graphics Card: ATI Radeon HD 4600 Series, 512 Mb
Hard Drives: C: Total - 76216 MB, Free - 8485 MB; D: Total - 1907599 MB, Free - 1891982 MB; E: Total - 476809 MB, Free - 409377 MB; G: Total - 2861575 MB, Free - 2680780 MB; J: Total - 238472 MB, Free - 107677 MB;
Motherboard: ASUSTeK Computer INC., P7P55D-E PRO, Rev 1.xx, 103822980000259
Antivirus: ESET NOD32 Antivirus 4.2, Updated and Enabled

Read other answers

I selected a cell, and then recorded a macro when I copied the cell, moved down to the cell below it and pasted it. But when I select another cell, and then run the macro, it goes to the initial cells I did when I recorded the macro.

So is there a macro where I have selected a cell, whatever cell I decide, and then run the macro, and it copies the cell selected and paste it to the cell below the cell that was copied?

A:Solved: Macro to Copy and Paste From One Cell to Cell Below

Read other 16 answers


I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i

End Sub


A:Solved: Clearing Cell Contents Based off other Cell

try this variation;

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub

Read other 2 answers

I have a user within AD who is getting a chinese popup error everytime he presses a key in excel for the 1st time or even outlook. However on the 2nd occasion everything is fine and it works well and no error occurs.

I hvave done everything you can think of from a

Full Uninstallation of the app + registry removals of Office 11 and Re-installation

Checking that his AD I.D. has full rights within the Registry

Re-created his profile both Locally and Server Side

Re-installed the keyboard driver under his AD login

Tested using regmon and filemon and seen few failures here and there and got the registry keys elsewhere and imported

All of these still results with the user getting the popup error with Chinese Writing when the 1st key is pressed still

Any of you guys came across this and resolved??

A:Office 2003 Excel Displays Chinese font popup on 1st key entered in cell

Actually, I have heard of this- something in an update caused the problem. There should be a fix:

This was the update: http://www.microsoft.com/technet/security/Bulletin/MS07-002.mspx

This is the Caveat: See http://support.microsoft.com/kb/927198

For # Microsoft Office 2003 Service Pack 2, when used with:
* Microsoft Office Excel Viewer 2003
* Microsoft Office Excel 2003

Read other 2 answers