Free Microsoft Excel 2013 Quick Reference

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

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

- Increment by row when copying across columns
- Increment by row when copying across columns (alternative me
- Increment Column by 1 Row when copied across columns...then copy formulas down wrkshk
- Increment Row When Copying Across Columns
- Formula Down Rows When Copied Across Columns
- Add Running Formula One-By-One when info entered in adjacent Column in the same Row.
- Inserting blank rows when data in column changes
- Increment Row Number Copying Across Columns
- Repeat row reference two lines in a row when copying down formulas
- Repeat row reference two lines in a row when copying down formulas
- Incrementing IF formula's by rows
- Calculation that when copied across does not miss a newly inserted column.
- Increment Row Number Within Formula When Copying Across Columns
- Copy across a column with various text entries
- Fill Formula to Last Row of Data
- Skip 'n' rows when copying formula
- Make my formula copy across columns
- Formula Not Capturing Data When Copied Down
- Delete Row When Value In Column Is Unique
- Skip X Columns In Formula Reference When Dragging Across Columns
- Exclude header row when copying autofilter results? (XL2003)
- Auto fill formula on new rows
- Copy Formula from previous row when data is entered
- MSQuery Varying Parameter by Row

VB: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(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

=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:Note that the Sheet name is supplied to the first ADDRESS function only=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

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

=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)

=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

Thanks for your help!

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.

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!

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

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.

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

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

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

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

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 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,

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

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.

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

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 ?

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

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.