Skip X Columns In Formula Reference When Dragging Across Columns

Nov 15, 2009

How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example: I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2. Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.

View 3 Replies


ADVERTISEMENT

How To Skip Columns When Dragging Formulas

Dec 3, 2013

I have 2 sheets in my excel document. The first sheet (sheet1) has student data (grade they got on a test) from C5:C35 all in 1 column. On the 'second' sheet (sheet3), I have made a profile for each student (there are 30 of them, spread out 1 per printable page). I want to take Sheet1!C5 result and put it in sheet3!D7. The formula in sheet3!D7 would just be =sheet1!C5. I want to 'drag' that formula to L7, then to T7 then to AB7 etc etc (they are all 8 columns apart), obviously not affecting any of the cells between them.

There are 12 pieces of data in sheet1 that I want to auto update in sheet3, for 30 students, so copy pasting, and adjusting the formula would take a long time.

View 7 Replies View Related

Changing Indirect Reference When Dragging Formula

Dec 9, 2012

I have been using the following formula to do conditional summing over multiple worksheets:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$AB$2:$AB$13&"'!b3:b39"),$B16,INDIRECT("'"&$AB$2:$AB$13&"'!c3:c39")))

The formula works fine, but when I drag it to the next column c3:c39 stays the same. How do I get that reference to change when I drag the formula over multiple columns.

View 1 Replies View Related

How To Update Cell Reference When Dragging - INDIRECT Formula

Jul 10, 2014

I am using the formula below to sum across several worksheets, and the formula I am using works fine, but when I copy the formula across to other cells, I can't figure out how to have it update my cell range:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!G:G")))

The range A:A is ok to be static, but I need the G:G to be dynamic so when I drag it to the right it will change from G:G to H:H. I:I, etc.

View 6 Replies View Related

Use VLookup And Skip 10 Rows When Dragging Down?

Sep 13, 2013

I have used VLookup function to reference some information between books. The formula used is:

=VLOOKUP('[Book1.xls]Sheet1'!$A15,'[Book1.xls]Sheet1'!$A$7:$BA$67,12,0)

When I drag down I want the formula to skip from $A15 to $A25 and so on. Is there a way to do this? Or am I better off using another formula?

What I am trying to achieve is:

Book2 Cell D54 = Book1 Cell L15
Book2 Cell D55 = Book1 Cell L25
Book2 Cell D56 = Book1 Cell L35

View 3 Replies View Related

Intercept Formula - Reference In 1 Row Not 2 Columns

Dec 2, 2011

When you use a formula like intercept. It wants its ranges in 2 columns. for example, intercept(A1:A5,B1:B5).

How can I get intercept to work using the ranges in 1 row. for example, intercept(A1:D1,E1:H1).

I want to do many intercepts in a large data range and still filter by some other variables that go along with these intercepts.

View 2 Replies View Related

Formula To Reference Rows Across Columns

Aug 6, 2007

I have two workbooks open, Workbook 1 (W1) & Workbook 2 (W2)

W1 has the following data entered in about one of our clients:

A1= Client Company Name
A2= Client Name
A3= Client Address
A4= Client Phone Number

What I would like to do is the following:

