Copying Formula With Fixed Rows But Different Columns?

Nov 27, 2013

Im using this formula in a spreedsheet =(B$3*0,5)+(B$3+(B$4*0,5))+(B$3+B$4+(B$5*0,5))+(B$3+B$4+B$5+(B$6*0,5))+(SUM(B$7:B$28)*4)

What i want is to be able to drag this down for multiple rows but i want the formula above to only change columns i.e.

Next row gets C$3 and so on.

View 3 Replies


ADVERTISEMENT

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

Rows To Columns For Fixed Criteria

Jun 24, 2009

i have a unique problem. I have a spreadsheet with 2 worksheets.

Worksheet A

Dlr# Dlr Name Zip Code
X123 Dane's 50266
X123 Dane's 50266
X123 Dane's 53135
X983 Andy's 50254
X983 Andy's 50254
X348 Ryan's 45678

So, this spreadsheet has some duplicate rows and some unique ones. All are needed as duplicate zips mean that the dealer is sending out multiple mailings to the same zip code.

My task is to transfer these into Worksheet B so that duplicate zips go in different columns but unique zips don't. So this is what Worksheet B will look like:

Worksheet B

Dlr# Dlr Name Drop 1 Drop 2 Drop 3
X123 Dane's 50266 50266
X123 Dane's 53135
X983 Andy's 50254 50254
X348 Ryan's 45678

My spreadsheet has over 500 dealers with over 1500 rows of data in it, so if there is an automated way to create spreadsheet B.

View 9 Replies View Related

Dynamic Range Of Columns With Fixed Rows

Jan 5, 2009

I am still trying to develop a macro that will copy and paste a set of formulas into a dynamic destination range. I have included a worksheet that shows what I am trying to do. Basically when using AutoFill I need the columns of the destination range to be dynamic based on the number of rows in another sheet.

View 13 Replies View Related

Fixed Range Name When Adding Rows/Columns/Cells

Aug 25, 2006

How do I define a " Range Name" that is fixed? So if I insert a row above the range it has no effect on the orginal range.

IE: Range Name "MyRange" = A10:A100

I insert a new row at A4 then "MyRange" = A11:A101

I want it to stay at A10:A100.

View 5 Replies View Related

Excel 2010 :: Pivot Table With Fixed Rows And Columns?

Jun 21, 2014

I'm trying to produce a fixed format pivot table in Excel 2010. Normally I'd just construct a manual table using COUNTIFS, SUMIFS etc, howver, for this exercise the requirement is to be able to click on any field and have a tab pop up with the relavent data a la Pivot Table. But....I need the Pivot table to have a fixed format (which I can do with 'Preserve cell formatting on update') and to have all of the rows and columns in irrespective of whether there is data or not (i.e. if I haven't sold any apples in June, I still want the 'Apples' field to appear, just with a value of zero). I had assumed that the option I needed was 'Show items with no data on rows/columns' but these options are greyed out. I've tried right-clicking on the whole table, on individual fields and on labels but still get the same greyed out options.

Is there any way I can retain all of the rows and columns?

View 1 Replies View Related

Sorting Certain Columns But Ensuring Certain Rows Remain Fixed To Their 'master Row'

Jan 22, 2009

I have created a spreadsheet which is tallying up various data. I have a number of different categories e.g. A, B, C, D and within each one of these are sub categories. I then have a tally for the number of each category and within that the number of each sub category. I then want to sort the data so that first the main category is sorted in order of highest tally to lowest. the sub categories however need to stay with their primary category. I then need to sort each sub category within the main category.

the data looks omething like this:

category tally sub cat number

A 6 A 3
B 4
C 6
B 9 A 1
B 6

View 9 Replies View Related

Using Autofill With Fixed Rows In A Formula

Mar 8, 2013

I'm trying to autofill "vertically" in a spreadsheet using a formula which loads value from a different sheet.

Lets say I have cell (A1 for example) ='2013'!A39

Now I want to fill A2-A30 in a way that the formula updates to ='2013'!B39, ='2013'!C39, ='2013'!D39...and so on

I've tried using ='2013'!A$39, but this only copies the formula as it is. When I try to autofill cell A2-A30, the formula still is ='2013!A$39

Is this possible to do?

View 1 Replies View Related

Automatically Copying 9 Rows And Converting To Columns

Dec 17, 2012

I need to take a list of about 1,500 rows of data (single column) and convert it into columns of 9 entries. I know that I can select nine rows and then paste using transpose... But any way of "automating" the process?

So

1
2
3
4
5
6
7
8
9
ETC
ETC

becomes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 etc....

View 5 Replies View Related

Copying And Pasting Formatted Columns And Rows

Jul 10, 2014

So, I have a document 25 pages long. It has 26 sections (1-26) and each section has up to 10 (.1 - .10) sub sections which contain 16 (a-p) sub-sub-sections. There are 6 columns of varying widths. Some cells, in a row, merge into adjacent cells.

I would like to break this document into 26 documents - one for each section - so that I may recombine and regroup them in the future incorporating a few modifications. Whenever I copy and paste to a new Workbook, I loose the formatting of column widths. Resetting these alone takes lots of time.

I have come up with is to duplicate the entire document and delete everything above and below the section I would like to save. Then repeat for next section. There's got to be a better way?

View 10 Replies View Related

Macro For Copying Cells To Columns And Rows

Sep 22, 2012

Why does my macro mess up on second line C23 and not places Play Equipment or the amount in the right column it stays on row C22

[URL]....

Code:

Private Sub CommandButton1_Click()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)

