Value is actually $12.96777 which rounds up to $12.97 - how can I make excel stay at 12.96? (Round down?)
$402 billed hours, divided by 31 days.. $12.96777 – when the decimal places are removed to xx.xx, the values is rounded up to $12.97, is there a way to stop this from happening..
I have a cell that is going to be used for the quantity of a certain item. I would like to dummy proof this sheet as much as possible so that if someone puts in 21.5, that it will do something like change the value to either 21 or 22, since there can't really be a .5 of this item.
View 5 Replies View RelatedHow to round to significant figures using excel, its for a question in one of my college assignments and i cant seem to find any information.
View 1 Replies View RelatedI'm using Excel2010. I have a spreadsheet of loan information contained in rows. One of the columns lists the loan rate as a number (not a percent - so 5.25% is listed as 5.25, not 0.0525 and formated as percent). I need to pivot-table using the rate, rounded up to the nearest quarter of a percent (so 5.3875 rounds up to 5.50). Is there a formula to do this, or is it better to use a lookup table?
View 4 Replies View RelatedExcel 2007, Windows XP
I am concatenating some cells into an array. The amount fields should always have just 2 rounded digits following the decimal. What should change in the following VBA code to achieve that result?
Currently Cells(r, 6) & Cells(r, 7) could have these values:
1.5
24.78945678
45.2341
What I want is rounded values to 2 decimals:
1.50
24.79
45.23
The array is used as an input parameter in a remotely called function module, after logging into the remote system, SAP.
' delim is a | character
' Populate Myarray with data from all rows
' - Only from rows which are not hidden ' 05/23/2008
For r = 1 To row_count 'r is row number
if worksheets("JEMASTER").rows(r).hidden = false then
i = i + 1 'increment myarray index by 1
myarray.AppendRow
[Code] .........
i have an excel file(master copyvba.xls) in which i have my vba codes. My problem is that each time i open another excel file ,my vba codes does not work. I must open my master copyvba.xls to enable macros in my other file.how to make the vba codes available to my other excel files ?
View 2 Replies View Relatedwhere i'm going wrong with this?
If ThisWorkbook.Sheets("Obs Sheet").Range("C" & Counter).Value (=MONTH(TODAY())) Then
I'm not sure how to make VBA use the excel MONTH command.
How to make a fillable form in excel that will save the entry into another sheet and serves as a database.. like for example i have an invoice form after i enter all the data it will print and automatically save to the database.
View 9 Replies View Relatedi want to make a dropdown list from excel 2007. I try data validation then allow then list then source but i cannot make the sheet 2 as the source of my dropdown list in sheet 1. i uses excel 2007 and my OS is XP.
View 4 Replies View RelatedI'd like to make a conditional design for a row. If a certain cell equals "x" I'd like to color the whole row with grey My version of excell is 2003
View 7 Replies View RelatedI recall a few years ago that I located a program that allowed me to create an Excel spreadsheet and share it with others, but that locked the spreadsheet after a given period of time. I need to create a spreadsheet, but I want to put a time frame on it which requires users to request an access code from me every so many days. I don't want users in my business to be able to take the spreadsheet and use it should they leave the company. I know there was a subscription program that allowed me to do this, but I can't recall the name of it. It also allowed Excel to hide all of the formulas, even though the calculations worked.
View 2 Replies View RelatedI have made TAB using Custom ID Editor but it is available in particular file how to make appear that TAB in all excel ?
View 3 Replies View RelatedIs it possible to make excel hide cells containing certain text?
View 6 Replies View Relatedhow to make VBA code work in all workbooks. I created a new module in my PERSONAL.xls file, and added the code, but the code does not work when I open a new workbook. Using Excel 2007.
View 4 Replies View RelatedConditional formatting format: Boarder - bottom boarder
The problem when I insert a raw in the middle of the table the condition formatting is not applied. How can I make excel carry over the conditional formatting?
The only reason I formatted the data in a table because it'll automatically control alternating row background color when insert rows.
Is it possible to make excel template work only on 1 computer by giving any vba password (on lines of license number).
Aim is to ensure it works on only client computer, even if template is shared outside it becomes useless.(We often see clients audit team getting access to templates using it for thier purpose & distributing).
In same spirit what are best practices one can take like protecting formulas with password etc
I'm looking for a macro or function/formula to list & total a range of column widths. Having lots of columns with varying widths t'would rather not have to do it manually from each column's Properties.
View 9 Replies View RelatedExcel 2010.I'm using this code to print user's accessing a file:
[Code] .....
However as someone could just delete the printed user log in the Sheet2, and also you can't use this if you protect the sheet, which defeats the purpose.
I want it to print the log into a separate file. Can I add in the file path/name to this code somewhere?
I have workbook I would like to auto save to PDF copy file in different location every time the original file is save maybe some VBA code
View 4 Replies View RelatedI managed to make hidden sheets unhidden. However, after doing so, the first sheet stays active. How can I correct this? EXCEL 2010.
Module4:
Code:
Function Hide_Show(MySheets)
ActiveSheet.Unprotect Password:="****"
Application.ScreenUpdating = False
For Each ws In Sheets
X = Application.Match(ws.Name, MySheets, 0)
If Not IsError(X) Then
[Code] .....
Sheet WORKSPACE:
Code:
[Private Sub Open_BusinessDone_Click()
MySheets = Array("WORKSPACE", "BUSINESS_DONE")
Call Hide_Show(MySheets)
End Sub
Excel 2010ABCDEFGH1DATESAMPLECUSTOMERSHADEREF #ARTICLE"BRAND"REQD2REQ # CONES302.01.139118CNS-BACLAS-40463-2TEX-60-2000M PP(29/3)SILVER FALCON2402.01.139118CNS-BACLAS-40463-2TEX-30-3000M PP(40/2)SILVER FALCON2502.01.139118CNS-BACLAS-40463-2TEX-27-3000M
[Code] ......
I have like above 12 sheets (JAN-12 to DEC-12). I want to make a summary in new sheet base on bellow condition.
All the 12 sheets , data need to copy to new sheet & same time if Column "C,D & F" values are repeating in same sheet or any other sheets then that repeating rows column " H" values should summarized (only one entry should display in summary).
I am looking to copy a simple board game in Excel
Basically it would be a simple race. Containing 4 counters (blue, green, red and Yellow) and 10 squares each.
There would be 40 cards 10 blue, 10 green, 10 red and 10 yellow. Cards would be draw randomly and the counters move respectively. I would be thinking about starting with =RANDBETWEEN(1,10).
I have no knowledge of VBA/ macro but would welcome ideas with what formulas/ commands to use.
I have added some snapshots to show what I would be looking for.
[URL]
I have workbook I would like to auto save to PDF copy file in different location every time the original file is save maybe some VGA code is this possible ???
View 1 Replies View RelatedI'm making a custom BINGO board in Excel 2010 for a family reunion, so it doesn't contain numbers; instead it has words (like "has brown eyes" or "served in the military"). the first sheet is the board and looks like a traditional BINGO board with the same number rows and columns with a center FREE space. On the second sheet is the list of all the phrases I'm going to use, one phrase per cell, all in Column A.
In BINGO cells, I want it to use one item (phrase) in each box but NOT repeat within the board. I have the following code in each cell and it works EXCEPT, there is no code telling it to make each cell unique, so sometimes it may repeat.
=UPPER(INDEX(ColumnA,((RAND()*(Data!A1-1))+1),0))
If I have a set of number in a column. a1-12345 a7-12356 and a20-15487. how can I get them to show like this in columns Z.
12345
12356
15487
I am use 2007 and my data go down to cell 40000 in column A:A
Essentially all I want to do is create a procedure/function in Excel such as =SQLdata(3), where 3 could be customer ID and then the function would make a connection to SQL and perform the SELECT etc procedure and return the CustomerName in the cell with that formula.
I have excel 2007 and MS SQL where my data is stored. I understand that I need to make the connection to my SQL database, but I really do not know how to do this.
I am trying to use the codes below to find text "xmxy" and "xmx" within a column, then try to move the cells between the texts to the right by one column. I believe that the problem for the code below is that
Cells(j, "B").Insert (xlShiftToRight)
or If Cells(i, "B").Value = "xmxy" Then has limitations. I read that when you dim a variale as integer, it only can contain value between -32,768 to 32,767 . But I have more than 32,767 rows of data. I have already set dim i,j, etc. as long, but how do you set the cells range to recognize number larger than 32,767?
Sub ShiftRightbbb()
Dim i As Long, j As Long, lastrow As Long, rowscount As Long, count As Long
lastrow = Range("b" & 65000).End(xlUp).Row
For i = 1 To lastrow
If Cells(i, "B").Value = "xmxy" Then
For j = i To lastrow
Cells(j, "B").Insert (xlShiftToRight)
If Cells(j, "C").Value = "xmx" Then GoTo Nextgroup
Next j
Nextgroup:
End If
Next i
End Sub
Is it possible to get excel to work out a formula but only allow the end values to fall as a 0, 2, 3, 5, 7, or an 8?
For example, show a value of 1881 rounded down to 1880 or 1880 rounded down to 1888.
I've been trying to research how to do this for a while now and still can't figure it out. My impression is that this is fairly straightforward in Excel 2010 since the menus and ribbons are easily customizable but not so in Excel 2007.
So far I can get macro shown as a button in the Quick Access Toolbar. But the issue is that I can't organize them or customize the icons.
Is there an accepted practice for this?
I have been given a task to create a line graph who has 10 categories and each category has 5 different values. I need to plot a line graph, so that those 10 categories should appear on the Y-axis instead of X-axis.
I am using Excel 2010.