A B C
1 10 0 =B4
2 10 10
3 10 20
4 10 30
5 inputting new values -- > C1 changes it's value from =B4 to B5 as new value gets created.
How do I create an excel formula that changes itself (C1) to =B4 -- > B5 -- > B6 etc, whenever a new cell is created at B5 and 6 and so on.
I am trying to create a shared database that tracks training for about 100 people. I've built a workable sheet, but the only way I have to share it over e-mail (there is no office NAS set up, and we're on a military base so we can't establish wifi).
Is there a way to set up a workbook that can be updated on one computer and instantly reflect that information in the cloud?
My goal is to have each team responsible for updating their own form and then have that information update my master tracker.
I am working on a excel sheet that tracks thousands of records. Basically, have 400+ employee's who have scores that need to be tracked. The scores are % based... to start we have 15+ columns but all that matter for these calculations is probably 3. We have score 1 which their required to hit 90% score 2 which is 80%, they are seperate scores. Some people dont count so we disqualify the formula from counting a 0. My current forumla is...
COUNTIF(K418,"<90")+COUNTIF(N418,"<80")-COUNTIF(N418,"=0") where the rows change for each record. We are counting the # of times they don't hit their goal. They can only be held accountable for one score a month not for both scores so created a 2nd column which has =IF(O418>=1,1,0) so if it came back with 2 counts it changed it to 1. OK, so background explained
Now the tricky part... we have a running total per month of how many times they did not hit their goal. We have a column with the mm/yyyy. So we have a pivot table which breaks down the associates by name department etc... and once completely drilled down into it gives a breakdown of their score for every month and then if they did or did not hit their goal by referencing the previous field I discussed. Currently it has a running sum, but I am stuck on the twist I need to throw in... if they meet their goal for 3 months straight, 1... we will call it "occurence" drops off. So for example.... Jan 1 Occurence, Feb 0, Mar 0, Apr 0. So in this case, Jan, Feb, Mar need a running total of 1. Once april hits, the running total needs to go back to 0. So if they got an "occurence" in May, the running total would go back to 1 instead of the current 2 my sum shows.
I imagine there has to be some way I can do this, but I have been thinking about this and hit a brick wall. Any idea's at all? For reference column A is the mm/yyyy (which can be reformatted differently pretty easily). Column K is score 1, column N is score 2, those are the 3 fields I reference for my forumulas currently, the pivot table uses everything else.
Here is an example of one associate.... the top row is the average for the associate and the sum of "occurences"... I want to or plan to have a running total section added on the side however if possible. This associates running total should be 2 instead of 3 as there was a 3 month gap for Mar, Apr, May so it would have been -1 in May as explained above.
Doe, Jane103.7788.823
1/1/200998.5894.120
2/1/2009101.6270.591
3/1/2009110.61105.880
4/1/2009131.78117.650
5/1/2009102.0588.240
6/1/2009110.4658.821
7/1/200998.23105.880
8/1/200986.8270.591
9/1/2009100.3982.350
10/1/200 97.1694.120
I have a spreadsheet that tracks Auditing Dates. Cell A1 has today's date =Today()
Column B2 has the first Audit Date (hard keyed), cell C2 has the second Audit Date (formula =+B2+182), cell D2 has the third Audit date (formula =+C2+182), etc. . . I would like the format of the Audit dates to flag the last audit date in the row red if it is prior to today's date (cell A1).
I want to be able to create formulas where the output can act as the input.
For example, I tried to write a simple proof of concept script where you can convert between pounds and kilograms. So if you type in a value for pounds, it'll update the kilograms cell. And if you type in a value for kilograms - it'll update the pounds cell.
This is what I came up with:
I am having trouble creating a dynamic forumla in VBA. I used concatetnate to create a dynamic formula (below):
VB:
Sheets("Monthly Snapshot").Select
Range("L7").Select
ActiveCell.FormulaR1C1 = _
[Code]....
But - this is now the formula stored in the macro. So everytime I run the macro I get this exact same formula, not the new formula I created with the previous action. How can I get this new formula to show in the macro, or just have the macro convert the text in the cell into a formula by deleting the " ' " charcter on the front?
I have attached a spreadsheet of data that I would like to reduce.
In the workbook there is a set of data (rows 1 - 24) which are defined with random dates (row 2) and number criteria column A.
In the table below that (rows 28 to 37) I have set out how I would like to present the data. how to create an array formula that sums the above data months into the criteria of column A.
I have a large spreadsheet file with numerous worksheets.
Each sheet represents one month of the year and is named Jan05, Feb05, Mar05
etc.
In the "Summary" sheet I want to automatically reference a range in the
other sheets.
For example:
In the Summary sheet column A, I have the sheet names:
Jan05
Feb05
Mar05
etc.
In column B, (let's use B5 as an example) I want a formula that reads
something like:
=B4 + Apr05!C7
This seems simple enough, but how can I write a formula that 'automatically'
enters the sheet name (i.e. "Apr05") in the formula?
The row containing the data for Apr05 is only created when that month comes
along, in order to keep the file size as small as possible at any time.
I need to create a new variable according to the formula:
Suma( Foreign_Owenrship_Plant * Number of Full Time Employees)/ Suma(All Full Time employees in the sector)!
However previosly I need to filter the firms within a sector within a country according to a three digit ISIC code. Lets say in Albania, in year 2002 in the Transport sector there are 5 groups of 3 digit ISIC codes. Once I have filtered them then I can proceed with calculating the new variable. How can I do this using programming?
I want to create a formula to make periodic automatically for the next few years. see attachment my sheet.
View 7 Replies View RelatedI am looking to create a formula to get the initials of a name, where the the last name is listed first, followed by a comma.
Now, the names could be full names or initials.
So, it could be something like: Smith, John R - result would be JRSSmith, John Robert - result would be JRSSmith, John Robert Michael - result would be JRMS There were similar formulas that I found here, but these only got the last letter of the last name - in this case the following formulas would have resulted (with the above examples) in: JTS or the last example with Michael, JLS.
=UPPER(MID(A1,FIND(" ",SUBSTITUTE(A1," ",REPT(" ",2)))+1,1)&RIGHT(A1)&LEFT(A1))
=UPPER(MID(A1,FIND(" ",A1)+1,1)&RIGHT(A1)&LEFT(A1))
Note: While I am using Excel 2010, there could be users that are still using 2003.
I am trying to create an "if,then" formula that shows:
if cell E8 is greater than 171, then i'd like cell E9 to show the value of cell in E8.
See attached spreadsheet. I am trying to create one formula that will take the product of numbers in column B based on criteria in column A (want the product of %s in each year). Product if formula.xlsx
View 7 Replies View RelatedI am trying to do is create a total of only the last 90 days of entries from a list of $ amounts. Attached is an example. We are going to start keeping track of our cashiers variances but at the top of the sheet we only want to know what their last 90 days of entries equal.
This would be considered a rolling 90 days, so older variances drop off after 90 days, they would still be left on screen but would not effect the main total.
I have a very simple lookup formula (eg: ='data'!C1) that I need to copy down about 4000 rows. The difficulty is that I need to miss out 5 rows in between each paste but no matter what I try when copying it down, it throws itself out of numerical order. I have tried to type the formula manually for every row but its taking forever and is bound to go wrong at some point. I have attached a very small example to show what I mean - Imagin this example needing the formula to run down in order up to row 4000?
View 2 Replies View RelatedIm trying to come up with a password that changes daily that contains 2 constant numbers and some multiplication:
The formual would need to be like a string for example:
1 (the day * a number) 2 (the month * a number)
The numbers 1 and 2 are always the same.
I have a spreadsheet that has the month (number) in one cell.
The days are in columns A1 - A31
Everytime i try to do this in Excel, it trys to multiply every number.
I would like to create a formula that allows the destination cell display a number depending on a word that is typed in the source cell. The source cell and the destinating cell will remain the same two cells.
example:A2=IF(A1="WORD1",1.5,0)
If i type WORD1 in A1 (source cell) then A2 (destination cell) will display 1.5, but how do i make this formula work for a multiple of different words.
example:A2=IF(A1="WORD1",1.5,0 IF(A1="WORD2",3.0,0 IF(A1="WORD3",2.5,0)))
this formula doesn't work, but is basically what i am looking for the formula to do - WORD1 displays 1.5, WORD2 displays 3.0, WORD3 displays 2.5 etc where the source cell and the destinating cell remain the same two cells, it is just the word and its value that change.
i'm working on an excel file that consists of 31 sheets one for each day of the week, the information about how much we make each day is inputted on the end of each day, i've consolidated all the sheets into 1 big sheet and now i want to create charts over the production, so what i would like is a code that searches the huge sheet for Machine name (Found in Column A and Column I) then walk 5 cells to the right and find the number of litre's made by that machine on that shift.
The different shifts are
Formiddagsskift - Morningshift
Ettermiddagskift - Eveningshift
Nattskift - Nightshift
I will include an example of the data. The data should be pasted to the "Formler" spreadsheet. It doesn't matter if you don't wanna do this i can simply do it later i just need the code to search, and go 5 cells to the right and remember which cell it is, and make sure it is in the right "Shift" ie, all the 282 machines for morningshift are summed in c7 and c10 has eveningshift and c13 has nightshift.
Quite time consuming to search through 31 days of production to find the correct machines on the correct shifts. And the data changes for each month so this would be a great asset to me.
I want to create a formula which will add 0.20 to the value of a cell if its greater then 0.
Basically i have a list of cells some of which will have a value and i want to sum this list and add 0.20 if there is a number in the cell.
I am trying to create a quoting system and basically its a list of rooms etc with the wall lengths and i am adding 0.20 to each wall to allow for excess but for example if there are no measurements in bedroom5 i dont want the 0.20 added.
I receive daily reports from the people that work for me. The files are
saved in a known location and a known naming structure (initials - date.xls)
In a summary report I CONCATENATE their initials and the date along with a
path but cannot get the INDIRECT command to recognize the text line.
I have created a table which is an index of all the worksheets. Now i would like to create a formula using the index reference. I get the right result if i concatenate but the text doesn't turn in to an excel formula.
=CONCATENATE("=";A1;"!";"D4") A1 contains the worksheet name, if the worsheetname is "A" then i would like it to return a formula that is =Sheet1!D4
I need to create a formula that will allow me to use two sheets within the same workbook. I set up a dropdown box in my B2 cell in my first worksheet. My A4 cell is where I am trying to create my formula. I am not even sure how to say what I need. My drop down is filled with numbers from 1-211. I started my formula with =IF(B2=1,Sheet2!B3) this much I can get to work. It fills in the correct information. I do not know how to get it to continue. I need B2=2,Sheet2!B4.
View 12 Replies View RelatedI am trying to create a week ending formula in excel. I have a list of dates and in a new column I would like to display the week ending of these dates. I want the weeks to end on Sunday. For example, if the date column has the date 7/24/12 in it, I would like the week ending formula to spit out 7/29/12. What formula would achieve this?
View 5 Replies View Relatedcreate a formula using the COUNTIF function? I have a number in cell C1 and then numbers from C3:C65. I would like to produce a number count of how many items from c3:c65 are less than the value in c1 which is 24.
View 6 Replies View RelatedHow can I create a sequence with a formula starting in A1 that will give 1,1,2,2,3,3 and so on horizontally?
View 3 Replies View RelatedI keep getting errors with this vba code. I'm trying to make 10 new worksheets with the name of each worksheet in the Newname array. I'm trying to put the same formula (with the difference that each formula would take from a different "Newname" file. I'm trying to put first formula from A1 to A 560 and second formula from B1 to B 560.
Sub Namesheetformula()
For x = 1 to 10
Newname = Choose(x,"A","B","c","d","e","f","g","h","i","j")
Firstformula = if(isna(match('Master!'A1,'Newname'!A1:A560,0),"",'Master'!A1)
[Code] .....
I'm trying to create a dynamic sum formula off of a table of data which is then used in pivot tables and pivot charts. What I would like it to do, is show me the summation of values AFTER a filter has been applied. Everything I've tried returns a sum of the entire table (includes hidden cells). Perhaps I'm searching with the wrong query terms (this not called a dynamic sum formula?)
Using slicers and pivot charts, I need my account managers to be able to look at their territories quarterly performance vs. a "fair share" of company performance during that quarter. I.e. they would select the slicer for their region, and dates (ex april, may, june) and the pivot chart would show territory performance during that time, with a second column chart series which is company total from april/may/june (my dynamic sum) divided by the 8 territories...thus giving me what should be "fair share" during that quarter. Each territory has equal Opportunity, so this would allow them to see who is over-performing and underperforming in their territories.
way of joinng the contents of cells to create a formula?
path month subdept
x:path oct [dept.xls]sheet1!a1
x:path dec [dept.xls]sheet1!a1
is there any way of joining these to create a cell reference such as x:path oct[dept.xls]sheet1!a1
i've tried the concatenate and "& &" method to no avail.
a formula to create an email address. The person's name is in cell A1. The email addresses are all the last name + first initial followed by @ and the domain name. For instance, if name is John Doe, the email address would be doej@emaildomain.com.
The problem is, some of the names in the list contain middle initials, so I can't figure out a reliable way to discern the last name.
I know I can create a custom function in VBA using InStrRev to separate the last name, but if there is a way to do it using the functions already available in Excel, I'd rather do that.
Another question about pivot tables. In Excel it is possible to show different kinds of information in a pivot table like sum, total, min, max, average and so on. I want to create my own formulas in my pivots. Is this possible and if so how?
View 7 Replies View Related