Over 1 million tech questions and answers.

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

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

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?

RELEVANCY SCORE 200
Preferred Solution: Excel 2007 Cell Values Won't Take On Characteristics of Newly Applied Cell Format

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: 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
RELEVANCY SCORE 122.8

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.
 

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

Pedro
 

Read other 3 answers
RELEVANCY SCORE 118

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
RELEVANCY SCORE 117.6

Hello Everyone,
(This is kind of long but I didn't know how else to explain everything)

There are two parts to my question below.

I have a spreadsheet with roughly 40 columns

An Example of some of the columns would be:

Code:

B1 = Client Name E1 = Travel F1 = Backup G1 = PC Install H1 = Data Recovery | X1 = Total | AE1 = Summary
B2 = Tony Smith E2 = $70.00 F2 = $0.00 G2 = $95.00 H2 = $0.00 | X2 = $165.00 | AE2 = (See Below)
B3 = Tom Daniel E3 = $70.00 F3 = $0.00 G3 = $0.00 H3 = $725.00 | X3 = $795.00 | AE3 = (See Below)
B4 = Rene Little E4 = $70.00 F4 = $125.00 G4 = $95.00 H4 = $995.00 | X4 = $1285.00 | AE4 = (See Below)

Currently this spreadsheet is being analyzed to be used to track field technician jobs and figure everything out for accounting.

I have been asked to please make a cell with a list of the information above so that accounting can just copy and paste from that cell into quickbooks in order to send these people out a receipt instead of having to retype everything into quickbooks. This list has to be based upon whether or not there is a value greater than 0 within that cell then it will grab both the heading cell in the first row and the value of that cell it was checking and place it as a line item in another cell.

I know how to do half of what they are asking and if I was dealing with 1 column I can do the other half.

I know how to list items in 1 cell using the c... Read more

A:Excel: Checking values on multiple cells and transferring values to 1 cell

Read other 16 answers
RELEVANCY SCORE 114.4

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
RELEVANCY SCORE 109.6

I am having trouble doing the following:

I have an Excel workbook with two sheets. The first sheet contains two fields, one for first name and one for last name.

On the second sheet, I want to combine the two into one column called borrower name. Here is the formula I'm using:

='sheet1'!A1 & ", " & 'sheet2'!B1

this works on the same sheet as the source data (sheet1), but is ignored when on the second sheet.

Any ideas?
 

A:Combining cell values on different Excel sheets

HeavyBall said:

I am having trouble doing the following:

I have an Excel workbook with two sheets. The first sheet contains two fields, one for first name and one for last name.

On the second sheet, I want to combine the two into one column called borrower name. Here is the formula I'm using:

='sheet1'!A1 & ", " & 'sheet2'!B1

this works on the same sheet as the source data (sheet1), but is ignored when on the second sheet.

Any ideas?Click to expand...

Hi

If you are combining data from just one sheet then your second reference should be Sheet1 and not Sheet2 - or have I misunderstood?

Regards
 

Read other 1 answers
RELEVANCY SCORE 109.6

Is it possible to use an IF statement to set the value of not only the cell the formula is in, but also other cells.

i.e. IF (G3="Yes" THEN Cell B3 = "xxx" and Cell D4="yyy" ELSE "False")

Or is there a way of doing this without the IF function, this would be very useful. Thanks.
 

A:Excel - Setting mulitple cell values with IF

Not unless you're "in" the other cells.. or using VBA. A formula in a cell cannot influence any other cell unless you're directly in that other cell (i.e. another formula).
 

Read other 1 answers
RELEVANCY SCORE 109.6

I am trying to add a series of cells that contain a mixture of positive and negative values.
Using =sum(b3:f3) is not providing the correct answer.
Can someone help please?
 

A:Solved: Excel addition of +ve & -ve cell values

Read other 6 answers
RELEVANCY SCORE 109.6

I have a formula that references values from a different worksheet. I am instead needing those values to actually be in there! Instead of just being referenced by a formula.

My formula is:
=IFERROR(VLOOKUP($A12,Table2,10,FALSE),"")
Click to expand...
 

A:EXCEL - Making values stick in a cell

Select the entire range of cells in question and then right and choose COPY. Then right click again and choose PASTE SPECIAL and then select VALUES as the paste type. FYI you can do this for a single cells, entire columns, or all the cells in the entire sheet at once.

Rollin
 

Read other 1 answers
RELEVANCY SCORE 109.2

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
RELEVANCY SCORE 108.4

....A................. ........B ..........................C

1. AMDRG.SA ........AMDRG.CCM.......CTFACmpMkt='AMDRG.SA' + 'AMDRG.CTA';

I am not sure how good that is going to look once i post this, but here is a rundown of what it is.

Lets say those are 3 columns, A, B, and C. I would like to have a macro or script that will take what is in column B, which is also inside the text of column C, and change the text AMDRG.CCM to what is in column A(AMDRG.SA). So when i am done, the info in column C will look like this...
CTFACmpMkt='AMDRG.SA' + 'AMDRG.CTA'
Column A and column B will always line up with eachother, but column C will not always line up right beside the cells associated with what the replacement text is.

Thanks for any help that you can give!
 

A:Multiple find and replace cell values in excel

Can you post a sample workbook with a few more records?

Rollin
 

Read other 3 answers
RELEVANCY SCORE 108.4

I have some values in Excel that i would like to get into variables in Visual Basic. So I have values from cell A4 to A50, that will be one group. then Values from cell B4 to B50 tha will be another goup. I am using Visula Studio 2017 if it makes a difference. I want to use Visual Studio instead of Excel so i can turn it into and exe eventually. Can anyone tell me how to do this.

thanx
 

Read other answers
RELEVANCY SCORE 107.2

So, this one has me stumped and the Internets is not revelatory.

I am trying to create a decision analysis tool in Excel. It's a typical design: one structure for criteria, another for proposals, a third to enumerate the alternatives, and a fourth for scoring.

It's fine for simple decisions where all proposals are mutually exclusive. I'm trying to incorporate the concept of dependency and partial exclusivity, though.

So, for example, on my proposals sheet, I've created a simple exclusion list that looks a bit like so:
Code:

[Proposals Sheet]
C1 C2 C3
=======================================
x1 ...is incompatible with... y3
x2 ...is incompatible with... y2
x3 ...is incompatible with... y2

Where x1, x2, x3, y2, and y3 are all proposals being analyzed.

The alternatives are enumerated automatically using binary math, like so (just a sample extract):
Code:

[Alternatives Sheet]
x1 x2 x3 y1 y2 y3 | Alternative Code
==========================================
0 0 0 0 0 0 | Do nothing
1 0 0 0 1 0 | x1, y2
0 1 1 0 1 0 | x2, x3, y2 ****
0 0 1 1 0 0 | x3, y1
...

The row with all those asterisks is mathematically considered an alternative but should not be considered due to the exclusion list--it includes both x2 and y2, which are incompatible.

I'm trying to find a worksheet formula that will check whether an incompatibility defined on the Proposa... Read more

Read other answers
RELEVANCY SCORE 107.2

OK... Here's something I have been looking at for a while and getting myself all in a tizz...!!!

I have 2 worksheets within 1 workbook - see attached example. The first contains unique references (ABx in Column A) with associated processes in Column B. Each cell may contain more than one process - comma seperated. The second sheet contains unique references (XYx in Column A) which map back to ABx references in Column B (again, each cell can contain multiple mapping references - comma seperated).

e.g.
Ref - Process
AB1 - Create, Update
AB2 - Create, Read
AB3 - Delete, Update

Ref - Maps-to
XY1 - AB1, AB2
XY2 - AB1
XY3 - AB2

Now, what I am looking to do is within sheet 2 (containing the XY references mapped to AB references) is create a column which will display the corresponding processes without duplication.

e.g.
Ref - Maps-to - Process
XY1 - AB1, AB2 - Create, Update, Read
*Note that AB1 and AB2 both contain Create which should only be returned once.

I have tried using =VLOOKUP(B2,'Reference Sheet'!A:B,2) but this only uses the first reference to return results and therefore using the above as an example the result would not include "Read".

Any takers? Hope someone can help...

Thanks in advance!
 

A:Solved: Excel: Cross Reference multiple values in cell

Read other 16 answers
RELEVANCY SCORE 107.2

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
RELEVANCY SCORE 106

I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.

Sub Email_Out()
If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
End If
End Sub

All help is greatly appreciated!
Mikey
 

A:Solved: VBA email excel workbook based on cell values using; If Then ElseIf Please he

Read other 16 answers
RELEVANCY SCORE 106

Hello,

A pretty inexperienced VBA'er here.

I have 2 named ranges:

- LastStatus = "J8:J500"
- CurrentStatus = "K8:K500"

What I would like to do is create a loop to copy the cells in CurrentStatus over to the LastStatus column if and only if there is a value in CurrentStatus (column K). If there is no value in column K and there is a value in column J, I do not want change the value in column J.

Any help would be greatly appreciated.

Brian

 

A:Excel - Conditional Copy / Paste (Same Worksheet) based on cell values

You don't need a loop.

Sub test()
Range("LastStatus").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]<>"""",RC[1],"""")"
Range("LastStatus").Value = Range("LastStatus").Value
End Sub
 

