AutoFill Formula Based On Data In Another Sheet

Jan 20, 2008

Here 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.

View 9 Replies


ADVERTISEMENT

AutoFill Column To New Sheet Based On Value - Autoupdate

Mar 31, 2014

Master 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

View 7 Replies View Related

Autofill Data In A Column From One Sheet To Another

May 29, 2014

I'm trying to autofill data in a column from one sheet to another in the same workbook. My goal is to be able to paste data in one sheet and have it appear in another sheet.

View 2 Replies View Related

AUTOFILL SHEET 2 Update As Data Goes In

Jan 11, 2009

1.
column b should autofill the last number in column i
in this case b12 = £1820.58
and then evertime column i has data in it should go to the next row in column b

2.
i would like column d to be blank until there is a stake in column c

3.
the same for column f, h and i

4.
column l should have the date auto entry after column c has data input

5.
column n should automatically work out how many days this has been running

6.
sheet 2 cell g5 should know how many records are in sheet 1

7.
sheet 2 cell g6 should return all known "y" in sheet 1 column g
and g7 should return all known "n" in sheet 1 column g
column i.........................

View 2 Replies View Related

Horizontal Autofill From Vertical Data Different Sheet

May 21, 2013

Here My first thread autofill horizontally from other sheet vertical data.

Vertical Data
Sheet1
A1
A2
A3
A4
& so on
AUTOFILL HORIZONTAL DATA
Sheet2
D3,D4,D5,D6..... fill by A1 A2 A3 A4.....

View 5 Replies View Related

AutoFill Cell W/ Data From Different Sheet After Action

Jun 22, 2009

I 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

View 3 Replies View Related

AutoFill Formula To Last Row Of Data

Jul 18, 2012

I have the macro to insert a formula and copy it in a range("A2:A14", but I want the end of the range in column A to be the last row with data in column B. How to add that in?

Sub vlookup()
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[1],'[Old.xlsx]Total Student Count Data'!R2C2:R1000C2,1,FALSE)"
Range("A2").Select
Selection.autofill Destination:=Range("A2:A14"), Type:=xlFillDefault
Range("A2:A14").Select
End Sub

View 2 Replies View Related

Autofill Data In Tabs Based On Data From Main Tab

Dec 29, 2013

My main data tab is collecting gallons pumped for a particular piece of equipment. I have a drop down box to populate the piece of equipment and VLookup to identify the unit number associated with the equipment. I would like to create a separate tab for each piece of equipment that will track the number of gallons pumped during the calendar year. Here is the format for the main tab:

DateBeginning Meter ReadEnding Meter Read GallonsEquipment IDEquipment Description

1/2/2014565443565625 18212006 ford f250
1/10/2014565625565675 5022006 Chevy 2500
1/11/2014565675565750 754Ford Taurus
1/12/2014565750565830 8012006 ford f250
1/13/2014565830565900 7012006 ford f250
1/14/2014565900566000 10012006 ford f250
1/15/2014566000566125 12512006 ford f250
1/16/2014566125566215 9012006 ford f250

Here is the format for each piece of equipment:

2006 Ford F250

Date Gallons
1/2/14 182
1/12/14 80
1/12/14 80
1/12/14 80
1/13/14 70
1/14/14 100
1/15/14 125
1/16/14 90

The formula I used in Cell A4 is =INDEX(Gasoline!A4:A23,MATCH(1,Gasoline!F4:F19,FALSE),1)

Cells A4 and B4 have the correct values. My problem comes in A5,A6 and B5,B6. These should not have a result because they are different pieces of equipment. Throughout the year, each piece of equipment is going be used on the main tab. How do I keep the individual tabs from picking up the same entry multiple times.

View 2 Replies View Related

VBA To Autofill Formula Over Column Data Calculate And Replace Original Data

Nov 14, 2008

Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.

I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008

All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'

Sub Create_formula_result()

Dim Limit As Long
Dim r As range
Set r = range("A1")

r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))

End Sub

View 9 Replies View Related

Data From Sheet A To Appear In Sheet B Based On Data Posted In A Column In Sheet A?

Jan 1, 2014

I have a workbook where Sheet A is a Master Order Form. Employees will input the quantities they need in Column C. The sheet will be otherwise protected. Sheet B is a "printable" order form that only contains the rows from Sheet A where the Quantity on Sheet A is not blank and is >=1. This will allow me to print sheet B with no blank rows for the items I don't need.

I found the following formula which claims to be able to do what I want:

=IF(ROWS($1:1)>MAX(C:C),"",INDEX(A:A,MATCH(ROWS($1:1),C:C,0)))

I can't get it to work correctly across two sheets.

I've attached Sample of spreadsheet for review : HDForm_Test01.xlsx

View 6 Replies View Related

VBA Code To Pull Data From A Sheet Based On Date And Add That Data To Another Sheet?

Aug 8, 2014

I use my workbook to track sales data from one store to the next. I use my workbooks to compare data from year to year. Each year's data is displayed on a separate worksheet. '2013' has 2013's data, '2014' has 2014's. On the 2014 sheet, I have a Prior Year's Sales that pulls data from the previous year's spreadsheet using a formula which I just drag down each day I enter sales. I would like to automate this process and have the VBA code check for today's date and automatically pull the previous year's sales data from the '2013' sheet and put it in the appropriate cell on the '2014' worksheet. I hope I have explained this well enough to understand. I've included a link to my workbook for reference.

I had to use dropbox since I can't post a file over 1 MB. The file size is around 1.25 MB.

View 5 Replies View Related

Autofill Last Sheet Save Date

Oct 29, 2012

Is there a formula I can use to see what the last time the sheet was saved is without using visual basic

View 1 Replies View Related

Autofill For Unknown Range In Different Sheet.

