Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Fill Formula Reference By Rows When Copying Across Columns

Hi:

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.
Any suggestions?

Thanks,
Minh


Post your answer or comment

comments powered by Disqus
To increment a formula reference by rows, when copying across columns you need a combination of the INDIRECT and ADDRESS Function. For example, place this formula in any row in Column "D".

	VB:
	
=SUM(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(COLUMN()-2,1))) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now copy it across any amount of Columns. You will note that at each Column the row number increases by 1. In affect it will look like:

=SUM($A$1:$A$2) at Column D
=SUM($A$1:$A$3) at Column E
=SUM($A$1:$A$4) at Column F
=SUM($A$1:$A$5) at Column G
etc

The Syntax for the ADDRESS Function is

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

The last 3 arguments (abs_num,a1,sheet_text) are all optional. If you range resides on another sheet, it is the sheet_text that you need, eg

	VB:
	
=SUM(INDIRECT(ADDRESS(1,1,,,"Sheet 2")&":"&ADDRESS(COLUMN()-2,1))) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Note that the Sheet name is supplied to the first ADDRESS function only

Here is an alternative method for incrementing a row reference when copying across columns. (See this forum's thread: http://www.ozgrid.com/forum/viewthread.php?tid=1530).

Use the OFFSET function to achieve similar results by placing this formula in column D and copying across (as needed):

=SUM(OFFSET($A$1,0,0,COLUMN()-2,1))

The effect is such that at each column to the right of the previous one, the row number increases by 1, and just like the thread mentioned above, will achieve the same results as if you had entered the following:

=SUM($A$1:$A$2) at Column D
=SUM($A$1:$A$3) at Column E
=SUM($A$1:$A$4) at Column F, et cetera.

The values returned are the same as those using the INDIRECT/ADDRESS formula, but this OFFSET function formula is a little shorter (and probably a little faster when dealing with hundreds of formulas, although I haven't tested this).

Regards,

Tom

I want to copy =sum(a2:a3) across 5 columns incrementing the row by 1 per column... then I want to copy the formulas down 10 rows... so changes would only occur when the formula is copied across columns... ie Column A would only sum a2:a3, a3:a4, a3:a5 etc... Column B (incremented by one row) would contain sums a2:a4, a3:a5, a4:a6 etc...I know how to do this by hand but I use a vlookup function that uses these formulas that use 250 columns by 3000 rows so I am interested in learning how to change the copy over to next column without having to Cntrl F all 250 columns... Simple example... formula is copied across columns incrementing row by 1 and then has ability to copy formula down to still have the same functionality as the example.Column A
=SUM(A1:A2)=SUM(A2:A3)=SUM(A3:A4)=SUM(A4:A5)=SUM(A5:A6)

ColumnB
=SUM(A1:A3)=SUM(A2:A4)=SUM(A3:A5)=SUM(A4:A6)=SUM(A5:A7)

ColumnC
=SUM(A1:A4)=SUM(A2:A5)=SUM(A3:A6)=SUM(A4:A7)=SUM(A5:A8)

ColumnD
=SUM(A1:A5)=SUM(A2:A6)=SUM(A3:A7)=SUM(A4:A8)=SUM(A5:A9)

This formula increments the column number when copying the formula down a column.
=OFFSET($E$41,0,ROW(A1)-1
I.e., say 1 is in cell E41 and 2 is in cell F41. Place the above formula in A1 and copy to A2. The result in A2 will be the number two. I now need to do the opposite. How do you increment the row when copying the formula across columns?
Thanks. Rich

I am looking for a formula that reads down when drug to the right. Please see the attachment.

Thanks for your help!

Add Running Formula One-By-One when info entered in adjacent Column in the same Row.

Dear Forum,

Basically, I want to insert formulas in three different columns based on entering information in 2 Different columns.

When I enter information in the column B then I want a formula to be be entered in the same row of the column A..the column A is the column for the Sr,No where the Automatic Serial no will get generated leaving inter-row space between any entries in the Column B.

Column A and Row C2 onwards formula:

Ex: Cell A2 =


Now as seen the formula is a locked type of formula with the cell $B$2 and the End Range as Running...

after the calculation is done I want this to be immediately copy pasted as value in the same cell..

On the same grounds, I also want to have 2 different formulas to be entered in the Columns C and Column D whose values are dependent on the entry of information in the Columns A as well as Column E.

Let me start with the Column C whose value depends on the information entered in the Columns A and Column E i.e. when information is entered in Cell B2 then the cell C2 and Cell C3 should have the formula as ...

Column C =


So, I want the formula to be entered in 2 rows in the column C after checking for the information in the Columns A and Column E.

In the Column D, I want the below formula to be entered which is again based on the Columns And Columns E.

Column D =


Both the values derived in the column C and Column D should be immediately copy-pasted in the same cells...

I suppose this VBA code has to be entered in the WorkSheet_Change Module..

Warm Regards
e4excel.

Hello Everyone

Thanks again for everyone's help!

I'm stuck, I'm starting to wonder if it's because my logical approach is essentially wrong.

This time I'll start from the top.
I have a master sheet with 9000 rows of data approx, this needs to be split into 6 categories, it then needs some columns added, formatting etc. The data within each of the 6 categories then needs to be further broken down and subtotalled, then totalled.

The point I have reached is to begin inserting rows when data in column B changes (which I have done thanks to this forum!). However I now need to insert further rows, when the data in column C changes. I did post this on an earlier thread and thought it worked but it doesn't and suspect this is because there is blank rows.

My other option is to set the whole s/s up manually and just focus on the macros to populate it (the issue with the spreadsheet in the first place was where the number of rows etc changed, very time consuming to change manually!)

I reckon this is as clear as mud so have attached what I've done so far with some dummy data in it. It also contain a version of where I want to be at the end of this project.

Would be grateful for any help / guidance!

I have had a massive spreadsheet dumped on me from work that has some very tedious repetition in the formula entry.

The sections are 31x31 cells arranged two wide with some space in between, and twelve sections high. This data is copied by cell reference to a second section which is also 31x31 cells, but the data is at a 90 degree angle. EG, the first row of the second section is =c9, then =c10, then =c11. When I use the fill handle across the rows, the alphabetical part goes up, when I need the numeric part to go up.

I have been entering by hand, but I'm going crazy. I didn't write it, so I have no idea the reasoning behind it. I have uploaded a zip of the top two sections. The actual entire behemoth is something like 28 megs.

Thanks in advance!

Jeromey

REPOST FROM THIS MORNING WITH RELATED ATTACHMENT:

On Sheet2 (Journal Entry) trying to write a formula in rows 9 & 10 that both reference row #16 on Sheet1 (Chargeback). Formulas on Journal Entry tab will then be copied down to rows 11 & 12 but will then reference row #17 on Journal Entry tab and so on and so forth. This will be copied 100's of times and I want to automate rather than manually adjust formulas.

My issue - by copying the formulas on the Journal Entry tab from rows 9 & 10 into 11 & 12 the Chargeback tab row reference is incorrect. Copying of formulas into rows 11 & 12 refer to rows 18 and 19 when I need it to be 17.

Is there a way to write a formula to indicate sheet1 name & sheet1 column letter and then reference a cell on Sheet2 that would indicate the row number to pull?

Maybe something along the lines of:

Col A Col D
Row1 +"Sheet1name!"&"F"&$D1 1
Row2 +"Sheet1name!"&"F"&$D2 1
Row3 +"Sheet1name!"&"F"&$D3 2
Row4 +"Sheet1name!"&"F"&$D4 2

I've tried this but the formula doesn't pull the proper result and instead shows the written formula itself. What am I missing?

Sorry so long - posted before but reply was not correct - maybe I wasn't clear. Not sure if I can post the workbook I'm trying to do this on......

Thanks for any suggestions.

I'll will attach the sheet I'm working on. Please see columns on Journal Entry tab that are highlighted in yellow.

REPOST FROM THIS MORNING WITH RELATED ATTACHMENT:

On Sheet2 (Journal Entry) trying to write a formula in rows 9 & 10 that both reference row #16 on Sheet1 (Chargeback). Formulas on Journal Entry tab will then be copied down to rows 11 & 12 but will then reference row #17 on Journal Entry tab and so on and so forth. This will be copied 100's of times and I want to automate rather than manually adjust formulas.

My issue - by copying the formulas on the Journal Entry tab from rows 9 & 10 into 11 & 12 the Chargeback tab row reference is incorrect. Copying of formulas into rows 11 & 12 refer to rows 18 and 19 when I need it to be 17.

Is there a way to write a formula to indicate sheet1 name & sheet1 column letter and then reference a cell on Sheet2 that would indicate the row number to pull?

Maybe something along the lines of:

Col A Col D
Row1 +"Sheet1name!"&"F"&$D1 1
Row2 +"Sheet1name!"&"F"&$D2 1
Row3 +"Sheet1name!"&"F"&$D3 2
Row4 +"Sheet1name!"&"F"&$D4 2

I've tried this but the formula doesn't pull the proper result and instead shows the written formula itself. What am I missing?

Sorry so long - posted before but reply was not correct - maybe I wasn't clear. Not sure if I can post the workbook I'm trying to do this on......

Thanks for any suggestions.

Hi,

I'm trying to copy an IF statement across cells but can't work out how to increment the rows without affecting the columns:

I have a similar table to the one attached and would like to copy the formula in the sales tab across the row for each agent but increment only the row value. We have about 90 agents so I don't want to do it one by one by manually changing the reference. I've only included one row just for an example and included the lookup table in the same workbook which is the 01-12 tab.

Is there any easy way to achieve this?

Thanks

Alex

Hello Everyone - Happy New Year

Can you help me please as I'm sure its very easy but I'm a bit stuck!

In column X - cell X9 I want it to show that if the next column cell 3 (so Y3 in this case) = forecast then the cell equal the same that is in cell X6.
If I copy across the formulas into column Y - it works fine. But....if I then add a new column in prior to Z, cell Z9 uses column AB as it's reference instead of AA.
How can I stop the calculation doing that when entering in a new column and copying across the forumlas please???

Thanks

Hello,

Thank you for your help. My question is related to copying a formula from one column to the next adjacent column and also incrementing the 2 numeric values inside the formula. Please see attached sample.

One value increments with each new column whereas the other increments with each new row. Please see attached sample.

And in the last row there is the average formula which must copy the new column and average the new column.

I tried to create a Macro in attached but it did not work with formulas.

Once again, thank you so much for your help.

Amdnet

Hi

I am trying to find a formula that allows me to copy across a column with various text entries when the I enter in a certain lookup reference in column A2
It will also need to be capable of repeating the information if the lookup reference is entered again.

Someone on the forum kindly helped me with a formula that does what I want wish numbers but now I need it to do the same with text.

Below is the formula that works for numbers. When I enter in a reference number 1 - 2000 it will draw across the text information from the corresponding row but in column D.

=SUMIF('Appointment Tracker'!A:A,A2,'Appointment Tracker'!B:B)

Is there anyone who knows how to do this and is it possible?

Thanks
Ashley

I have a blank column in Column B in which I place an IF formula in cell B2. The formula looks at the data in Column A, and then returns a value based upon the data in Column A. I then use the fill handle to copy that formula from B2 down to the last row of data, which is usually over 6,000 rows. (B2 looks at A2, B3 looks at A3, etc.) I recorded a macro to do this and some other tasks (copy, paste special, values, sort, etc) and it works perfectly.

The problem is, one time the sheet may have 6,000 rows of data, the next time 6,100 rows, the next time 6,500 rows, etc. No two reports will ever have the exact same number of rows.

I would like to go into the VBA and change it so that instead of copying the formula down only a specified number of rows, it copies it down until there is no longer any data in Column A. How can I change the code in the macro from being a range of b2:b6100 for example to be from b2 to the last row of data in Column A. The data starts in row 2 and there are no blank rows.

BTW, I am using XL2000 if that makes a difference.

Thanks in advance for your assistance.

-dave

We have a workbook with several sheets, we have detail data gathered from many resources named 'Data'.

We then summarize the data on several data pages.

A typical formula on one of the summary sheets might look like this:

=DATA!F151-DATA!D151

which gets the data we need. However if we copy that formula down to subsequent cells, we get:

=DATA!F152-DATA!D152

We have grouped our 'Data' sheet data so that the information we need is exactly 5 rows apart, everytime. SO...we want to have the formula skip 5 rows each time we copy it down, so that ultimately we would get the following result if we copied the formula 3 times:

=DATA!F151-DATA!D151
=DATA!F156-DATA!D156
=DATA!F161-DATA!D161

We were originally given information on using a combination of INDEX and ROW to achieve this, but the tip was a bit vague, all it said was:

Try this:

(first part of your formula)
=INDEX(DATA!F:F,(ROW()+y)*5+x)

If you have an idea of how to utilize this tip, or have something different (including VBA), I'm all ears...

Thanks in advance for any assistance.

Sincerely,

=IF(ISTEXT(D2),"",COUNTIF(Scores!B6:E6,"3")+COUNTIF(Scores!G6,"3")+COUNTIF(Scores!K6,"3")+COUNTIF(Scores!M6:N6,"3")+COUNTIF(Scores!P6,"3")+COUNTIF(Scores!R6,"3")+COUNTIF(Scores!F6,"2")+COUNTIF(Scores!H6,"2")+COUNTIF(Scores!O6,"2")+COUNTIF(Scores!Q6,"2")+COUNTIF(Scores!I6:J6,"4")+COUNTIF(Scores!L6,"4")+COUNTIF(Scores!S6,"4"))

Except for the first part of the formula (=IF(ISTEXT(D2) I would like to be able to copy this formula across columns without the letters changing (ie. B6:E6 to C6:F6) instead I would like the formula to change numbers (ie. B7:E7) is there a soluion for this?

I have attached my spreadsheet. Thank you.

I am trying to copy a formula down a row. All I am getting is the exact same answer from the 1st cell instead of the new data.

Hi, I am new here and I hope you can help.

I have a worksheet called 'DR manager and deceased' which is part of a larger workbook. It contains around 20000 entries. There should be at least 2 rows for each entry, identified by the customer erefrence in column H, however a casting a quick eye down the data shows me that there are several entries where there is only one row for a specific customer reference and I need to identify these.

I need a macro that will remove the entire row from this worksheet and copy it to the 'unique' worksheet if the value in column H for that row is unique, ie only found once in the whole of column H.

Can this be done and if so how?

Thanks in advance for any help offered
Jonathan

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.

Thanks, Simon.

I'm looping through code that updates an autofilter, and then I copy the
autofiltered data to a target sheet (hundreds of times). The problem is that
it is also copying the header row, and as I add each chunk of data to my
destination sheets, I've got unwanted repeating header rows throughout the
data.

Is there a good way to select the autofiltered rows while excluding just the
header row? When I record my desired activity via macro, is selects the first
row, which will vary depending on what data I'm autofiltering on in each loop.

Current code:
Sheet2.Activate
Sheet2.Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=SortableTempVal
Sheet2.Cells.Select
Selection.Copy

Recorded macro selecting the visible rows excluding the header row:
Rows("752:752").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

If there isn't a good way, I suppose I can write some less elegant code to
delete the first pasted row in each destination worksheet, but I was hoping
to learn something new today

Thanks!
Keith

does this look like it would do the job of auto filling the formula to
new rows if I stick it into the sheet code ?

Private Sub Worksheet_SelectionChange2(ByVal Target As Range)
Application.EnableEvents = False
If Target.Offset(-1, 0).HasFormula = True Then
Range(Target.Offset(-1, 0), Target).FillDown
End If
Application.EnableEvents = True
End Sub

I did it and turned off the extend data range formats and formulas in
tools, options and edit...

SO I am thinking the script isn't working like it should, how close is
it ?

This is just a sample worksheet. I have got a worksheet with having 3 coloumns A, B & C. Column A contains E Code, while Column B is of time which user will enter. Column C contains the time in Hours.
I have entered one record for example. Now, whenever user enters the value in B3, then formula from C2 should be copied to C3 i.e it should be =B3/60.

I want this to be done using VBA. Pls help me out. I want to use this feature in one of my another files which requries this feature.

Thanking in advance...

Srinivas

I have a pretty simple embedded query in an excel 2003 spreadsheet.
The query looks up information from our database for each work order number entered in column A.

The query as built works for one row but now I need to figure out how to get the cell referenced in the parameter value to vary as I copy the contents of the working row into row 2,3,4 etc and change the work order value in column A 2, 3, 4 etc.....

I do not know what function to use when defining the parameter value that will allow this to happen when I copy the query into a new row.

Currently the query parameter setting references cell A1 and works.
Now when I copy the query to row 2 I would like the parameter to reference A2 like when copying cells using a relative cell reference.

Here is the current SQL code that is running

SELECT wo_mstr_0.wo_nbr, wo_mstr_0.wo_so_job, wo_mstr_0.wo_due_date, wo_mstr_0.wo_part, wo_mstr_0.wo_qty_ord, wo_mstr_0.wo_qty_comp, wo_mstr_0.wo_status, wo_mstr_0.wo_vend, wo_mstr_0.wo_rmks
FROM PUB.wo_mstr wo_mstr_0
WHERE (wo_mstr_0.wo_nbr=?)

Any help pointing me in the correct direction would be a blessing.


No luck finding an answer? You could always try Google.