[Code]....

View 2 Replies View Related

Copying Formulas Into Adjacent Columns But Same Rows

Nov 13, 2006

If I have formulas in A1, A5, A19, and A36, highlight them with goto, how do I move them into B1, B5, B19, and B36? Copying and pasting puts them in B1, B2, B3, and B4.

View 9 Replies View Related

Excel 2010 :: Copying Data Form Columns To Rows?

Mar 27, 2012

I need something that will take data from columns in one Spreadsheet and put in difference cells in a row. I know this could be done with recording a macro but the number of column will never be constant.

Below I attached examples of the Spreadsheet

Financials SpreadSheet
Need to have the data in column B to F put their respective cells in row in the
Master Spreadsheet
So we would have 5 rows.

Excel 2010ABCDEFGH5Job Number17542000250030003500Total6Date Booked01-Mar-1215-Mar-1215-Mar-1223-Mar-1223-Mar-127SalespersonJames ThorntonHoward StandenHoward StandenIan BullimoreSylvia Walton8AdvertiserNestleTalkTalkLloyds BankSkodaHonda9ProductNature ValleyBroadbandMortgageApril

[Code]....

View 6 Replies View Related

Increment Row Number Within Formula When Copying Across Columns

Mar 16, 2009

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.

View 8 Replies View Related

Finding Last Cell And Copying Down Formula In Multiple Columns

Feb 11, 2013

I have the following code which works but only fills down one row and I want it to fill down to the last row but I cannot work it out.

Dim Col As Range
For Each Col In Range("L:Q").Columns
With Cells(Rows.Count, Col.Column).End(xlUp)
.Offset(1).Formula = .Formula
.Resize(2).FillDown
End With
Next

How to get it to copy down to the last cell in all the columns.

HKDDSHH
31-Jan-13
HUS
8
8
8
0
-896.469
637.037
-636.555
-895.987
January
2013
Comp
-112.059
Thursday
Midweek

[Code] ........

View 5 Replies View Related

Sumproduct With Fixed Columns

Dec 6, 2013

Im new to excel and problem regarding the sumproduct function.

I have three columns in my sheet1, column A and column B contain 1000 numbers.

Hence, column A goes from A1 to A1000 and column B from B1 to B1000.

In the first row of column C, i want to use sumproduct on A1:A5 with B1:B5.

My problem arises when I want to drag the formula down, in the second row of column C I want

to use sumproduct on A6:A10 with B6:B10 but the formula uses A2:A6 with B2:B6.

Is there a way to fix the ranges when I am using sumprodct so it takes "fixed" ranges like every fifth,