Read other 1 answers
RELEVANCY SCORE 103.6

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:
Code:
Sub Unpost()
If Selection.Font.Underline = True
Then Selection.ClearFormats And Selection.Clearcontents
Else
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.

http://www.virtualsplat.com/tips/visual-basic-fso.asp

Rollin
 

Read other 1 answers
RELEVANCY SCORE 103.2

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
RELEVANCY SCORE 103.2

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.

....A.....B
Apple.........
..........Fire
..........Ice
..........Snow
Peach
..........Sleet
..........Rain
..........Fog

Here is how I want the data to look

...A ...........B
Apple
Apple.......Fire
Apple.......Ice
Apple.......Snow
Peach
Peach.......Sleet
Peach.......Rain
Peach.......Fog
 

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.

Code:

Public Sub CopyData()

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

ActiveCell.Copy
ActiveCell.Offset(1, 0).Select

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

Loop

End Sub


Rollin
 

Read other 2 answers
RELEVANCY SCORE 103.2

Hello,

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
RELEVANCY SCORE 103.2

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
RELEVANCY SCORE 102.4

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)
Range("A1").Select
Selection.Copy
Sheets("Results").Select
Range("O54").Select
ActiveSheet.Paste
Sheets("info").Select

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
RELEVANCY SCORE 102