In W2, when I enter (in any cell, but let's say C3 for example) that C3 = A1 (from W1), that I can automatically have the following cells D3, E3, & F3 filled in with the same information appearing in cells A2, A3, & A4 from W1 as well.

I believe this must be possible-- I just don't know the right formula & steps to take, to make this happen.

View 6 Replies View Related

Index Row Reference When Filling Formula Across Columns

Oct 16, 2013

In Column A, starting in row 8, I have a list of part nos. This list can be any number of rows long.

Further down the sheet, I want to put that same list into a horizontal array. I currently have this formula in the first column. It will, of course, pull the value from the 1st row.

=IF(ISBLANK(A8),"",A8)

However, when I fill across all of my columns, it changes the Cell reference to B8, C8, etc. whereas I need it to change to A9, A10, etc.

Writing a formula that will index the ROW reference as I fill across?

View 3 Replies View Related

Fill Formula Reference By Rows When Copying Across Columns

Dec 20, 2007

I am trying to fill in reference cells horizontally but fix the column and increasing the row number as it fills. For examples I want to start my refence as "=A1" in cell B1. I want to copy this reference horizontally to column Z1 but want the refences to be A1, A2,...,A26. Excel only seems to increase columns when filling horizontally and rows when filling vertically. Transpose works but it's an array which would slow the spreadsheet down.

View 4 Replies View Related

Using Count If But Skip Columns

Jan 20, 2010

I use the formula below to sum hours in a spreadsheet:

=((((SUM(B6:AP6)+(COUNTIF(B6:AP6,"x")*12.5))+(COUNTIF(B6:AP6,"d")*12.5))+(COUNTIF(B6:AP6,"m")*12.5)) +(COUNTIF(B6:AP6,"e")*6))

I would like to be able to change the range from "B6:AP6" to something which will start with B6 but only count every 6th cell up to AP 6. I could probably place a symbol at the top of each column I wish to count if needed. If I try to simply choose every sixth cell I get an error about too many arguments for the countif function The above formula is used to add hours for various shifts (symbolized by letters). For my current need I do not need the multiplier i just need to count how many of each letter appear in every sixth cell of a row.

This may be too fancy but if one formula would work to count every 6th cell starting with B6 and give me a sum , then automatically do the same starting with B7 and count every 6th cell etc...that would be great. I am trying to sum the number of each occurrences of each shift at each of 6 locations.

View 4 Replies View Related

Adjust Formula Range Reference As Columns/Rows Added

Apr 4, 2008

I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:

=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998

becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998

View 3 Replies View Related

A Formula To Reference Another Page And Skip Cells To Get To Desired Results?

May 14, 2014

I am trying to find a formula that will allow me to reference another page in my spread sheet. In my scenario the row I am referencing has 5 consectutive numbers after the reference point I would use for a vlookup. These numbers are all zeros except for one number in each row which will be a positive number greater than zero. I want my formula to check the first cell in the row, if it is not a zero it will show that number, but if this cell contains 0, then it moves over to the next cell. If all the cells in the row have a zero, then i just want zero to appear on my summary sheet. I tried to do a vlookup with an IF function, but I couldn't get it to work. I have attached a sample of what the spreadsheet looks like.Example doc.xlsx

View 2 Replies View Related

Transpose Function - How To SKIP / SELECT Certain Columns?

Feb 3, 2014

I am working on a =transpose function. Pretty simple right? Well now I am trying to have it SKIP/SELECT certain columns.

I have =transpose("date!B1:T1"), but now I want to "Select A,B,D,E,F"

=TRANSPOSE(INDIRECT("date!B1:T1"),"select A,B,D,E,F,G,H,P,Q,R,S,T")

View 1 Replies View Related

How To Select Range And Skip Over Hidden Columns

May 1, 2014

I have below a code that is selecting from A11 down as far as there is data and across a certain number of columns then sets a print area.

Currently the columns go A, B, JW, JX, JY, JZ, KA, KB as C through to JV are hidden. I have tried to offset the columns by 7 to see if it will select A-KB but it is selecting cells that are hidden and only selecting A-G. I need it to go past the hidden columns. Here is the code:

[Code] ......

Could I also get a code to go from A10 and move over to KB and create a range from there e.g. KB-KJ and last row from there?

View 10 Replies View Related

Using Indirect Function To Skip Columns From Different Worksheet

Feb 9, 2012

A post about skipping rows is very similar to my situation, but I'm stuck on how to translate that for columns. (Filling formulas but need to skip cells)

I'd like to transfer information from a worksheet called "Raw Data" to another sheet, skipping every 5th column. I'd like to go from "Raw Data" B12 to a new sheet C6, then from "Raw Data" G12 to D6 in the new sheet, and so forth.

I figured out that I'll need to use COLUMN()*5-13 as a component of the function, but I can't seem to figure out how to format the name of the worksheet correctly, with the ampersand and quotation marks.

This doesn't work at all, but I think it's in the ballpark...

=INDIRECT("Raw Data!"(COLUMN()*5)-13)&12)

