We have 3 PCs, all running MS Office 2013. On 1 of these machines, it is doing strange things with formatting. If you open a document or try to paste anything into certain documents, it decides everything is currency format and assigns all sorts of wrong formatting to the entire sheet, or the entire document. There may be some cells in the doc that are indeed currency, but only a small proprtion. If I open a new, fresh document and paste into that document, it does not do this, it seems to work normally, only applying currency formatting where it might be applicable. On some larger docs that have this issue, no matter what I do, it just continues to apply these strange settings.
How to make EXCEL 2007 Automatically convert all my CURRENCY symbols in all cells formatted for CURRENCY in THE SAME SHEET to the one and ONLY currency selected in JUST ONE cell ? (i mean, after conversion, all of them are Not just display currency symbols, but Calculable monetary values like regular Excel's built-in currency format)
I am a dummy in VBA so I really am not familiar at all if the solution is really one of VBA. I don't even know how to start programming VBA at all in Excel.
When I enter a number, say 2456 in this textbox why does it display "$2.45" instead of "$2,456". I've tried fiddling with the ###'s and .'s but it just gets worse.
What I am trying to do is display my output in currency format. The catch is that the values I am using are not from cells, they are from text boxes that are locked on a user form. The first text box is a base rate with a value of "$2000.00". Second comes a quantity text box with an integer value of let's say "3". Last comes a markup rate which is a text box with a rate of ".10". The calculation is fine, it although it returns "$6600" when it is critical that I display the two decimal places at the end. Here is my code for the calculation:
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
I enter in numbers, eg: 1, 2, 10, 24, 100, 1000 in a consecqutive cell range They display as: 0.01, 0.02, 0.1, 0.24, 1, 10. Default cell format is general. If I change cell format to Number (with default display of 2 decimal points) it displays: 0.01, 0.02, 0.10, 0.24, 1.00, 10.00. If I also specify no decimal points, it displays: 0,0,0,0,1,10
The only way I can get the numbers I enter to display (and store) as 1,2,10,24, 100, 1000 is to change cell format to text and THEN retype th numbers into the cells. But, then it creates the green error comment in top left of each cell.
I received an email with a WORDPERFECT attachment. I couldn't open so I downloaded application (COREL)from Internet. I finally got to read my WORDPERFECT file but found out that COREL had changed all my EXCEL files to WORDPERFECT. How do I change them back to EXCEL flies?
I currently have a spreadsheet with the following. I would like to convert the GL Date to just pick up the month. I have tried =mid(cell,4,2) but because this is a excel date it will not pick up 01 as the excel number for this date is 410001
In the spreadsheet attached, i have a formula now() which will update the current time in column B if i select opted from column A and similarly in column D the current time will get updated when i select option in column C.
However when i first select the option in column B the current time gets updated but after a while if i select the option in column C the time in the column B is also getting changed.
May be there is a different formula, Not sure what is the trick ?
I have an the excel book with sheets that are password protected so that the users cannot delete rows or columns.
In each sheet I gave permissions for certain ranges that need to be filled out only to specific users that need to fill out those cells.
The users are located and managed in the Active Directory.
The point is that each user can only update his/her sheet within the Excel book.
It was working for a day or two and then all of a sudden without any changes all the permissions were gone and every user was able to edit every sheet.
When I combine 3 reports (which we get out of a system) into 1 big file, the date format remains the same (mm/dd/yyyy and right alligned). Same happens for most of my colleagues.
When 1 particular colleague goes and combine these reports, I've noticed that some of the dates are showing as text? (dd/mm/yyyy and left alligned).
I am trying to set up an holiday card on excel which I have the template for but I need an email sent to me, the manager, each time it is updated by my staff requesting Annual Leave. Each member of staff will have a program each.
I have a spread sheet which totals the amounts in 2 columns D and E, which are pounds and pence. The formulas for each one are:
Pounds
=SUM(D3:D8)+ROUNDDOWN((SUM(E3:E8)/100),0)
Pence
=MOD(SUM(E3:E8),100)
These work very well and give me the correct figures.
What I would like to do is to add up the values of each row based on the criteria of column C and have just one value in ONE cell (as shown on lines 13 & 14), so for instance:
When added together the values of rows 3,4 & 6 are 7.25 (based on a value of "W")When added together the values of rows 7 & 8 are 5.00 (based on a value of "F")
I have tried to show this as an example in the image below:
I have a lot of formulas that I don't want to be changed or deleted, but I still want the user to be able to enter data into the cells.
If I use "Protect sheet" I can't select a cell or enter something into it. I sure there is some setting I have to change, but I'm not sure what to select.
Iv changed the security settings to my name (along with admin) and removed 'everyone'.now when i go into my folder, everyone else's excel sheet is there but mine has gone.
I have an Excel 2003 file that contains hyperlinks to OneNote notebooks on a Sharepoint site. An Excel macro looks for these links and determines the full hyperlink address which is then assigned to a variable. An Outlook message is generated which includes the hyperlinks.
The hyperlinks work in the Excel file. I can also copy them from OneNote and manually paste them into an Outlook message and they work. However, when I obtain their full address and transfer that to the email through code, the links do not work. The hyperlink address from OneNote starts with "onenote:http" which is not recognized as a link.
If I can do this manually, there must be a way to do this with vba. Are there characters I need to include in the OneNote hyperlink address to make this work? Is there another way to transfer the working link from Excel to Outlook?
In Excel 2003 I have Manually Grouped a number of rows. (Data>Group and Outline>Group).
If I use the buttons with the numbers 1, 2, 3 (indicating an outlining level) to collapse or expand the outline I receive the Error Message: "Cannot shift objects off sheet."
If I use the collapse button (-) or expand (+) button the data collapses and expands as I would expect.
I am aware of the normal issues surrounding objects and outlining and have ensured that I have changed the properties of all my of the objects in the sheet to "Move and size with cells". There are no comments in the grouped rows.
The following formula was, several weeks ago, very graciously offered to me from one of Excel Forum's contributors.
=SUMPRODUCT(--(MOD(ROW(E8:E6782),2)=0),E8:E6782)
My request was to find a formula that would add each 6th row starting in row e8 (e8+e14+e20+e26+e32 etc. through e6782) in column "e" when the column was 6782 rows deep from top to bottom. (i am not trying to add every number in column e, just each 6th row, starting at e8 and going through row e6782).
I entered the formula into my spread sheet and, voila, I had a sum that I assumed was accurate for my spread sheet of ticket sales. I began to question the functionality of the formula when I altered the E8:E6782 parameters (which represented the gross ticket sales) to E4:E6778, in an effort to sum up the E4 values e4,e10,e16, e22,e28,etc. . . (which represents the net values after commissions were deducted). The difference in the two sums (e8 values Versus the e4 values) was incorrect and did not represent the appropriate commissions (which should have been 15%).
I have a Log In/Log Out report that I need pulling data from.
Ultimately what I want to do is get the amount of time my employees were logged out.
I am using Excel 2003. Attached is an example of the data I'm working with.
Sheet 1 contains the data, and Sheet 2 will be the summary.
So, looking at Bob's times: If column A contains Bob (A2 in this case), and the same row in column G (G2) contains 79, subtract the contents of the same row in column F (F2) from the next row down in column D (D3) (which would be D3 - F2). That result should populate on Sheet 2 next to Bob's name in h:mm:ss format.
If there is nothing in the next row down in column D, then do nothing.
I would like to leave the data as is, if possible. There are extension numbers next to the names in the data. If possible to ignore those when rolling up to the summary that would be useful. Also, on the summary sheet, if the employee is not on the data sheet (if they had the day off) they can have 0 or NA or something to indicate there is no data for them.
In Excel 2003 the Status Bar is missing. I know to go to Tools, Options, View to view and hide the Status Bar however as i cannot see Tools (as Status Bar missing) is there another way to get the Status Bar back?