Jun 16, 2009

I need to autofill a range of formulas (A2:O2) but the actual range to fill up depends on the range of data on sheet1. I've managed to use:

View 4 Replies View Related

AutoFill Based On Variable Last Row

Jan 20, 2008

What 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")

View 3 Replies View Related

Import The Individuals Data From The Master Sheet To The Individual Sheet Based On The Employee Name

Feb 6, 2008

I have a master spreadsheet that list several columns about employees(name, date, event name, etc.). Then I have an indivdual sheet for each employee. I am trying to import the column information for each employee onto their individual sheet based on their name. It is possible to import the individuals data from the master sheet to the individual sheet based on the employee name?

View 9 Replies View Related

Pulling Information In Seperate Sheet Based On Data In Current Sheet

Jun 1, 2009

I've got a bit of a quandary here that my novice programming skills can't seem to solve.

Here is the scenario. I work for a photographic library. We have a spreadsheet that contains 5 or so columns, and a couple of thousand rows. Essentially each row contains an imagine code (the number we use to file away the imagine, ie. US_NY_NYC_1 ) in the first cell, and the image information in the following cells (ie metadata, keywords, photographer, date, etc).

so a typical row would look as follows:

Filename | Keywords | Photographer | Metadata | Location |

all the keyword, photographer, metadata, and location information can only be tied to the one image code whose cell begins the row.

Now, here is my dilemma. This sheet (lets call it the master sheet) contains all the images we have in a given batch. However, when a client orders images, we will send them most images in that batch, but not all, and we may send a few additional. So I have a separate sheet that contains only those image codes that the client requested. What I need to do is essentially take a code from the second sheet, find that same code in the master sheet, and copy over all the cells associated with that code's row. If the number does not exist in the master sheet, simply leave that row with only the code in the first cell, and the rest blank, and move on.

so for example, in my second sheet lets say I have the following A_B_C . I would like for excel to find that same code in the master sheet, and then copy over the metadata, photographer, date, etc, associated with that code, and paste it into the second sheet, so that that row in the second sheet now looks identical to the row with the same image code in the master sheet . If A_B_C does not exist in the master spreadsheet, then I would like excel to simply leave that code in its own row, leave the rest of the row blank, and move on to the next code.

View 12 Replies View Related

Macro To Pull Data From Multiple Sheet To Main Sheet Based On Column Headings

Sep 13, 2012

I need a Macro which pulls the data from different sheets of excel (which is not formatted properly) to Main Sheet. Also some of the columns will not have the same names, so macro should handle this exception as well.

View 8 Replies View Related

Autofill Cells With Information Contained In Other Sheet

Feb 9, 2014

I have a workbook with two sheets. The first one, let's call it the Main sheet, will be used to keep track of the touring artists of a record label. The second sheet contains a list of show venues with their respective contact info. Here's what the two sheets look like :

Main sheet
mainsheet.jpg

List of contacts
listofcontacts.jpg

What I would like is row D of the Main sheet to autofill, according to the contact information contained in row B of the List of contacts, when I enter a venue name in row C of the Main Sheet.

View 3 Replies View Related

Autofill Based On Cell Condition

May 20, 2014

I 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

View 4 Replies View Related

Autofill Last Day Of Month Based On Another Cell

Jul 25, 2012

In 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 View Related

Autofill Rows Based On Number

Jul 26, 2012

I 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.

View 3 Replies View Related

Autofill Range Using VBA Based On 2 Variables

Jan 14, 2014

I 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.

View 2 Replies View Related

Autofill A Formula?

May 4, 2009

I have the following formula that I would like to be able to autofill.

=UniqueItem('Divide Outs'!$A$2:$A$999,1)

The 1 after the comma needs to count up...anyway to autofill a formula like this?

This is a function that i use to display unique listings.

View 3 Replies View Related

Autofill Down Formula

Mar 29, 2007

I have data in colA. In colB I want to auto fill a formula that is in cell B1 all the way down and stop when the data in A stops.

Range("A1").AutoFill Destination:=Range("B1:B" & Range("B1").End(xlDown).Row)

It doesnt seem to like this though and I think ive got the cols all wrong in the formula.

View 4 Replies View Related

Data From The Data Sheet Is Copied Over To The Report Sheet With Formula

Apr 11, 2007

The data from the Data sheet is copied over to the Report sheet with formula.

Basically, what I need to do is If statement:
-when there is no any data in csv file (so the Data sheet will be empty);
-the range (A7:N1000) to be hidden (on the Report Sheet)
-the Report sheet to be saved

and also...

I would like to ask, how to select from the Data sheet, the rows with Data only. I copy them over with formula instead of macro- but would like to hide all the empty rows on the report.


Option Explicit

Sub Update_Report()
'
' Macro1

Dim extract1 As String
Dim dReport As String
Dim rSheet As String
Dim dSheet As String

extract1 = "LeadSheetAll_0001.csv"
dReport = "Appointments.xls"
rSheet = "Report"
dSheet = "Data1"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

View 9 Replies View Related

AutoFill Cells Based On Combobox Selection

Jul 14, 2014

What 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.

View 1 Replies View Related

How Do I Autofill Colors, Based On Input Of Text

Jun 16, 2008

i'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 Related

VBA Autofill Based On Specific Cell Values?

Aug 25, 2012

I 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 Related

AutoFill Down Rows Based On Another Columns Last Cell

May 13, 2008

I 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 Related

Autofill Formula To End Column VBA?

Aug 1, 2012

The code below inserts a formula into E2, copies it across to column O and then down to the last row. It works fine but if more columns are added obviously it would need to be changed to go up to column P for example.

How can I change this so that the formula will be copied across to the last column automatically and therefore the code will not need to be changed?

Code:
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("E2").Select

[Code]....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved