I would now like to as remove the first column and add another column a and b. A would say "Service From Date" and column B would say "Service To Date" In addition I would like to add a column between "5202 Meals Provided" and "5221 S.A. Facility Lodging" a column that reads "7025 Number of Days Care" and that tallies like the rest of them.
View 14 RepliesI need to editing a VBA script and can't assist right now. Here is the VBA script as it exists today:
View 3 Replies View Related in my workbook: in sheet1 i have many cells with formulas which calculates values from sheet2. I also have a macro which is used often that will arrange and prepare data in sheet2, including adding some columns.
The problem is that after these columns are added, the formulas in sheet1 will be changed, even if I used f4 ($) in the formulas.
how can I freeze these formulas? I think a solution is to include the formulas in sheet1 in the macro I'm running for sheet2. But I would like to avoid this.
how do i add this =LOOKUP((E12+G12),{0,1500,6000,15000,55000;3.4,2.9,2.4,1.9,1.4})%*(E12+G12)+0.2 Into this fomula =IF(AND(E3>0,J3="Yes"),((E3+G3)*3.4%+0.2),0)
View 1 Replies View RelatedI'm tring do a simple formula: =CA3+BP3+BE3+AT3+AI3+X3+M3, but because the cells I am adding have formulas in them already it is returning a error. Can I add them using 'sumproduct' formula, or is there another way?
View 9 Replies View RelatedI need formulas to be automatic added when i insert row
View 2 Replies View RelatedHow do I add a column of cells with formulas without getting #DIV/O! for the total?
View 9 Replies View RelatedIs there a way I can add formulas dynamically to a sheet using VBA? I need to do cost calculations in the excel sheet for each company defined as an input from the user, so the number of formulas needed will change? Is there a way to write in the formulas to the sheet?
View 4 Replies View RelatedI have a workbook which is used to merge new customers data into letters and then monitor the progress of the job through to closure. I have a worksheet (New Jobs) where new jobs are entered which acts as the db for the word document and have assigned a macro that appends all new jobs to the monitoring worksheet (WO Open). I have 10 cells in WO Open worksheet to the right of the new data that I need to populate with formulas.
View 3 Replies View RelatedI have made a table with many dynamic formulas that I want to import into the Name Manager. It has the names in Column A and the "Refers to" formulas in Column B. Unfortunately, when highlighting the cells and using the "Create from Selection" feature of Excel 2010 it puts the Column B cell address in the "Refers to" field, not the actual formula. Is there another way to get these formulas into the "Refers to" field without typing them all out or coping and pasting them individually?
View 2 Replies View RelatedI want me to track our inbox. All jobs that go into the inbox get logged on a sheet and at the end of the day get transferred to an Excel spreadsheet. They now want to track how long s job sits in the inbox before it's logged out and worked on. Currrently we put a time and date for both logged in and logged out. I've attached below a sample spreadsheet and a brief description as to some restrictions.
Our hours of work is 7AM to 6PM. So we only want to add add up the time the job was in the inbox based on those hours.... ie if a job came in right at 6PM at night and was logged out at 7:05AM in the morning, the total time that job was in the inbox was 5 mins. Same thing applies for the weekends.
I'm suppose to be in charge of creating that part of the spreadsheet.
************************************************************************>Microsoft Excel - work time.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB3=
ABCDEFGH1********2*Job*#Time*inDate*inTime*outDate*outTime*spent*in*inbox*3*111:15*AM09/11/062:30*PM09/11/063*hours*&*15*minutes*4*24:25*PM09/11/069:45*AM09/12/064*hours*&*30*minutes*-*overnight*5*34:45*PM09/08/069:35*AM09/11/063*hours*&*50*minutes*-*over*weekend*6********7*hours*of*operation*are*7am*to*6pm***8********9*Explaination*or*#3*from*above….******10*Job*came*in*@*4:45*PM*on*Friday*but*was*not*removed*from*th*inbox*until*9:35*AM*on*Monday*morning.*11*Since*our*hours*of*operation*are*from*7AM*to*6PM,*the*time*that*the*job*was*sitting*in*the*inbox*was*3*hrs*&*50*mins.*12*This*is*figured*out*by*4:45PM*to*6PM*is*1*hr*&*15*mins*and*7AM*to*9:35AM*is*2*hrs*&*35*mins.**1hr,*15*mins*plus*2hrs,*35 *mins*equals*3hrs*&*50*mins*13********Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I've created a pivot table as shown in the attached image - I've had to hide most of the data but I'm not sure how confidential it is so thought I should play it safe.
excelforum.JPG
In between the Job Board Applications column and the Registrations column I want to have a Cost Per Application column.
In this fake example, the company that I've called 'Excel Forum' (imaginative I know) pay $1000 per month for their email marketing, so the cost per application from email would be 1000/1150 = $0.87.
Is there a way to insert a column in a pivot table that you can manually add formulas to?
I've tried replicating the pivot table underneath using cell=B6 etc. and the GETPIVOTDATA function then hiding the whole pivot table apart from the filters, but because there are a different number of subcategories every month the cell references don't work when the filter is changed.
I also tried to add a calculated field but this didn't seem to be what I wanted.
As a last resort I can add the cost per application manually to the raw data and include it as a column when making the pivot table, but this would be an ENORMOUS job to do every month so I'm in need of a better solution!
Is there a work around to pull data from cells in excel to another tab with standard language? For example the template language will stay the same except in example A's case the last sentence "0.0" hrs will vary based what's sitting in B34.
It's stating the formula is too long. Is there too much text?
Sometimes the link would be in the middle of the text as shown in example B below with dates 00/00/00.
EXAMPLE A
For any leave of absence that is granted for more than 10 days, time off is governed under the Disability Bank Policy. Under the Disability Bank Policy there is a 10 day elimination period which must be satisfied prior to gaining access to your disability bank. During this 10-day elimination period, you are required to use your sick time for the first 6 days (48.00 hours) and vacation time for the remaining 4 days (32.00 hours). After this elimination period, we use all the time accrued in your disability bank. Currently, you have a disability bank balance of "00" hours.
EXAMPLE B
After this disability bank is exhausted, you will use all remaining and future paid time off balances from your vacation/CME/holiday banks. Because your leave begins in 00/00/00 and ends in 00/00/00 we will advance you future paid time off balance thru 00/00/00. Once those balances are exhausted the remainder of the leave will be unpaid. Per our policy, should you not return to work, you agree to pay back any time advanced to you within 30 days of your separation from the company.
I want to make an excel workbook for a client. What I want to do with it is make a set of instructions telling them to add or insert as many rows in specific categories I have made for them. These options differ depending on the different duties performed. There will be an associated number (value for that duty, ex. duty might be Janitor and the number would be like 5 dollars per hour, so on and so forth), I want to make it to where there is a code that notices more rows and adds more IF statements or whatever statements are needed to keep adding those extra options. These values are connected to a data validation list that drives the if statement.
View 4 Replies View RelatedI have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.
I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:
1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table
The code I'm using for my "Add" button is:
[Code].....
The code I'm using for my "Delete" button is:
[Code] .....
The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above).
Attached File : Productivity Report (HH).xlsm‎
I'm trying to copy a huge list of formulas and formats from one work book to another almost identical, each time it adds the old workbook name title into the formulas, this is easy to do in the formula bar for individual formulas but can this be done for many?
View 3 Replies View RelatedI have data for 500 companies over 10 years, in three criterions: “EPS”, “DPS” & “PX” (i.e. earnings, dividends and price).
For each company I have four lines recording the data, and given a letter code indicating how the change in dividends and earnings have been according to the following
(Examples)
DD-ED = Dividends Decreased & Earnings Decreased
ND-EI = No Dividends & Earnings Increased
As can be seen below this code is present for each company
What I need help with is some sort of lookup function which takes the average of the price changes (which is given in numerical values) only for the companies which code is equal to the code in the reference field.
So something like: =AVERAGE(IF((MOD(ROW(D2:D2353)-ROW(D2)+1,5))=0,IF(D2:D2353"",D2:D2353))) (currently counting every 5th line, since there is 4 lines between respectively PX/DPS/EPS for each company) –But with a constraint indicating only to include the value in the calculation if the codes are the same (e.g. "DI-EI" = "DI-EI").
So that the value, which is currently 20.96% (which now includes ALL price values), would only include those for the respective group (in this case, companies in the DE-EI group).
I have this spreadsheet that I am creating schedules for my employees since our scheduling software at work is horrible to work with. It calculates total hours for each employee at the far right of the rows, and calculates total hours for each day in the columns. The formulas that calculate the hours for each day is set up to automatically deduct 30 minutes from a shift, for lunch, if they're scheduled for more than 5 hours (the gray shaded employee rows are for managers and that only deducts 30 mins if they're shift exceeds 8 hours).
I'm familiar enough to put somewhat complex formulas together, but I don't understand it enough to always make them work correctly. What I'm wanting to be able to do is add or subtract entire rows (add new employees or remove old), and have my formulas for calculating the total hours to still work. Right now if I delete a row the cell with the formula in it says #REF!. I can fix this by going through the formula and manually deleting everything in that contains those other cells, but the formula is huge, and it's pretty tedious going through the whole thing when it feels like there should be a simpler way of doing it.
TLDR: I want to be able to add/remove a row and have my cells currently on row 36 update the formula correctly.
I have a table of data 300 rows long with protected formulas in adjacent columns and at the bottom of the column. Some of the data cells are blank but the non-blank cells are contiguous and start at the top of the range (see Before Example).
a macro that would allow users to insert a blank cell at any point in the data table without losing any of the other data or disrupting the formulas. In other words I want to move all the data from that point to the bottom of the range down by 1 row. A before and after example for a smaller 10 row data set is shown below.
Before
AB1data aformula 12data bformula 23data cformula 34data dformula 45data eformula 56data fformula 67Â formula 78Â formula 89Â formula 910Â formula 1011formula Aformula B
After
AB14data aformula 115data bformula 216data cformula 317data dformula 418data eformula 519Â formula 620data fformula 721Â formula 822Â formula 923Â formula 1024formula Aformula B
Using VBA... I have Column N (eFIUnitRebate) which I want to swap with Column M (FFUnitPcnt)
Now with Column M as eFIUnitRebate and N as FFUnitPcnt I want to create an extra column (adjUnitCost) where Column N is and push FFUnitPcnt to become column O, etc.
Here is how the whole process should work:
Swap M and N
Insert new Column in N
Insert new Column in P
Insert new Column in Q
Insert new column in R
There is more information on the sheet after these columns so for all column inserts it needs to push whatever was there over.
So the layout is as follows:
J
K
L
M
N
O
P
Q
R
Units
UnitCost
UnitRebate
[Code] .....
These columns are thousands long, so I would like the formulas for them to be applied to the whole column as well.
Is it possible to apply a formula with a relative reference to an entire column? I have a very long, constantly updated datasheet for pivot tables and this would be a most helpful thing to know.
I wish to have a column of values. It will be dates. There will be a value in each cell (hours worked by employee) I want to know only the total hours for the last 30 calendar days. I still need to see previous values, but I just want to total the last 30
View 9 Replies View Relatedi have two columns
column1: status
column2: total time of status
infinite long
now what i need to do is count the total time a specific status has.
f.ex.
column1: status1, status2, , status1
column2: 0 days 4 hours, 1 day 0.5 hours, 5 days 3.5 hours
how can i make a formula that looks up
if (in column1= status 1)
time= column2
the result i need is:
status1 = 5 days 7.5 hours
status2= 1 day 0.5 hours
i thought about a countif over column1 but i dont know how to add the second column
I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.
Here is the data that I am trying to combine
C14: 2013
C15: Period 6
C16: Week 1
In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"
Is there such a way to do this?
I have data from F1 to AG1...I am unable to create a formulae & drag in excel in a column like
=F1
=G1
=H1.....If i drag after H1...it is reflecting as
=F4
=G4
=H4.
I'm working on a project that spans three sheets. A2:A151 of Sheet 1 and 2 is a list of names. The names may repeat and they probably won't fill all 150 rows. What I'd like to do with formulas is combine Sheet 1 and 2 into a single list in Column A of Sheet 3 without repeating names or showing blanks. I'm fine with multiple formulas and hiding columns, whatever needs to be done to make it work. Is this possible?
View 2 Replies View RelatedI have column AD where there are something like 2,500+ formulas. I would like to change the columns used in the formulas around. By that I mean the first formula is...
Code:
=SUM(AA6-X6)
...and I would like it to be...
Code:
=SUM(X6-AA6)
Now the thing is, the formulas are not continuous, there are breaks where there is a formula to sum say the previous dozen or so for example, then it skips a couple of rows and I have more formulas and then the sum say for the previous dozen for example and so on.
I just want those cells changed with the particular formulas as above.
What's the fastest way to display the sum of each column in one row. Here's a picture of what I want to do: http://www.filefreak.com/ppublic/33833/excel%20help.bmp now I want the sum of each column to be displayed in for example row 10. I have tried manually clicking "autosum" (sp?) but that will take a lot of time as I have about 400 more of these.
View 2 Replies View RelatedI am new to the Macro editing of Excel. I have a list in the first tab that I would like to click yes or no (how to set up a checkbox) and the appropriate column will pop up in the next tab with appropriate header. I would like it to be where the column will pop up in the correct order, so even if one is clicked in the middle, it knows where to put it.
View 6 Replies View RelatedI d like to ask for a vba code that adds two columns starting from d5 and e5 for several worksheets called multi1,multi2,multi3,........The d column contains only numbers, the e column contains number and letters (product descriptions)
View 9 Replies View Related