Excel 2010 :: Maintaining Format In A Cell When Using Formula
Jan 10, 2014
I just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.
Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)
The data looks like this:
A1 = formula resulting in 1.76%
B1 = formula resulting in 1.90%
C1 = formula resulting in 1.69%
D1 = C1*B1/A1 resulting in 1.8218147382920100%
The desired result is 1.82%.
View 4 Replies
ADVERTISEMENT
May 16, 2014
I pulled a set of financial statements from a database into Excel. All of the values are hard coded but I would like to make them dynamic, while maintaining the original content. For example, If cell C3 is a revenue figure for 2012 ($1,000) I would like to link it to cell C5, which will be a currency conversion rate (2.0). So I would like cell C3 to read =1000*C5, which would equal 2000, and for cell D3 to equal 2013 sales*D5, etc. I would also like this to go across a specified range so that I can also change operating expenses, taxes, etc over a number of columns (dates) that I define, all linking to the conversion rate for the given year. Having another tab where you link and anchor is an option but I would prefer something in VBA where I can qickly include this functionality with new workbooks that I download.
I am using Excel 2010.
View 5 Replies
View Related
Jan 16, 2013
I have a large spreadsheet that I would like to indicate the location of the cells (Change cell color or text color) which contain formulas. My thought is that conditional formatting would be the easiest way...
View 3 Replies
View Related
Feb 26, 2014
I need conditional format in column Q which highlight in red, any date over 14 days old.
This column also contains texts (non-dates) and these need to be left alone.
I've experimented with a few bits of formula I've found online, but nothing has worked . .
I'm on Excel 2010.
View 3 Replies
View Related
Dec 17, 2011
i am trying to work on a database that has some errors that i want to correct the issue is that some of the cells have data that is listed last, first instead it needs to be listed into first last.
I can not seperate this into 2 fields as it would break the program this database belongs to.
this database is linked from excell to access i am using excell to update an access database
i use office 2010
View 9 Replies
View Related
May 5, 2014
In cell 'I6' if I enter the text "Balance" I would like it to display as "Actual" but if any other text is entered I would like it to display the text entered. How to do this using custom cell formatting. (Currently on Excel 2010).
View 2 Replies
View Related
Jan 28, 2013
I am looking for a vba to use a button to save a file using the cell as its name and save the file in xlsm format. I am using Excel 2010.
View 12 Replies
View Related
Feb 28, 2007
I have a table where alternate rows are formatted with interiror colorindex 15. Macros will result in 1 or more of the rows in the table being hidden (this can be consequtive rows).
Obviously, when a row is hidden (unless that results in 2 consequtive rows hidden) the alternating row format no longer works (ie either 2 grey rows or 2 nofill rows together)
I am struggling with the code to set the format when a row is hidden. I loop through the table to set format, fine if no row hidden. But when row is hidden everything I try to do to take account of the hidden row results in an endless loop when the hidden row is encountered.
View 7 Replies
View Related
Jan 29, 2014
I need to make a cell display the contents of the cell immediately below it, regardless of if there are rows inserted at the referenced row or above or below it on the worksheet. ALSO I need to do this at several additional cell locations down the worksheet. Such as : A1 displays A2, A4 displays A5, A7 displays A8 etc.
Example: I want A1 to display whatever is in the cell immediately below it. In this case A2. I need to be able to insert several rows at row 2 and have whatever new value is now in the A2 position displayed in A1.
After the rows are inserted I need the cell that was in position A4 to continue to display the contents of the cell below it.
I tried using the formula in A1 of: =INDIRECT("A2") and it works good except when I insert the rows my similar formulas below the area where the rows were inserted continue to refer to the specific original cell and not the one positioned now below it.
View 3 Replies
View Related
Jan 1, 2007
I have a spreadsheet that has tons of entries for revenues and expenses. Some cells on the rows with revenues have a different format than these same cells on the rows with expenses.
The problem is that, when I sort the rows to organize them by date, Excel seems to only organize the values in the cells, screwing up all the formats, mixing the revenues format with the expenses format.
Is there any way to organize the information maintaining each cell's formats?
View 9 Replies
View Related
Jul 19, 2014
I'm using Excel 2010. Is there any solution which cover requirements given below.
1) I've converted a range in to table format (using Format as Table)
2) Need to Prevent pasting on data validation cell.
3) Need to Prevent change in cell format after pasting data (other than data validation cells as mentioned above) from same workbook or other workbook
4) Need to insert row in the table as per requirement.
View 3 Replies
View Related
Mar 2, 2009
Cell A1 could contain either % value (eg 50.0%) or could contain a numeric value (eg 50.0). (It's input is written by a macro that performs a sorting function, hence the mix of % and absolutes).
I need to copy the contents of A1 to cell B1 with a simple formula "=A1"
The problem is that Cell B1 will either show 0.5 or 50.0 depending on whether A1 is a % or number, respectively.
Doesn anyone know how I can output in B1 with the format from A1?
View 9 Replies
View Related
Aug 7, 2008
I have a worksheet that gets autofiltered by the user. I need take the unhidden data and copy it to a new worksheet.
Range("H18").Select
Dim sh As Worksheet
Dim Cell As Range
Dim Txt As String
For Each Cell In Sheets("Panel Check List").Range("H18:H5000")
If Cell.EntireRow.Offset(1, 0).Hidden = False Then
Cell.Copy
Sheets("Query Results").Range("A6").Select
If IsEmpty(ActiveCell.Offset(1, 0)) = True Then
ActiveCell.Offset(1, 0).PasteSpecial
End If
End If
Next Cell
View 9 Replies
View Related
Aug 1, 2013
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
View 3 Replies
View Related
Apr 4, 2014
I am using Excel 2010. I am extracting text and data info from a single cell and inserting the info into different cells. Let's say the following formula is entered into cell M28:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), "")
This tests cell J28 to see if the text string "IM" is present in that cell.
If it is, then the text "IM" plus the following eight characters become the result in cell M28.
If the text string "IM" is not present in cell J28, I want the contents of cell M28, that is the formula itself, to be deleted from cell M28. EG below:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), function here to delete this formula from this cell)
( logical test ) ( value if true ) ( value if false )
View 4 Replies
View Related
Jun 4, 2014
Excel 2010. When the result of the formula in Col. E is less than zero, I need to move the row to another sheet, and then delete the row.
I modified another poster's script as follows:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Move rows with negative value in E to Flagged Items sheet
Dim rng As Range
' Set Target Range
Set rng = Target.Parent.Range("E2:E200")
[Code] ........
The script works perfectly if a negative value is manually entered into a cell in Col. E, but not when the result of the formula produces a negative number.
I can't find a way to make the Case Is < 0 act on the resultant value rather than the formula.
Is this possible in the script or is there another way to achieve the desired result?
View 4 Replies
View Related
Aug 27, 2012
I use Excel 10 and i'm on Window 7.
I need a formula to transfer the values in on cell to another designated cell. "AM" should be in the cell under "AM and "PM" should be under the cell marked "PM". If there's nothing in the space where AM or PM is, that should be blank.
View 3 Replies
View Related
Apr 30, 2014
Two formulas which I need in Cells E74 and E75.
Basically the formulas will depend whether the entry in D73 are Yes or No
in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70
in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70
Excel version 2010
View 4 Replies
View Related
Jun 20, 2014
How do I combine a formula and text in the same cell in excel 2010.
What I am looking for is the sum of a number of cells, followed by text.
e.g. something like =SUM(E4:E50)"/35"
If the sum result of cells E4:E50 was 10, I would be expecting to see visible in the cell '10/35'
How do I write this formula?
View 3 Replies
View Related
Aug 7, 2014
(Excel 2010). I have 3 cell that contain data. What I need is to compare the 3 cell and return the data that has different value into 1 new cell.
I have attach an example : test.xlsx
View 3 Replies
View Related
Aug 7, 2012
The formula I found is close but I need it to count, not sum.
=SUMIFS($C$36:$C$51,$B$36:$B$51,$B36)
Example data: [972 1] [972 2] [972 3] [214 1] [214 1] [765 1] [765 2]
I want the formula to count the different numbers in the second column based on the first column. The results should show, 972 equals 3, 214 equals 1, 765 equals 2.
I am trying to separate this out but it keeps putting this paragraph.
View 3 Replies
View Related
Jul 3, 2014
I am having a terrible time with Excel today. The version I'm using is Excel 2010.
I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.
The simple division is =13588/12
The output in accounting format should be 1,132.33.
Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.
View 7 Replies
View Related
Aug 30, 2013
My formula is =IF((C2+E2)>D2,"WIN","LOSE") the new formula if I wanted the cells with "WIN" to be green, and the 'LOSE' cells to be yellow. Excel 2010
View 6 Replies
View Related
Feb 24, 2014
I have data arranged in columns A-F. I am wanting to set it up so those groups are all based on the name in column A:
Name Location Quantity Notes Etc.
Joe Likes bread
Hates butter
Jane
Julio
Column A only takes up one space, while the other columns take up 3-4 spaces on average. I am looking to sort column A alphabetically while maintaing the spaces between so that the info doesn't become jumbled.
View 8 Replies
View Related
May 6, 2013
I have a spreadsheet with nested subtotals. i need to sort it based on one of the nested subtotals, but maintain the rows that comprise the subtotals, together with the subtotal.
is there a way to do this? I don't want a macro because then the whole project will be done as a macro. this is just a small part of what i am doing.
sample included. my goal is to sort by column F (ABS value) high to low so that rows 8-13 are together and maintain the subtotal and on top, then rows 34-36 are together and maintain the subtotal and are next, etc.
I am in Excel 2007.
View 1 Replies
View Related
Jul 1, 2014
Isn't there a checkbox somewhere where I can tell Excel 2007 to leave xls files as they are? My Excel 2010 doesn't urge me to save an old xls file format into xlsx but in Excel 2007 I can't find a switch (and I don't rememeber if I had to do anything in 2010). So ultimately I want to stay in compatibility mode when using old files.
View 2 Replies
View Related
Feb 5, 2013
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
View 1 Replies
View Related
Mar 20, 2014
I am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
View 3 Replies
View Related
Aug 5, 2013
I have to import data from an external source(oracle database) to an Excel(2013) table.
Now the data in the staging table in the database keeps refreshing/changing, However in Excel i need the data to come into a new row everytime instead of refreshing the whole table and looking like the staging table in the database. So basically i need to build history in Excel.
View 3 Replies
View Related
Jan 16, 2014
Open a brand new spreadsheet whether the format is set to "General" or "Text" it will not let me enter numbers. For example, when I enter 2007 in the cell it displays 20 07 not really that big of space between the two zeros - but still a space. I can't format this no matter what I do.
View 1 Replies
View Related
