Autofill Last Day Of Month Based On Another Cell
Jul 25, 2012In cell A12 the format is mm-###. Based on the mm (2 digit month) in cell A12, I would like cell B12 to auto fill the last date of the month as mm/dd/yyyy.
View 4 Replies
ADVERTISEMENT
Autofill Based On Cell Condition
May 20, 2014I have a workbook sheet change condition that, when triggered runs the following code (amongst others).
[Code] ......
What I would like is to amend the above (possibly in a loop?) so that it skips the rows in which the value "New" features in range F17:F190.
So basically Autofill as per the code but leave the values in the cells of the range in which New features in range F17:F190
VBA Autofill Based On Specific Cell Values?
Aug 25, 2012I want a range to be filled based on 2 cell values in my sheet. The autofill range should be filled based on the values in the input range. For instance in cell A1 I place the value 1 and in cell A2 I place the value 10, then the macro should automatically fill in the numbers from 1 to 10 in another column. If I change for instance the input values to 5 and 15, then the autofill range should fill up the numbers from 5 to 15.
View 4 Replies View RelatedAutoFill Down Rows Based On Another Columns Last Cell
May 13, 2008I have a workbook which has one sheet "Raw Data" where I will cut and past blocks of data of various numbers of rows. Another sheet "level one calcs" conducts various calculations on the raw data. I was wondering if there would be any way to create a macro which would copy down (autofill) the last row of my my calculation page for the exact number of rows I added into the raw data. The goal here would be so that I dont have to highlight and copy down the formulas on the calc sheet each time I add in new data.
View 3 Replies View RelatedAutofill Weekdays/skip Weekends In A Month
Nov 30, 2009I would like to be able to auto fill weekdays (skipping Saturday & Sunday) in a month. For example, my spreadsheet would look like this if cell A1 contains12/01/09:
DATE WORKDAYS TASK
1-Dec 1 AP CLOSE - CAPITAL & POS ITEMS
1-Dec 1 POS MONTH END
1-Dec 1 HEALTH INSURANCE
2-Dec 2 DEPR SYSTEM RUN; INCL-SOFTWARE AMORT
2-Dec 2 SPECIAL EQUIPMENT
4-Dec 4 PRELIMINARY ENERGY REPORT
7-Dec 5 AP CLOSE - ALL OTHER ITEMS
The DATE column needs to return the date for WORKDAYS listed...Monday through Friday only. The first workday in Dec 2009 is 12/1, the fifth workday is 12/7, and so on.
Macro To Autofill With Custom List Based On Value Of Current Cell
May 25, 2012I have an Excel file for work that has the following:
Column 1 is Agent
Column 2 is Interval (in 15 minute intervals from a report that I pulled)
Column 3 is Agent Calls (has a number anywhere from 1 - the highest currently is 19, which tells me how many calls that Agent had on that 15 minute interval.)
What I have currently is that if it says:
John Doe 15:00 5
It will automatically insert rows after the number based on a # -1, so it will add 4 empty rows after this row.
I have another step that will automatically copy Column 1 and Column 2 to the blank rows directly beneath them until it reaches a cell with data.
Now, what I need is a macro that will take Column C and where it says 5 automatically know to change that to '5a' then proceed to go down the list with 5b, 5c, 5d, & 5e. If it sees 4 it will know to change that to '4a' then proceed to go down the list with 4b, 4c, & 4d. I currently have numbers ranging from 2 - 19.
I have built custom list with this information so if I change all the numbers from 4 to 4a or 19 to 19a and double click on the black box it will autofill exactly like I want but it only goes down to the next cell with data, and I have 100+ lines per agent, and up to 21 agents a day, so this gets time consuming.
Here's how it looks when I'm ready to start the macro:
JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:45 4a
[Code] ......
And I need it to look like this:
JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15 4b
JohnDoe - Doe, John15:15 4c
JohnDoe - Doe, John15:15 4d
[Code] ........
Autofill Cell Based On Results Of Multiple Dynamic Dropdown Lists?
Aug 25, 2014how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.
What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On
Is there a way to do this with the set up I have?
I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory
=IF(ISERROR(VLOOKUP(D7,IF('Main UI'!C7="Heating_Cooling",Appliances!$A$7:$B$14, IF('Main UI'!C7="Kitchen_Bathroom",Appliances!$A$16:$B$27,IF('Main UI'!C7="Monitors",Appliances!$A$52:$B$62,IF(C7="Computers",Appliances!$A$64:$B$68,........
And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm
I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list
Populating A Cell With A Calendar Month Based On The Previous Date In Another Cell.
Jan 9, 2010Trying to word this right. I have one cell with a date of 01/01/2010. I have other cells that I want to be equal to this cell plus 1 or more months.
For example A1=01/01/2010
I want A2 to = 02/01/2010 based on one calendar month entered into A1. So if A1 changes 03/01/2010, A2 will = 04/01/2010.
Function To Fill All Days Of Month To End Of Month Based On Workdays
May 1, 2006I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday
to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
Calculation Value Based On Month Name In Adjacent Cell
Feb 28, 2008I have a spreadsheet spread over with dates in column A from September to December and I have a fuel rebate column as well. The fuel rebate is different for each month and is calculated as a certain amount of cents per dollar added to the total amount in Column B
September: 26c
October: 13.5c
November: 43c
December: 66c
e.g. If there is an invoice for 10/9/07 (september) for $100, then the fuel rebate would be $100*.26 which equals $26 fuel rebate. I want to calculate column B (this is the column with the dollar amount in it), but I want the calculation to be different depending on what date is in column A, so if in column A the dated month is October, then the calculation would be something like B2*0.135. give me the calculation I am certain you don't need VBA for this right (just nested IF statements?)
Change The Cell Focus Based On Month And Year
Jan 22, 2010I have a worksheet called "zz_Exchange" that contains the exchange rate for my company for each month of the financial year. I am using this info in a summary page that uses the data for multiple calculations in the workbook.As we pass each month I would like to move the focus of a cell in the summary sheet to the relevant month in "zz_Exchange"
The column names are "currency_code" (USA Euro etc),
"currency_year" (09, 10)
"period" (1-12)
"exchange_rate" (this is where the data is held)
Display Dates In Particular Month Based On Selection In Cell
Feb 27, 2012I have the months in a drop down menu January to December in cell B1.
Is there a way for excel to display the dates in that particular month based on the selection in cell B1?
For example, if I were to select "February", I'd like Excel to display:
02/01/2012
02/02/2012
...
02/29/2012
in cells A5 through last day of the month?
Copy/Paste Cell Value To Correponding Dates Based On Month
Sep 5, 2007I want to paste a cell value into a column that is a certain month based on a date inserted by the user. For example, in cell a1 is £1000 in cell a2 is 01/01/2008 so i would then want this to be pasted in the same row and then column called january 2008.
View 3 Replies View RelatedAutopopulate Saturdays In Multiple Cells Based On Month Date In Another Cell
Aug 19, 2014get other cells in a spreadsheet to populate with the Saturdays of a month based on the date input in another cell.
Example
A1 = October 2014
The following cells would automatically display:
A2 10/04/14
A3 10/11/14
A4 10/18/14
A5 10/25/14
A6 11/01/14
Then if cell A1 is changed to March 2015 the following cells would automatically display:
A2 03/07/15
A3 03/14/15
A4 03/21/15
A5 03/28/15
A6 04/04/15
I can't seem to find a way to get just the Saturdays. +7 doesn't work because Saturday is not always the 7th day of the month.
AutoFill Based On Variable Last Row
Jan 20, 2008What I need to be able to achieve is a function in my macro that will
a: Count the number of rows in the active sheet
b: Allow me to use this info in other functions, such as subtotals, autofills
After searching many sites I'm pretty certain the function I need to use is "rowcount".
I've managed to incorporate a loop using this function, which is fine when running the macro but not so good when you are stepping through 5000+ rows
RowCount = ActiveSheet.UsedRange.Rows.Count
For x = 2 To RowCount
Cells(x, 42).Select
ActiveSheet.Paste
Next x
So for me now it seems as though "x" should represent the number of row that has been counted in the above code. But when I try to use "x" in functions the macro falls over
Range("AQ2:AX2").Select
Selection.AutoFill Destination:=Range("R2C43:RxC50")
Input Date In A Cell Will Show Month And Year Based In Database From MS Access
Jun 1, 2014I have my ms access and ms excel which is connected each other .
=> now in my ms access have a table name (tblMonth) has columns (year,MonthNum,StartDate,EndDate)
HERE: sample in january(1) and feb(2)
YearMonthNum StartDate EndDate
2014 130/12/201305/01/2014
2014 106/01/201412/01/2014
2014 1 13/01/201419/01/2014
2014 120/01/201426/01/2014
2014 127/01/201402/02/2014
2014 203/02/201409/02/2014
2014 2 10/02/2014 16/02/2014
2014 217/02/201423/02/2014
2014 224/02/201402/03/2014
=> and for my ms excel has its columns (year,month,date) in this if i input the date that is base in the database range from StartDate TO its EndDate will automatically put the month and year which base also in my database (year,MonthNum)
HERE:
A1 B1 C1
Year Month Date
A2=2014 B2=January C2=01/01/2014
Autofill Rows Based On Number
Jul 26, 2012I have wookbook with a lot of sheets on it
The first sheet to be filled is called "Local Info"
In this sheet in the Active Cell "G7" the user must register a number between 0-500
This number must affect my next sheet called Competence Map Autofilling rows 7+8 from Columns A to AM
So If for example I put 83 in "local INFO" G7.
I will get 172 rows with text (the first 6 Headers) the 7-8 original and from 9-172 filled with the information given.
Autofill Range Using VBA Based On 2 Variables
Jan 14, 2014I am trying to write some code that keeps throwing me the error message "Autofill method of range class failed". But I cant understand why, I think it may have something to with my range notation:
Code:
Sub SummariseSheets()
'collates individual client development data from consultant
'worksheets and compiles in one sheet on summary page
[Code]....
The error is in the "Selection.AutoFill Destination:=("C" & LR & ":C" & LR2), Type:=xlFillDefault" line, however I cant figure out why? The code essentially takes a section of data from multiple worksheets, then pastes it onto the summary page. It then adds the name of the sheet the data has come from into column C next to the relevant rows.
AutoFill Cells Based On Combobox Selection
Jul 14, 2014What I'd like to do is fill specific cells with specific information drawn from other pages based on a combo box selection.
For example, if I were to pick 10131 in the combo box, I'd like it to fill out cells X, Y and Z with information from the three cells to the right of 10131. Assume X, Y and Z are not touching.
How Do I Autofill Colors, Based On Input Of Text
Jun 16, 2008i'm trying to do is have a formula that when certain text is typed into column 'b' (fmc, nmcmc, etc... anyways..) when an airplane is 'fmc' its 'green' and i want the aircrafts tail number block in column 'a' to autofill in with a green color, and when its 'nmcmc, i want it to be red. when its 'bq' i want it to autofill in as yellow.
View 10 Replies View RelatedAutoFill Column To New Sheet Based On Value - Autoupdate
Mar 31, 2014Master Template
Project 1
Project 2
Task 1
1 - Mar
28 - Dec
Task 2
2 - Mar
2 - Jan
Project Due
5 - Mar
4 - Jan
Above is a table we have for high level tracking on specific tasks due for projects.
What I want to do is: If the last cell in a column = Jan then it will automatically copy the whole column and insert it into a Sheet named January and have it automatically update. Below is the end result.
January Template
Project 2
Task 1
28 - Dec
Task 2
2 - Jan
Project Due
4 - Jan
AutoFill Formula Based On Data In Another Sheet
Jan 20, 2008Here is my test example I am trying to figure this out with.
Sub a()
Dim rg As Range
'set formulas to be filled
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF('sheet2'!RC="""","""",'sheet2'!RC)"
Selection.AutoFill Destination:=Range("A1:L1"), Type:=xlFillDefault
'now get autofill to populate until no more data on sheet 2
Set rg = [a2]
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
rg.Cells(2, 2).AutoFill Destination:=rg, Type:=xlFillDefault
End Sub
Without VBA, once I set the formulas for A1:L1, I can use the mouse to autofill all columns until Im around row 27,000.
Id like to be able to run a check for the end of data in sheet 2 so that if the sheet2 changes, it will all be included.
The resulting sheet1 cannot have any null values, so a perfect end of data check is necessary.
Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month
Jan 28, 2010I 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.
Attached Worksheet Automatically Shade Out All The Saturdays & Sundays In Any Given Month Everytime You Change The Month/Year Cell
Jul 19, 2006Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.
View 2 Replies View RelatedFinancial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)
Dec 23, 2008I 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 Autofill Information From The Next Cell
Dec 20, 2013I have this information available.
test.png
And what I want to do is use this information further down in my excel-sheet. So whenever I plot in "Location 1" I want "Security" and "John Johnson" to magically come automatically.
Like this : test.png
AutoFill Cell With Designated Label
Aug 1, 2012I am trying to do is have a VBA automatically fill a blank cell with text of a different color.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B8") = """" Then
Range("B8") = "Name"
End If
End Sub
Is what I've been trying to get to work. If the value in B8 is suddenly deleted, I want that cell to show "First Name", in a lighter grey. I know I can get the color of the text by using the conditional formatting, but how do I get that cell to auto-fill when deleted?
Autofill Until Non-blank Cell Macro
Jan 10, 2009I have searched the forum a few times and haven't seen an exact example of what I am trying to do, so I hope this isn't a duplicate.
I am looking for a macro to copy a cell and autofill it down the column until it gets to a non-blank cell. When it reaches that cell it needs to then copy THAT cell down until it reaches another non-blank cell... this could happen just a couple times, a couple dozen times, or only the very first time.
So for example:
In cell B2 there is text "Data1"
In cell B13 there is text "Data2"
In cell B25 there is text "Data3"
I would like the macro to search column B to find "Data1", and then copy it down the column until it reaches B13, then copy "Data2" down until B25, where it would then copy "Data3" down until the end of the document. The location of the data in column B varies, and the number of cells with data in column B will vary as well.
AutoFill Cell W/ Data From Different Sheet After Action
Jun 22, 2009I have an excel file that contains two sheets. Sheet2 contains rows of different types of products with each of the columns containing details about the product.
On Sheet1, I have setup a data validation list in which a certain cell contains a drop down box that selects a product from the first column of all the rows on t from Sheet2. I used
Select A Cell To Autofill Userform Textbox
Jul 2, 2012I current have code that fills in textboxes in a userform with values in cells:
Code:
Private Sub CODE_Change()
Dim ws As Worksheet
Dim fm As Worksheet
Set ws = Worksheets(ActiveSheet.Name)
Set fm = Worksheets("Form")
[Code]...
How to I modify the lines under Checkbox1.Enabled = False so that they only take a section (of varying size) of the text in a cell and put it in textboxes in a userform?
ie. The text in a cell is "Outer: A123 ; Inner: B456." I would like A123 to appear in a textbox called OuterColor and B456 to appear in textbox called InnerColor