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.
I have two spreadsheets, one named Tracker, the second named Data. Data is a central database spreadsheet with account #s and Tracker pulls information from it. A team is assigned an account based on the last two #s of the account #, called term digits.
Goal: A VLOOKUP to where if a column equals the term digit range (example, any accounts ending in 00 to 09), it will pull the account # from Data to Tracker.
That part is easy, the tricky part I'm having issues with is that I need it to do the search mentioned above AND search for account #s that end in 60.
So: search for 00 to 09 and 60, pull the account # from Data to Tracker once found.
Edit: Term Digit is on column B and the account # is on column A - both in the Data spreadsheet
I'm using below code to add number of buttons and assigning macro to all of them at run time.
Code: j = 1 Do ActiveSheet.Buttons.Add(2.25, Top, 66.5, 14).Select With Selection .Caption = "play " & j .Font.Size = 8 .onselection = "mymacroname" End With Top = Top + 15 j = j + 1 Loop Until j = I
What do i write in macro to know the caption of the button being clicked?
Essentially, I'm arranging an excel spreadsheet to organise my ongoing sales and keep record. I would love to have a formula to calculate the appropriate comission for each sale.
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 think I'm using the correct terminology with the term "variable", but to explain what I'm trying to do, I want to get RAND() to hold the first returned variable so I can compare it to other places RAND() has been used & if the returned value is the same as the other place, then run RAND() again.
I'm trying to say in the formula above that if rand = what was already in A4 then run random again -- but this doesn't keep if from returning the same value as A4 on the second pass.
What would work is something like (where X is the assigned variable):
I have a dynamic list of items which I have to assign serial numbers to. If the serial numbers go in sequence, it would have been easy. But the problem is, they don't.
They go from A1 - A9, B1 - B10, C1 - C10 .... Is there a way I can assign an array to ensure that once the macro hits A9, it starts from B1, and once it hits B10, start from C1?
I use Excel 2003. Can I assign different macros to a checkbox depending on whether the box is CHECKED or UNCHECKED? In other words, if I link the box to A1 and the value there is TRUE, can I run a macro based on that value? Then, if the value is FALSE, can I run a different macro based on the FALSE value?
I'm building a time sheet. Some of the time I keep track of is billable per hour, some is billable as a complete job. I'd like to use a checkbox to indicate if the time in a particular row is billable as a complete job. If it is, the time would not only be included in the total time for the column, it would also be accounted for in a separate worksheet. I'm thinking macros are the way to go here but maybe there's something better?