A1:A5 with B1:B5 , A6:A10 with B6:B10, A11:A15 with B11:B15 and so on.

View 2 Replies View Related

Split Text Into Fixed Length Columns

Jul 20, 2012

I have 5 columns with data in each

I want to create a 6th column that looks to the columns on the left with data in ti and concatenates all data in the 5 columns and puts it into one cell in the 6th column however put a space between each break of data so that it can be distinguished which bit of data was in what column previously.

The challenge is the new 6th column can only contain 30 characters - When it exceeds 30 characters then create a 7th column and put the rest of data in the 7th column, again the 7th column can only have 30 characters so if exceeds this then put the remaining characters in a 8th column

There will never be more than a total of 90 characters in the original 5 columns so there will only need to be scope for a maximum of 3 additional columns

So for example

Column A had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column B had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column C had a word that contained 10 characters
Column D had a word that contained 5 characters
Column E had a word that contained 10 characters

Then the result would be

Column F would only have the data originally held in Column A (because it can't include Column B's data as this would exceed the 30 characters)
Column G would have data that was originally held in column B and column C - with a space between B and C data
Column H would have data that was originally held C, D and E - with a space between C, D and E data

Another point to consider is if in one of the orginal 5 columns had say 3 words in it and lets say the 3rd word is the word that exceeds the 30 character limit, then the whole of the third word is to be carried oved to the next new column, I can't have words cut in hlaf with one half in Column 'F' and the other half in Column 'H' for example.

View 1 Replies View Related

Excel 2010 :: Copying 3 Rows Of Formula And Using Autofill To Only Add 1 Each Time When Copied Down?

Sep 2, 2013

I am using Windows 7, with Excel 2010.

I have one Worksheet Short Course - PB's Which contains all swimmer information and searches through all previous swims and reports back the swimmers current Personal best times (PB)

I am creating a work sheet to calculate percentage increases over a set date period. I have managed the calculations but can not get the autofill to function as I was hoping.

In my short course sheet 1 Row = a Swimmer and there details

In my new sheet, I have 3 rows for the same swimmer

Row 1= Swimmer and PB's before a set date
Row 2 = Swimmer and PB's After set date and upto Todays date
Row 3 = Percentage calculation of difference between the two rows to enable track performance increase

I have all of this working and in place and want to copy the formula's down now to cover all swimmers in the club.

When I copy the 3 lines down, Autofill adds 3 to the row reference for the first line and I just want it to add 1.

ROW3) =IF('Short Course - PB''S'!A3="","",'Short Course - PB''S'!A3)
ROW4) =A3
ROW5) = A3

[Code].....

View 4 Replies View Related

How To Format Text To Columns When There Is No Delimitation Or Fixed Widths?

Dec 21, 2007

I need the different categories to be separated into separate columns, but they do not appear to be separated by anything useful or a fixed width. Not only that, but the last few columns were moved onto a second line when I copied the data into Excel for some reason. The data originally came over in an Adobe Acrobat file. This is what the final product should look like: ....

View 9 Replies View Related

Copy The Corresponding Rows To A Fixed Position

Jan 10, 2010

I'm getting random data and i want to put it in a fixed formatting

Therefor i need to be able to search a name and copy the corresponding rows to a fixed position

I put in a file

View 14 Replies View Related

Counting Rows From A Fixed Cell

Feb 21, 2010

I want to count the number of rows from a fixed cell (eg. $A$2) to a "named cell" (eg. "Department Winn").

View 9 Replies View Related

Fitting Fixed Number Of Columns To Page Size For Printing?

Sep 24, 2011

I'm struggling with getting my Excel worksheet to fit nicely on the pg for printing to .pdf. I could tinker with column widths manually to work this out for a printed report. But I need to print a couple hundred reports with varying column widths, so I need a vba solution.

The context is that I'm using vba to process two sets of interlinked data on a worksheet. I only want to print one set of data, and keep the second set out of the print view. To do this, I'm keeping the first set of data in columns 1 to 10 and the second set of data in columsn 21 to 30. I set the print range to only include columns 1 to 10.