View 3 Replies View Related

Copy Pairs From 2 Columns To Formula Reference Cells & Copy Updated Formula Results

Jun 24, 2008

I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.

View 3 Replies View Related

Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)

Oct 13, 2008

I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

View 4 Replies View Related

Average Formula In Excel With Even Columns Only And Columns With No Value?

Apr 26, 2014

i am trying to complete a spreadsheet which most of it is done but one column i have i have to average everyother square in each column which i did but when i total those columns at the end i get the dreaded #div/01 which means any value that i dont have in a particular column it will not add is there anyway to fix this. i am attaching a sample of the spreadsheet so you can see exactly what i am trying to do .

View 6 Replies View Related

Dragging Down Reference To Another Workbook Cell

Jan 23, 2014

I have a master workbook that I reference data from hundreds of excels on.

I am trying to reference the cell "BU7" that has the workbook name (NT-13-001) in it, to reference sheet name "Purchase Order" at cell "H13".

Next line I would like to reference the cell "BU8" that has the workbook name (NT-13-002) in it, to reference sheet name "Purchase Order" at cell "H13".

The computer path is K:FRG TOOL REQ2013NT FormsNT-13-001

This is the formula I have tried... I have also attempted the INDIRECT function & Hyperlink GO TO.

=IFERROR('K:FRG TOOL REQ2013NT Forms["&DU7&".xlsx]Purchase Order'!$H$13,"")

View 1 Replies View Related

Dragging Down Formulas To Last Row In Reference To Next Column

Apr 10, 2014

I would like to drag down the formula from the last cell in Column A in reference to the last row in column B.

E.g My last data in Column A104 (code to select the last cell and this can vary from time to time the report is updated)
Column B has data till B142. I want to drag A104(contains formulas) down to A142.

Here is what I have but Selection Autofill doesn't work.

