Hello, below is a sample of my excel sheet. The number of rows keep changing daily and the Month and year column has to be populated automatically as and when there is a new row inserted. I tried the formula =MONTH(A:A) and I paste it on the entire column. it works for rows with data but I also get 104,000 odd blank rows added to my excel because I pasted the formula on the entire column.
View 7 Repliesway to put a simple formula in a colum that will apply to the entire column, no matter how many rows without having to physically copy it to the number of rows required?
View 11 Replies View RelatedI have a column (A) where there's a date in each cell. Currently it is formatted as category "text". All entries where initially entered as 'yymmdd', but using category "number". Beacause of that all zeros in the year disappeared, so now they are shown as "ymmdd".
Can someone show me/tell me how to convert all these dates from category "text", to category "date", using format "yymmdd"? I have tried again and again and again, but can't make it work. I have attached a sample of the sheet.
I am trying to create a graph that is conditional on two different columns. The first column is a date column, the second column has various categories. I want to show how many times each category appears per month. This database is continually added to so I wanted the formula to reflect the entire column range.
For example, let say I have 5 categories (Grapes, Apples, Peach, Pear, Banana). Column A would show a date (in a M/D/Y format) and Column B would list the fruit type. I want to show how many Grapes were input in January, February, March, etc. and then move on to show how many apples in each month, and so on.
My macro's function is to paste the workdays of an entire month in a specific row of a worksheet. Everything seems to work except using a number to reference the column in which to store the values. I've searched the web for over an hour and found two "solutions":
1. write a function to convert column number to a letter
2. utilize Cells object
#2 seemed more efficient but the function is giving me the error: "Runtime Error: '1004': Method of 'Range' of object "_Worksheet' Failed"
see the attached spreadsheet including the data. In Column AA I have created a formula that looks at Column B, and pulls each unique value from that column. add to this formula, or propose a new one, that pulls unique values into column AA if at least one of the rows has an "Actual Finalization Date" in column D that is in 2014?
View 1 Replies View Relatedis it possible to apply a formula to a whole column just one time, so I don't have to copy/paste it every time I insert a new row?
View 14 Replies View RelatedI have amended the code but it's for columns. I want this formatting for entire row 4 only and not for columns. Because it's disturbing my program if there is any data using ':' sign then the same formatting is applied which I don't want.
[Code] .....
The ':' sign is not changing to bold. I want to bold ':' sign too.
How to amend the code for entire row 4?
I currently use the PROPER function for one column at a time. How can I apply it to a whole worksheet, preferably without creating new columns?
View 9 Replies View RelatedI am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.
The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.
Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).
VB:
Sub Macro1()
'
' Macro1 Macro
'
Range("B10").Select
Cells.Find(What:="as % of Revenue").Activate
Range("B20").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/R10C"
Range("B21").Select
End Sub
I need to do something to my workbook, and I need to do this task:
When I insert/delete a column between E & F in sheet 1, the formula (not the value) in the column E was applied too to the new column I've inserted/deleted..
Then, when that happened to the sheet 1, It would happen too to the other sheet automatically..
So I don't need to insert/delete the row and copy the formula manually for each worksheet..
I know that I could simply solve it with grouping the sheet tab..
But I have plenty of data that needed to be inserted and applied with the formula..
I will attach the little example : insert.xlsx
And one more thing, I received this VB code from [URL] ..... for inserting the column:
VB:
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
[Code] .....
And this code for deleting the column:
VB:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
[Code] .....
I'm inserting new information at the top of my worksheet. Unfortunately the formula for what WAS row one: =SUMIF(TotalSales!$B:$B,A1,TotalSales!$H:$H) Will no longer work for row one because as more date is collected row 1 shifts down to row 2. Row two still looks good but there is no longer a formula for the new rows inserted.
To be clearer... I would like to have the following:
=SUMIF(TotalSales!$B:$B,A1,TotalSales!$H:$H) in cell B1
=SUMIF(TotalSales!$B:$B,A2,TotalSales!$H:$H) in cell B2
=SUMIF(TotalSales!$B:$B,A3,TotalSales!$H:$H) in cell B3
How can I keep that formula in row one as I keep inserting new rows?
I want to copy a D1-C1 formula all the way down column E. If I don't have any data in column D1 and C1, however, I don't want anything to appear in Column E.
Is there a way to hide the formula once I have built it for the all of Column E? The reason I need this to happen is because I can't have any zeros popping up in Column E, because I am calculating a running average of Column E and "zero" values would throw off my average.
I have an array formula that I need to copy down the entire column. The formula is:
=SUM(IF(IF(K$2>=V3:V5,V3:V5-V2:V4,$K$2-V2:V4+1)>0,IF(K$2>=V3:V5,V3:V5-V2:V4,$K$2-V2:V4+1),0)*W2:W4)
I need the Row values to change, not the "array formula parts" (because I don't know what they really are called). So, as I copy down I need the new formula to read:
=SUM(IF(IF(K$4>=V3:V5,V3:V5-V2:V4,$K$4-V2:V4+1)>0,IF(K$4>=V3:V5,V3:V5-V2:V4,$K$4-V2:V4+1),0)*W2:W4)
The only way I've been able to do that is to rewrite the whole formula in the new row cell.
I am trying to add a formula to a defined column in a a table. I get a method range of object error. I previously used the .value and a for loop to enter the forumlas into each cell but I would like to use a different approach. It's basically looking at the compelted column to see if the check box is checked. Here's my code.
Set ntidRange = Application.Range(tn & "[NTID]")
Set dtRange = Application.Range(tn & "[Date]")
Range(ntidRange).formula = "=IF([@COMPLETED]=TRUE,Login!K2,"""")"
Range(dtRange).formula = "=IF([@COMPLETED]=TRUE,Now(),"""")"
'For Each c In ntidRange.Cells
'c.formula = "=IF([@COMPLETED]=TRUE,Login!K2,"""")"
'c.Offset(0, 1).formula = "=IF([@COMPLETED]=TRUE,Now(),"""")"
'Next
I'm wondering if it's possible to have a formula applied to an entire column without adding unnecessary length to the spreadsheet until someone is actually scrolling down and using more rows. I want formulas to be constantly present in two columns to automatically calculate values as they are entered into other cells in the same row, but ideally without the otherwise empty spreadsheet being X amount of rows long.
I initially had the formula dragged down 15,000 rows, but it would really be much better if it could start out as a smaller size and then expand as the user requires.
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
How to express what I am trying to do in a sentence but basically I have this formula
[Code] ..........
I need B# to change automatally like it does when you copy the formula but i need the N1 and N2 to stay N1 and N2, how do i do this?
Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name "LARSheet1 to 1" ]
I'm trying to build "Leave, Attendance Management" system using Excel. I am about 40% there so far.
The basic structure of the workbook is as follows:
12x main monthly "Leave Attendance Record" worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets asLARSheet1 to LARSheet12 ].
1 x worksheet which holds "data validation" list andvlook up info. ["FormulaListSheet"]
1 x worksheet acting as a database of staff details andpay numbers. ["StaffdBaseSheet"]
1 x summary page which shows staff reaming leaveallocation. ["SummaryLeaveSheet"]
What I'm having problems with at the moment is as follows:
A formula or a macro which works out how many days are present in a given month [starting date of month located in CELL B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).
I would like it to work like this:
When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.
I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.
Sample of sheet setup : screenshot_LARSheet1.PNG
I need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.
I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.
VB:
Dim Cell As Range
' 1st cell with the posting date
Set Cell = Range("A2")
Do While Not IsEmpty(Cell)
If Cell = "Date" Then
[Code] .....
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
View 4 Replies View RelatedI have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
How to correct this formula to get correct output.
=IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),
"January","February","March","April","May","June","July","August","September","October","November","December"),""))
It's working fine if the year is 2014 but not if the year is different.
Conditions:
If the date is today then only the month should display else blank. The year should be ignore.
For example:
08/01/2000: January
01/01/2000: blank cell
08/01/2014: January
01/01/2014: blank cell
The date is in column 'p' in dd mm yyyy format. In short if the date is current date that is for today '8' then only the month should be display in the output.
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
=IF((C26-B26)/B26
I'd like to apply a curve to a column of student exam grades and have the curved grades displayed in the adjacent column. Here is how I'd like to curve:
I like to make a linear function out of two coordinate pairs.
First Pair (x1,y1) = (Highest Exam Grade, 99) = (H,99)
Second Pair (x2,y2) = (Class Exam Average, Desired Exam Average) =(C,D)
Slope = (99-D)/(H-C) = m
Linear Function = Y-y = m(X-x) = Y-99=m(X-H)
Each student grade would = "Y" i.e. Y=m(X-H)+99
Instead of looping through 700,000 lines of data and applying a formula one by one, isn't there a way to simply apply the lines below to all cells in a range at once?
View 3 Replies View RelatedI'm using this to apply a formula to a worksheet:
Code:
Private Sub Worksheet_Activate()
Range("B2:B7000").Formula = "=IF(COUNTIF($C$2:$C$7000,C2)=0,"""",COUNTIF($C$2:$C$7000,C2))"
End Sub
Is it possible to amend this so that it only applies the formula to the used range? Although the current range goes up to 7000 this increases the file size so to keep it to a minimum I was wondering if there was a way to do it only to the used range.
I am looking to get a formula based on my spreadsheet attached
I want F column to add 5 days onto whatever date you put in there then correspond it to the matched date period in I2-I6 then apply the pay period from H2-H6 and put it in the G Column next to the date that has been input?
Is this possible?
Book1.xlsx