I have been trying to resolve my latest issue using various IF and Lookup formulas but havent been able to find the answer.
I have designed a spreadsheet that logs staff members hours. Staff input their hours worked in cells C12:BL12 and BM12 uses a simple sum formula to add them all up =sum(C12:BM12).
What i would like to do is input "S" for sick, "H" for holiday, "BH" for bank holiday etc and each one of these letters represent a number of hours adding in to my sum formula in BM12.
For example: Below shows a working week with the total hours worked per day underneath If H=8 and S=7.4, the total would read 39.4
I, too, am trying to use excel to the fullest. My first issue is, I would like to convert the numberic grade in one cell to a letter grade in another cell. I am not really good with all the vocabulary, but would love to learn it, and can copy a formula pretty well!!!! Not so good with functions and macros but would love to learn. All help appreciated.
I would like to sort words or data which has the First capital letter, words which has spaces and words which contains number... i have attached a sample file.
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
I am trying to do is extract the volume size of products in 'ml' from 10k plus products from a description field cell. this description field could also contain the weight of the product in grams so I cannot just do a search for a numeric string , it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
I have a column of several thousand entries listed as numeric with a scientific symbol eg.
1.4mSv 19.53mSv/1mSv (some have a mix and or alpha/numeric range)
I want to convert them to the numeric value only. I'm extracting to a chart which is not recognising the alpha and throwing the data out. I tried find and replace, trying various options within the 'replace format' tab with no joy.
I have a few thousand products codes (i.e ABCD123BLA08 or SHU267BLA) They are non standardised in length or structure. I wish to pull out everything upto the end of the third numeric digit.( ie ABCD123 and SHU267) I cannot use left as they are all different lengths. Ideally i would serach for a non numeric char after the number then use left up to that point. Search can't do this, FIND can't do this.
Summary ABCD123BLA08 would be ABCD123 SHU246BLU would be SHU246 I147ORT08-12 would be I147
19352510 C084111X AA 24253081 A001290U AA 19599291 48413321
I want to write an If Statement is a column next to each entry that denotes two options either Broker or Agent. Where the code is say 19352510 then Broker and where it is say C084111X AA then Agent.
Broker codes will never contain a letter. The Agent code will always start and finish with a letter.
I would like to assign a value to a row so I can enter different quantities in a cell in that row, and then sum the total of multiple rows.
Example;
I want to add up c3-c10, d3-d10, e3-e10. I want to make row3=$4, row4=$5, row5=$6 etc.
So c3, d3, e3, f3 cells will have different totals ex; I type 10 in c3, 12 in d3 and 15 in e3... but they are values of $4, so When I sum up a column c3 will actually represent $40, c4=$48, e3=$60 and so on.
Another example;
row3=$4 and row4=$5 i type 10 in c3, and type 5 in c4. the sum of c3 and c4 = $65
I've been doing a very simple table for my father's bass club. The club has one tournament a month and the 2 lowest weights are dropped. The table has a column of names on the left hand side, beside the name is 12 columns for one weight per month, right of that is a sum of the fisherman's catch weight in column O and to the far right is an equation that removes the 2 lowest weights =O4-(SMALL(C4:N4,1)+SMALL(C4:N4,2))
In the past I have put a 0 if someone didn't fish. He has asked me if it's possible to put DNF for Did Not Fish in lieu of a 0. This way they have that information and know how many people fished and caught nothing versus how many didn't fish. I need to know how to assign DNF a value of 0 for the whole table so that I can still drop the 2 lowest weights
What I'm trying to so is track weekly labor costs- my crew makes a rate for 6 days then an additional amount for a 7th day. I have attached an image of what I'm trying to do. Basically I want to add up the crew members "a" rate plus the "b" rate (if applicable) over multiple weeks in the total column.
I have created an add in for excel with all the macros that will be used for a particular workbook. I have some buttons in the workbook that I have macros assigned to them. I assigned them to the xls workbook before I saved it as an add in. Now, when one of the buttons is pressed, it opens the xls workbook.
how to assign the macros from the add in to the buttons.
I have a problem with a spreadsheet I am working on.
When a user clicks on column N in the sheet, I have a yes/no message box open asking if they would like to create an invoice for a customer, which opens an external spreadsheet.
The problem I have is that I can't seem to get the message box to perform seperate functions for each button (open the external spreadsheet 'for yes' OR Close the message box 'for no). I can only assign one funtion between the two buttons.
The code I have is as follows:
VB: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Not Intersect(Target, Range("N:N")) Is Nothing Then MsgBox "Do you want to create an invoice for this customer?", vbYesNo If response = vbYes Then Workbooks.Open Filename:=("H:Copy of Support Service Charges Form.xlsx")
I have a table that needs headers assigned to it. I have copied a link to view the example spreadsheets. The first sheet has the table and the second sheet has the headers. The code is designed to go to sheet 1 see what file name is being used and assign the header to the file name. Each file has two columns of data for Location 1 and Location 2, but have the same file name so it's assigns the first header, but ignores the second header. How to fix it so that it assigns it by the file name and also if it's Location 1 data or Location 2 data.
[URL]
VB: Code: Sub FindHeaders() Dim iRowH, iColH, iRowD, iColD As Integer Dim strHeader, strData, strTitle As String
I have a number of cells which are connected to various formulas. EX. A1 = 7. Well I want a formula in A2 which can look at A1 and if A1 = 5-6, then A2 = 1, if A1 = 7-8, then A2 =2, if A1 = 9-10, then A2 = 3, if A1 = 11-12, then A2 = 4. And so on.
I have a word, for example ROCK. I asigned numbers to the word. R=1,O=2,C=3,K=4. My Question: If I type in ROC it must return 123. If I type in KR it must return 41, etc. Is there a basic formula I can use to solve this?
is there any way wer i can assign the name of a sheet to cell so that watever i typ in the cell automatically becomes the name of that sheet? i have more than 2000 sheets
I am trying to write a code for simulating a stock price using geometric brownian motion. I have the function part down as follows:
Public Function SimStock(Initial_Stock_Price As Double, _ Expected_Return As Double, _ Volitility As Double, _ End_Time_Days As Double, _ Number_of_Steps) As Double SimStock = Initial_Stock_Price * Exp((Expected_Return - Volitility ^ 2 / 2) * (Number_of_Steps / End_Time_Days) * NRnd2() * Sqr((Number_of_Steps / End_Time_Days))) End Function
Now what i need to do is to apply this to an array. so that when i run the sub it will simulate prices from today up to some number of days that i specify. This number of days will be equal to "End_Time_Days" which is part of my function. Furthermore the simulation has to be such that it uses the above formula for the first entry but then replaces "Initial_Stock_Price" with the result of the preceding entry in each subsequent entry.
I have ranked the times of races for 30 participants. The results are in a column. I want to display the points given in the next column. The only points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts, 4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times, therefore ranks, will change with each race, so this information needs to be in every cell in that column.
NAME TIME RANK PTS Joe 12.05 2 5 Mary 13.00 3 4 Sue 10.57 1 6 Sam 15.04 8 0
I figured out the rank formula, but I can't figure out how to assign the points.
I've built a form in VBA which is working great. I now want to call it by creating a button in a worksheet and then assigning the Form macro to it so the user can just click a button. I've tried looking for it in the Assign Macro dialog and it isn't there (even though I've declared it as a public sub) and I can't call it from a normal Module as it says it's an undeclared variable.