Range("A2").End(xlToRight).Select
Range("A2").End(xlDown).Select
Selection.AutoFill Destination:=Range("A3:A" & Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillDefault

View 1 Replies View Related

How To Change Reference Cells Specific Amount When Dragging

Aug 14, 2014

So I'm dragging my formula down one cell and as expected the reference cell changes the same amount when I really want the reference to shift Down that column 11 cell so when I take my formula

=IF(ISERROR(LOOKUP(21474836472147483647,'Revenue Book'!L5:L11)),"",(LOOKUP(21474836472147483647,'Revenue Book'!L5:L11)))

when I drag it down one cell the reference should look like this

=IF(ISERROR(LOOKUP(21474836472147483647,'Revenue Book'!L16:L22)),"",(LOOKUP(21474836472147483647,'Revenue Book'!L16:L22)))

View 5 Replies View Related

Adding New Columns By Reference Of Another Tab?

Jun 2, 2012

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

Cross Reference Values Amongst Columns

May 22, 2009

I have these data arranged in three columns as follows:

ABC
91183
112198
***3181
54201
***5***
***6***
***7***
***8***

(where *** stay for blank cells)

I need to "report" somewhere and somehow within the spreasheet hopefully by means of some formulas the following data:

1. for column "C" the maximum value within first group of consecutive numbers that are exceeding 180: in this example equal to 201

2. for column "C" the total number of values within first group of consecutive numbers that are exceeding 180: in this example equal to 4

3. for column "B" the first number corresponding to the first group of consecutive numbers that are exceeding 180: in this example equal to 1

4. for column "A" the corresponding number to the maximum value within first group of consecutive numbers in column "C" that are exceeding 180: in this example equal to 5

and most of all I would like to generalise the problem so that to "report" the same data for all cases that are appearing in the above example;

View 9 Replies View Related

INDEX MATCH With Rows And Columns As Reference?

Jun 11, 2014

I am trying to work on a index match but can't seem to make it work.

My look up value are on column while the data I want to show and look up array are on rows and still getting 0 results.

Is there any solution ofr this to make it work without altering my look up value & arrays to columns as well?

View 4 Replies View Related

Cross Reference Multiple Columns And Copy

Apr 12, 2009

I am working with two sheets, Sheet one has 1 column of information with 12 columns to the right as categories. I want to be able to put an 'X' in any one of the 12 column rows, (category) and have a function that will copy/pastes the information from the main column (column #1, row=any) to the second sheet. I have tried True-False function, argument function, Vlook etc., I still cannot get it to work.

View 10 Replies View Related

Hiding Columns With A Macro By Date Reference

Jan 28, 2010

I have a spreadsheet set with the days in sach column (eg 29-Jan-09, 30-Jan-09, 31 Jan-09, 1 Feb-09 etc). What I would like to do is hide all columns prior to a date which is entered into a cell/macro text box.

View 9 Replies View Related

Hiding Of Columns Based On Reference Cells

Apr 25, 2006

I'm trying to determine a range of columns I would like to hide by applying HLOOKUP on certain reference cells. The following was what I did:

Sub HideColumns()
' HideColumns Macro
colx = Chr([A25].Value + 64)
coly = Chr([A26].Value + 64)
Sheets("Sheet 2").Select
Columns("" & colx & ":" & coly & "").Hidden = True
'Selection.EntireColumn.Hidden = True
Sheets("Sheet 1").Select
Range("B1").Select
End Sub

I'm referencing cells A25 and A26 as the range of columns I would like to hide in Sheet 2. I equated the cells in Sheet 1 to columns in Sheet 2. Unfortunately, the result was columns being hidden from column 1 to column x instead. I would greatly appreciate if anyone can kindly correct my macro. Also, may I ask how I can distribute the columns evenly after hiding/unhiding?

View 4 Replies View Related

Reference That Increments Columns When Dragged Down Rows

Sep 6, 2007

I am looking for a formula that I can drag down a column that is pulling data from left to right at the top of my spreadsheet. See the attachment.

View 5 Replies View Related

Reference A Value In Table Using Dropdown Lists For Both Columns And Rows?

Oct 8, 2013

I want to be able to reference cells in a table by using two drop down lists. One drop down for the columns in the table and another drop down for the rows in the table. The values in the columns will be different metal materials (aluminum, copper, stainless steel, etc.). The values in the rows will be thicknesses of those materials (1/16", 1/8", 1/4",3/16",3/8",1/2",3/4",1"). I already created a drop down list for the materials and another for the thicknesses. But now I want to be able to select a particular material and then a particular thickness and receive back in another cell a value that's cross referenced in a table. Specifically, the values in the table will be the weight of the particular material and thickness per square foot. I'll populate all of the cells in the reference table manually. I'll hide the table on a separate worksheet. i haven't created the table yet.

View 2 Replies View Related

Excel 2010 :: Columns That Are Dependent On Each Other Without Circular Reference?

Aug 7, 2014

I have two columns of data; one is in inches, one is in metres. If someone is inputting data from an original source that has the data in metres I want excel to automatically calculate and input the data into the inches column. Additionally, if they input the data from the source in inches, I want excel to convert it to metres and input into the other column automatically. The conversion factor from inches to metres is * by 0.0254. I am no expert but after looking for this on the internet I am sure this requires some sort of VBA event code (although I don't really know the first thing about VBA). The excel columns will be in the same worksheet. I don't have a circular reference like I would do using normal Excel formula? I am using Excel 2010.

View 2 Replies View Related







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