i enter a whole number (any cell) and the result is a decimal number.
ie: enter 4 - result .4
have tried diffirent things with no change.
 

A:excel - cell format.

Read other 7 answers
RELEVANCY SCORE 100.8

I have a spreadsheet that has about 10,000 cells over 20 or so columns and depending on the result value in each cell they are coloured differently.

Is there any way to write a sum formula that will scan the data area and sum up each set of coloured cells?

The results to colour of cell are not banded, the colouration depends on the source of the original data and value.

When the source data is refreshed, it is a manual process to change the colours of the cells.

Thanks in advance for any help you can offer.

Regards

Ben
 

A:Solved: Excel sum by cell format

Here is one way to do it:
http://www.exceltip.com/st/Sum_by_color_using_VBA_in_Microsoft_Excel/517.html
here is a slightly different way:
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
 

Read other 3 answers
RELEVANCY SCORE 100.8

I'm trying to type "4356400011157214" into a cell, and it keeps rounding down to
"4356400011157210". I've tried everything I know to type that last "4" into the cell.

I have no formula in the cell. I've changed the cell format to many different types to no avail. The current cell format is Number with 0 decimal places. Does anyone know why it keeps rounding down? If I correct the last digit to a 9 it still puts a 0 in there.
Thanks in advance
 

A:cell format problem (Excel)

i think 15 numbers is the most it can cope with - looking for spec

EDIT
just looked in help "Excel specifications and limits " and 15 digits is the max precision

Feature Maximum limit
Number precision 15 digits
Largest number allowed to be typed into a cell 9.99999999999999E
 

Read other 2 answers
RELEVANCY SCORE 100.8

I am using vba code to format some cells in a spreadsheet. When trying to set the horizontal alignment, it just doesnt seem to work. No error is generated but the alignment is always Left.

The code doing this is in an Access2K module. Here is the piece i am trying to use:
Code:

With ExcelApp.ActiveCell
.Font.COLOR = vbBlack
.Font.Size = 10
.Font.FontStyle = "Bold"
.Font.name = "Arial"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
.Interior.COLOR = 10092543
End With

Anything extraneous that i am missing?

TIA
 

A:VBA Excel cell format not working

It looks ok ... mine works and mine is;
ActiveCell.Select
With Selection
.HorizontalAlignment = xlCenter
End With
 

Read other 3 answers
RELEVANCY SCORE 100.4

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.

Thanks
 

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!

Rollin
 

Read other 3 answers
RELEVANCY SCORE 100.4

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:

$A$1:$A$150

Rgds,
Andy

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
RELEVANCY SCORE 100.4

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?

Thx.

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
RELEVANCY SCORE 100

Hi again all,

Need a little help and this time I think it is possible I just lack the computer know how to pull it off.

I have attached a summary spreadsheet so I can reference as I go but I will give a little back story first. I want to create a spreadsheet that I can view and at a glance see who is traveling on a particular date and when they are due back. The problem is that the personnel will travel several times a year and I don't want to just create a giant spreadsheet with loads of dates.

Cell A3 is just the persons name and serves as no consequence for what I am asking.

In cell B3 and C3 I want to be able to display when the person is NEXT traveling and when that person is NEXT due back. Now this is the tricky part, I want this to automatically change the value of the cells after the date has passed.

