My spreadsheet has a column formatted as 'currency' (because it's a column of prices).
I need to have accurate values in the fields, including pence, eg. '£24.32'.
However the vast majority are round numbers with no pence eg. '£34.00'
So I am looking for a way to not display the pence when their are none, eg. '£34' rather than '£34.00', but I don't want to round to the nearest pound.
I have a mileage expenses form from work that I am trying to fill in using Excel, instead of filling it in by hand.
Column R shows the miles travelled.
Column S shows the Rate per Mile.
Column T (should) show the pounds.
Column U (should) show the pence.
Searching has led to T11, for example, using the formula below to show the pound value.
Code:
=INT(R11*(S11/100))
However, I am having less luck on finding a way to display the pence amount in U11.
Ideally, I would like the cells in column U to show the pence without a leading zero or decimal point and just the pence value only.
I have found these three different ways to separate a full name in say column A row 1. But when i insert different cells into the equation i get lossed. What is the proper way to separate a collection of full names into first middle and last. Do you highlight the list and put in one general equation or do you have to repeat these steps for each individual name in each cell?
First Name
=LEFT(A1,SEARCH(" ",A1,1)-1)
Middle Name
=IF(ISERROR(SEARCH(" ", RIGHT(A1, LEN(A1) - _SEARCH(" ", A1, 1)), 1)) = TRUE, "", _LEFT(RIGHT(A1,LEN(A1) - SEARCH(" ", A1, 1)), _SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", _A1, 1)), 1) - 1))
Last Name
=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-IF(C1="",1,2))
I would like to enable my user to search on any one of about 4 columns, when the result(s) are found I need to return the full row to a result form. I've had no problems getting the information from the form to the worksheet but I've run into a few problems now. I should be able to be able to return the rest of the row with the result if I can determine a way to return the row number. Anyway these are the things that I need (in a nut shell):
1. Return a row number
2. Pass values to a form who should be able to change according to the size of the array
3. Have an array of variable size, with variable headers.
I have weeks in format YY-WW (this week is 09-18) and wish to calculate the full date for the week commencing day for that week. '09-18 is in cell S8. To work out the date I currently have: =DATE(LEFT(S8,2),1,RIGHT(S8,2)*7-8)-WEEKDAY(DATE(LEFT(S8,2),1,3))
Which I thought was working fine, but turns out to be the WC date for 1909. I'll probably have to change the -8, that's not a problem, but I can't work out how to get it to 2009.
I have a macro which searches for the latest date down a column and across particular rows. Extract of the code is shown below:
View 11 Replies View Relatedhow to make the full range variable...
View 9 Replies View RelatedThis formula looks at a cell and replaces the full date with jst the month
and the year....but some cells are blank, in this case this formula sends
back "100"
I need to add something to this formula that when the cell is
blank...instead of putting "100" as it does now, I need it to put
"Unscheduled"...
Like "If 100, then "Unsdchedule"
I want only one sheet should in Full Screen Mode rest of the other sheets remain normal view(With Ribbon).
View 2 Replies View RelatedI am looking to get a user's email address.
On Sheet A (Foreseen Absences) I have users enter their own data.
On Sheet B (email addresses) I have users' full names and email addresses.
I have the following formula entered on Sheet A to get the user's email address: =vlookup(C173,'email addresses'!A:C,3,0)
This works fine for staff who enter their full names, but I've got a few who are entering things like: G. Thoms or Marie which results in a #NA error.
Is there an easy way to update this formula to account for these instances?
I have cells A11 to G250. In column E11 to E250 there are lots of duplicate product descriptions. Any duplicates in that column (E11 to E250) I want to remove that full row leaving just a single row (A to G) for that product removing the full row A to G for the duplicates.
View 2 Replies View RelatedThis is a simple fine line crosshair which ALWAYS extends the full screen width and height. Excel users seem to be stuck with the little box cross. A full screen crosshair for Excel would allow one to instantly and easily see which row and column the cursor is on without clicking a cell or following from the cursor to the row/column with your finger on the screen. It may seem trivial, but once you have used a full screen crosshair, you just don't know what you've been missing.
View 9 Replies View RelatedI am creating a userform and would like to be able to automatically enter the name of the person using it. The way our network is set up is Username = 1st 5 letters of surname + 1st 2 letters of forename. Due to this i cannot use
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function
as this returns the login name as opposed to the full name. I also cannot use
Function UserNameOffice() As String
UserNameOffice = Application.UserName
End Function
due to the fact that the office settings are defaulted to "Any Authorised User" unless manually updated by the user.
The users full name is displayed at the top of the start menu, but I have so far not been able to find a way to pull that into my form.
How do I make my Excel-document automatically display in full screen-mode when it is loaded?
View 10 Replies View RelatedPrice - Bulk Price - Total price
And i need to calculate the total price (totalprice = (PRICE OR bulkPRICE * 2.5)
If Bulkprice contains a number i need to use that field but if it's empty i need to use Price.
If a cell in Column F = Closed Then, the entire row is filled green. If a cell in Column F = Open Then, the entire row is red with a strike through. I'm guessing this is simple, but I can only conditionally format on a cell-by-cell basis right now
View 2 Replies View RelatedI need to replace a full column with a certain word. It needs to start at a specific cell and needs to replace the full column until it hits an empty cell. I can't do a find and replace because the words in that column are all different words.
For example:
I need it to replace all of column F starting at F2 with a specific word and it needs to keep replacing until it hits an empty cell.
I have an excel workbook that I would like to
a) appear more app-like, and
b) show more data in the available space.
I do not want to go too elaborate by hiding (remembering and restoring) control bars and I reckon the View Full Screen is pretty much okay for my purposes.
I am currently invoking View Full Screen on Workbook_Activate but hitting the ESC key returns to normal view. I can already see my users hitting the ESC key for various reasons and ruining my master plan.
Is there are any way to prevent unintended ESC from Full-Screen or otherwise lock in the full-screen mode until revoked intentionally by macro (shortcut)?
I have an excel workbook that I would like to
a) appear more app-like, and
b) show more data in the available space.
I do not want to go too elaborate by hiding (remembering and restoring) control bars and I reckon the View Full Screen is pretty much okay for my purposes. I am currently invoking View Full Screen on Workbook_Activate but hitting the ESC key returns to normal view. I can already see my users hitting the ESC key for various reasons and ruining my master plan. Is there are any way to prevent unintended ESC from Full-Screen or otherwise lock in the full-screen mode until revoked intentionally by macro (shortcut)?
I have large workbook with various sheets. In my first worksheet I have some command buttons that call each sheet as needed. However, I would like each sheet to be displayed either as a web page or in a way that the user does not see all of the menus, bars and so on.
View 1 Replies View Relatedi have an excel workbook containing approx 20 worksheets. What I would like to do is make the workbook go full screen when the user opens the file and allow the user to save as into the same file path the workbook is stored when closed
View 2 Replies View RelatedI'm wondering what is the syntax to open a msoFileDialog box and select a file, resulting in setting a variable to the entire file path. If I use
msoFileDialogFilePicker
I'll only get the file name, without the path. I'd like to get the full path ending in the file name.
I am trying to create a spreadsheet the will populate/pull all tyre sizes that I have collected from different suppliers.
Tyre Size
165/80 R13
Size
Brand
Cost Price
Selling Price
Supplier
Cannt use Vlookup as it will list only one option.
I'm looking for away to convert the value of cells with a numbers that represents the days of the month as 1 - 31 to the full date.
As if the cell value is 3 convert to 3/3/2014 and so on. I tried changing the cell format which does covert it to 1/3/1900, then I experimented with DATEDIF but didn't get anywhere.
I am trying to create a system to organize my company products. Up until now we had no part numbering system so I created one. The part numbering systems goes as such
Brand Code.Product Code.Bike Code.Color
These are examples
S7R.SF.101.Black
S7R.SF.101.Blue
S7R.SF.101.Gold
[Code]....
What I would like is to create a formula to expand the part number back to its full length name using tables on other sheets. I have tables for the brand code, product code and bike code. What I would like to have it do is Lookup the first part of the code "S7R" on a column from the following page and return the data from the cell next to it. Then continue on to do the same for the second part "cs" and the third"101" and so forth.
Basically the top example above would expand to be
Strada7 Short Folding Aprilia RSV4 Black
S7R = Strada7
SF = Short Folding Levers
101 = Aprilia RSV4
Black = Black
This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.
View 9 Replies View RelatedI work for emergency services, and one of my colleagues has a GPS camera that snaps digital pictures and collects the coordinates where the image was shot. These coordinates are recorded in a database file readable in Excel. The emergency mapping software can load these points along with the images, but in order for the hotlinks to work (and thus be able to click on a point and load the image), I need a field in the database file that shows the full path name of the respective JPG image. As it is, the database file has a field (named ORIGINAL) that records just the filename of the image.
I need a field that contains the full pathname of the image. This is easily done in Excel, but I need an external script that will run this outside of Excel because I need to streamline the process. If dispatchers need to go into Excel and edit columns to make the data show up, they're not likely to bother.
I have a blank (not populated) column in the dbf (named INTERNAL) that I can use for the full pathname. What I want to do is begin with the text "C:/" and insert the text in the ORIGINAL column immediately following this. Is there a script, such as in a batch file, that will run this outside of Excel?
I seem to be stuck in "Full Screen" mode. When I go to the View menu, both "Full Screen" and "Normal" are highlighted. I can't seem to get out of Full Screen.
I am using Excel 2003, XP SP3 installed. This just started this am. I did download and install Microsoft patches this am. ???
I am able to save and use Excel but without normal view my formating buttons etc. are not available.
My workbooks have a column of state names that I need to replace the names with abbreviations. I am doing it manually with the Replace (Ctrl+H) right now, and it's very time-consuming since I'm doing it with numerous, large workbooks. I would be very grateful if there's a quicker solution. The state column is always the same (I). The states are United States and Canada, but I could edit if there's an existing solution from a different country, or modify a simliar work-around.
View 6 Replies View RelatedHow I can force the application to auto full screen and hide all toolbar?
View 5 Replies View Related