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 RepliesUsing 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.
i 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
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 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 Replies View RelatedI 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 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 RelatedI have a data worksheet, which contains about 20,000 lines of journal entry info. There is a separate column for debit and credit, not one "amount" column. On another worksheet, I am aggregating the data to review the impact for this series of journal entries over a time period (12 months). Thus, my pivottable contains "Accounts" in the "ROW" and "Period" in the "COLUMN" sections of the PivotTable Layout. The "Debit" and "Credit" fields are then summed in the "DATA" portion of the PivotTable.
My issue revolves around getting the actual impact for each account for each time period. Essentially, I need an extra column inserted between each time period that sums the debits and credits for that period. Do I need a macro for this (not any experience with macros)? My file is way to big to share, so let me know if a pared-down sample would work.
I am trying to write the code for adding double speechmarks for the number of columns on my spreadsheet with a column heading Notes. There are about 10 columns out of 30, and all of them need double speachmarks.
My manual process was to insert a new column in from of the 'Notes' column, use the concatenate function for example - =concatenate("""",A2,"""") and copy the function down the column. The I was geting rid of the formula by copypaste special values, transfering to my original 'Notes' column and at the end deleting the new column that I added at the beginning.
Is there a quick code to populate a concatenate formula via VB? If not, what code can I use to replicate my manual process via VB. On the quick note, the spreadshee has always the same number and order of columns, but number of rown is defferent each time.
I have 5 coloums in which the last three have numeric values. The first two coloums have name and country code. Now, The first two coloums have duplicate values but the rest of the corresponding columns have different numeriv values. What I want now is to add the numeric values of the coloumns of the duplicate data and want to display in the first row. For ex there are A = name , B= Country , C D and E contains different numeric values. A and B have dupilcate data but different C, D and E values. Now I want to add up the numeric values of the dupilcate data and get it displayed in a new coloum in the first row of the duplicate data set.
View 1 Replies View RelatedI just downloaded an income/expense template from Zillow to manage my rental properties. The template works great but it's only set up for 5 houses and I need more columns as I own more than 5. In the attached template the houses are listed by property codes and that is the column that I need to add to.
View 8 Replies View RelatedEach week I get new weekly data-metrics. I have a series of formulas that figure LAST 4 weeks average, LAST 8 weeks, etc etc. How do I drop in a new week of data and have my formulas adjust? For instance, my formula figures last 4 weeks average of May Wk 1, May Wk 2, May Wk3, May Wk4. Now it is June Wk1, so I want to figure (without changing any formula) May Wk 2, May Wk3, May Wk 4, June Wk 1.
View 1 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.
I want to total cells that have data in them across a row. I have a name in Column A and then in Columns C-Z I have data. In Column B I want Excel to scan across cells C-Z and if a cell has data in it (it will be text) I want it to add one to the total in cell B. Would CountIF do this with text involved?
View 7 Replies View RelatedThis macro has a problem in that it totals up the colunmns in two places by error
The total should be at the end of the coumns only.
It gives me a total part way through the list which muddles things up a bit.
Sub prepareSAPexport()
'
' prepareSAPexport Macro
' prepare the sap export ready to be compared to CDS
'
' Keyboard Shortcut: Ctrl+s
'
Range("A1").Select
[code]....
OK, I have a spreadsheet that has several pages for tracking when items are added and removed. On the main sheet I need to have the total items added, the total items subtracted, and the total avaialble. This is also based off of month.
For example, on sheet 2 it would show 5 of an item added, 2 of an item subtracted, all for March. It would also show 3 items added, 2 subtracted for April. On Sheet one i need to report in a row any items added, any subtracted, and the current total. I am pretty sure that i have the formula for added items and subtracted items for the given month, but i do not know how to get the current total.
How would I go about finding the "Number of Shirts Ordered" values in the top right?
View 1 Replies View RelatedI want to restrict the adding the rows and columns and also deletion of rows and columns in excel template, the same template is circulated to all the program owners to capture there project metrics. With that template through macro values are extracting to consolidated report. Some times program owners are adding extra columns/rows with that we are getting errors/incorrect reports.
View 14 Replies View RelatedI have a External Data Table, but I wanted to add a extra column to it for comments. I just need to be able to write down what I did with that particular order or things like that. I did make a column, but the problem is whenever I update the table the comments column fields stay static I mean they don't move with their row. So it ends up the comment belonging to a row is now on another row.
is there a way I can tell excel to move the cell with the record or something like that.
I'm trying to set up a billing form for my work. I'm trying to include drop down list for each sections (personnel, equipment, and supplies), then all sum together as a bill. I'm not sure if this is doable in excel.
View 9 Replies View RelatedI have a spreadsheet with 20 or so columns but that number can change at any time. I need my code to by dynamic so if a column is deleted (or added) my code will still work.
Each column has a name in row 2 and I'm trying to use that in order to make everything dynamic. For example, my code counts the number of blue cells in colums D4:G68, but now if someone adds a column before column D, everything gets messed up and it will still count the cells in D4:G68 but I want to use E4:H64 now. Does that make sense? I have made code that searches the column names and returns the column number to correspond to the name. How would I change this?
Range ("C8") = "= CountBlue(E4:G68)"
the code doesn't look exactly like this, I believe it is actually in R1C1 format
I have two columns D and E that contain dates and values. Column D has the dates and Column E contains the corresponding values.
I need a macro to take all of the dates in column D (it is pulled from a different macro so the length of the two columns varies every time but starts at D2) and paste the first value in W1, the second value in AE1 , and so on (every eight cells).
I need to do the exact same thing with Column E except start at Y1, then AG1, etc (still every eight cells).
I will try to post an example later.
I have a row of sub-headings at row 12 that require Sort Filters. My problem is that I need the filters on selected columns only. It seems that with the standard filter button I get all or nothing.
View 8 Replies View RelatedI am currently tracking online PPC keyword reports with Excel and need to know if there is a function that will find and match words and phrases and then add the columns that are queried for the matching words/phrases. I think an example is definately in order.
December PPC
Keyword Clicks Cost Revenue
large dogs 45 .18 $12.00
small dogs 35 .25 $15.00
January PPC
Keyword Clicks Cost Revenue
large dogs 12 .14 $8.25
small dogs 18 .18 $5.35
Now using the example above I need a function or maybe even a macro that will scan all "keywords" and find a match for each keyword each month, say large dogs, and then add the clicks, costs, and revenue columns and post them on a particular row or rows.
So when running the function it will find and match "large dogs" for each month, it will then add the clicks for all months with "large dogs" in it and then populate a column or row with the total along with the keyword "large dogs" next to it. Is there a function that will do this or maybe even a macro?
A
B
C
D
1
$10
$2.95 to $11.00
$4.50 to $19.00
$17.45 to $40.00
Is there a formula that will add column A to the left and right sides of columns B and C to give me an overall answer range in column D?
How do I go about using adding an auto filter on specific columns of a worksheet..?
I.e. I want to auto filter column "D", "G" and "I" but none of the columns in-between ("E", "F" and "H")
Currently I can only create the filter for one column or a group of columns that are next to each other)
How do I define a " Range Name" that is fixed? So if I insert a row above the range it has no effect on the orginal range.
IE: Range Name "MyRange" = A10:A100
I insert a new row at A4 then "MyRange" = A11:A101
I want it to stay at A10:A100.
I have attached a copy spread sheet. This has been working great but i have been asked to add some items and i dont want to screw up the working functions.
I now want to add incert two columns so the actual costs of a first and second service can be added to the contracts and used contracts sheets, this information allong with data from a,b,e,g h needs to be copied over into a new sheet (report sheet) which will have the budgeeds costs in column i,j starting from row 3 and finding the last row so as not to overtype so that a report can be sent showing profit/loss.
The costs will be put in at diferent times so it only need to up date a changed cell
If i just add columns will this effect the auto archive coding? Could the data be copied over to the new sheet using the original code on start up? (so customer etc copied then as cost are put in these would be added to the respective rows on each start up.
I have had to remove some of the sheets to up load this so my not work correctly, but you can see the funtion in the code
Essentially what I want to automate is a check through one list on the Sheets("Notes") in column A, with another column B on Sheets(template). If there is a match I want to insert a part of the row that the match occurred on Sheet("Notes"), and insert it above the row where the match occurred on Sheets(template).
Here is my code so far, currently I keep getting a "Application-defined or object defined error" on the line
VB:
Sheets("Notes").Range(Cells(i, 2), Cells(i, 11)).Copy
VB:
Sub add_notes(template As String)
Dim Rng As Range
Dim i As Integer
[Code].....