In E and F I have put example dates. So lets say the 10th of July passes I would like the Outbound to change to 20th of Aug, and so forth. The same would apply for the inbound.

My thoughts were I could just dump all the dates at the bottom of the spreadsheet off the page break and have the text white so it is not visable and the sheet will be neat and tidy. There will be several people to track and I dont like clutter.

If anyone is particularly clever, could I even host all the dates for cells B3 and C3 on sheet 2 of the spreadsheet??

Once again, any help would be greatly appreciated.

Thanks & Regards
High
 

A:Format a cell in excel depending on date

Read other 8 answers
RELEVANCY SCORE 100

I am trying to create a macro that will allow me to change the font of a word in Excel. I am able to change the entire cell, but would only like the word that I am searching on to change, not the entire cell.

I have attached a file with the macro that I use now to highlight the entire cell, but would like to change it. This file does not contain the data that I normally look at on a daily basis (usually 8,000 to 60,000 lines of data and more data in each cell). In this file, I would like to change the format of the word "door".

Any help would be greatly appreciated

Pkelly99

Copy of Macro:

Sub Key_Word_Search()
'
' Key_Word_Search Macro
' Keyboard Shortcut: Ctrl+q
'
InputMsg = "What key word would you like to find?"
InputTitle = "Key word"
DefaultText = "Enter key word"
KeyWord = InputBox(InputMsg, InputTitle, DefaultText)

Cells.Replace What:="door", Replacement:="door", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

End Sub
 

A:Change format of a word in an Excel cell

Read other 6 answers
RELEVANCY SCORE 99.6

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
RELEVANCY SCORE 98.8

update on the below question:I think something is wrong when I opened the .csv file.If I leave the file open then run the macro, it works ok. If I open the file within the macro, I get the format problem.Any advice?Thanks!Hi,

Please can someone give me some advice on this?

I am writing a vba code in Excel 2003 to copy from a source .csv file and paste into xls file.

First, I tried this using macro recording, it worked fine. so I recorded the macro and tried to apply it. Something strange happened.

One of the columns is date, and originally is format Date (*01/01/01). If I manually copy and paste, the format is fine. If I use the macro, some (and only some) of the cells in this column will change format to General and align to the right, while the others keep the original Date format and align to the left.

How did this happen?

I viewed the .csv file using Notepad, and cannot see any difference between these dates that end up in different format.
And if I manually copy/paste, there will be no format issue at all.

Please can someone help?

Thanks!
 

A:copy/paste in excel macro changes cell format

upon a closer look, i think I found some clue, but still far from a solution, here is what I observed.

for date 09/07/2006, excel interpret it as Sept 07 06
for date 20/06/2012, excel interpret it as June 20 2012

please note the year format is different!

any advice pls?
 

Read other 1 answers
RELEVANCY SCORE 98.8

Hello.

Would anyone know how to use conditional format when the same word appears twice in one cell?
In the example below, A1:A4

A1 apple
A2 apple, banana
A3 apple, apple, banana
A4 apple, apple

one cells A3 and A4 should be highlighted using conditional format
 

A:Excel Conditional Format if text appears twice in one cell

will there always be a , between the words
if so try

=AND(A2<>"",NOT(ISNA(MODE(FIND(","&TRIM(MID(SUBSTITUTE(TRIM(A2),",",REPT(" ",100)), (ROW(OFFSET(A$1,,,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,",",""))+1))-1)*100+1, 100))&",", ","&TRIM(A2)&",")))))
 

Read other 2 answers
RELEVANCY SCORE 98.4

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
RELEVANCY SCORE 98.4

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

A1:A5
A4:A8
A7:A11
A10:A14
etc

If not can anyone suggest another way of doing it ?

Regards

Flynn
 

A:Excel 2007 - use autofill on cell ranges

Read other 12 answers
RELEVANCY SCORE 98.4

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
RELEVANCY SCORE 98.4

Hello Turbodante

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

Thank you for your help and quick response.

Regards

Flynne
 

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
RELEVANCY SCORE 98.4

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
='Folder[Worksheet.xlsx]QC'!$E270
syntax
But the IF statement should work
 

Read other 3 answers
RELEVANCY SCORE 98.4

Greetings,

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
RELEVANCY SCORE 98.4

Hi...

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
RELEVANCY SCORE 97.2

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?

RAS7827
 

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
RELEVANCY SCORE 97.2

Hi,
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:

=REPT("&#9642;",A1)&REPT("&#9643;",100-A1)​
(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.

G
 

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

Read other 6 answers
RELEVANCY SCORE 97.2

Hi:
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")
OutApp.Session.Logon
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
RELEVANCY SCORE 97.2

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.

Rollin
 

Read other 2 answers