This works fine for keeping the second set of data off the printed pg. The problem is that there is always a gap between the end of column 10 and the maximum width of the printed pg. Given the formatting of the report, this doesn't look very good.

I know that page setup offers the "fit to" option. This would work if I wanted to fit everything on the worksheet on the pg, but I don't. I just want to fit columns 1 to 10 to the pg. Is there a way to do this, allowing for varying widths in columns 1 to 10? Is there a way to do it via vba?

View 2 Replies View Related

Macro To Recognize Content Between Two Fixed Values And Transpose To Columns?

Oct 26, 2012

I have a long (190,000) list of customer data, all in Column A (unfortunately with blank rows among it, but working now to fix that).

Down the column, individual customers are bookended by a "adf" and a "/adf". (these have open and close brackets like HTML code, but I cannot reproduce them in this forum).

For each customer, I need to find the rows that begin with (brackets spelled out since I do not know how to show them):

1. [open bracket] vehicle status

2. name part=3D"first"[close bracket]

3. [open bracket]name part=3D"last"[close bracket]

4. [open bracket]email[close bracket]

5.[open bracket]phone time=3D

6.[open bracket] name part=3D

And transpose only those rows it into columns.There is a dynamic number of rows for each customer, so there's no way to simply count and transpose, as the columns would all be mis-entered.Somehow it needs to recognize those 6 row items, and transpose those values only, with the and the only telltale of a start and finish of a specific customer.

EDIT: How about a macro to delete all rows except those that contain those partial values above?

View 3 Replies View Related

Text To Columns Macro (fixed Width) With Predefined Format On Result

May 18, 2009

I need to fixed width-text to column macro and found a reply in the forum.

However, when I apply the macro, the result of zeros in front of figures disappear since the format of value in splitted column doesn't predefined as text

e.g. sample text to split to column:
000122042009ABCDEFG00567

Required result:
0001|22042009|ABCDEFG|00567

when running below macro; result shows:
1|22042009|ABCDEFG|567
(Beginning zeros figures of the first and last column disappear)

Applied Macro:

View 3 Replies View Related

Formula With Non Fixed Row Number?

Dec 14, 2013

For instance I have a formula such as =sum(A1:A9). But imagine that the range is not always till row 9 but depends on the total number of rows that are in the table. How can I change that 9 so that it takes a number equal to the total rows in the table?

The final formula I want is not that simple one. It is actually

=INDEX($F$1:$F$1047,MATCH(1,INDEX(($G$1:$G$1047=G2)*($L$1:$L$1047<>"47200")*($L$1:$L$1047<>"47700"),0),0)).

I want to change 1047 by the relevant number of rows. Also, I do know how to do it in VBA, but I have a problem with the length of the formula there (not that one, but other bigger than that)

View 5 Replies View Related

Copying Invisible Columns, But Not Invisible Rows

Nov 4, 2008

I'm using the following code, which I figured out quite by accident out of happy coincidence that somebody else on here asked about selecting only visible rows:

View 4 Replies View Related

Formula Result Which Is Fixed After A Certain Date/time

May 21, 2007

way to get a formula result to freeze or remain unchanged AFTER a certain time?

I tried using a macro which runs when the file is opened and does a crude 'copy/paste values' operation and replaces the Formula results with their values, but its is not very 'nice'!

I need a table to change a pattern of values but not in the past, but still to remember what that value was....!

View 9 Replies View Related

Insert Row At Active Cell With Formula From Fixed Row

Jun 20, 2007

I want to insert a new row that contains the formulas of a fixed row (1:1).
The inserted row is changeable and is determined by whichever is the current active cell.

Eg:

Active cell is something random like E16

I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1

View 9 Replies View Related

Shift Range Of Cells To Calculate Formula By Fixed Number

Mar 10, 2009

How can I shift the range of cells I want to calculate an average from? For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10) And next I want to get the average of cells A11 to A20, so =AVERAGE(A11:A20). But I don't want to manually change the data or delete rows all the time.Is it possible to do something like: =average(A1+10:A10+10)??

View 4 Replies View Related

Formula Down Rows When Copied Across Columns

Aug 24, 2006

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

View 9 Replies View Related







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