Transpose, but preserve, a range of formulas?

I'm using Excel 2000.  I have a column (precisely, a 1-column by
12-row range) containing formulas.  I want to end up with a transposed
copy of this range (12 columns by 1 row) in which each cell contains
exactly the same formula as the corresponding cell of the source
range.

For a simple example, suppose range H7:H18 contains these formulas:

H7  =E7+G7
H8  =E8+G8
etc.

I want to copy this range into C44:N44 so that I end up with

C44  =E7+G7
C45  =E8+G8
etc.

Is there an easy, direct way to do that?  If not, is there an indirect
way, short of brute force, and how does it work?

Thanks in advance.

--Erv
0
ervyoung (2)
10/22/2003 8:55:59 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
630 Views

Similar Articles

[PageSpeed] 39

Do you really need a copy of the formulas, or will a linked range do.  Both are possible, but the
linked range is slightly quicker:-

With your data in say A1:A5, select say C1:G1 and type =TRANSPOSE(A1:A5) and then array enter it
using CTRL+SHIFT+ENTER at the same time.

Other way is to select your range of formulas in A1:A5, do Edit / replace and replace = with %%.
Then copy the range A1:A5 and Edit / Paste Special / Transpose wherevere you want.  then select
your new tranposed range and do Edit / Replace replacing %% with =.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Erv Young" <ervyoung@nc.rr.com> wrote in message
news:50a6427b.0310221255.26301e90@posting.google.com...
> I'm using Excel 2000.  I have a column (precisely, a 1-column by
> 12-row range) containing formulas.  I want to end up with a transposed
> copy of this range (12 columns by 1 row) in which each cell contains
> exactly the same formula as the corresponding cell of the source
> range.
>
> For a simple example, suppose range H7:H18 contains these formulas:
>
> H7  =E7+G7
> H8  =E8+G8
> etc.
>
> I want to copy this range into C44:N44 so that I end up with
>
> C44  =E7+G7
> C45  =E8+G8
> etc.
>
> Is there an easy, direct way to do that?  If not, is there an indirect
> way, short of brute force, and how does it work?
>
> Thanks in advance.
>
> --Erv


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003


0
ken.wright (2489)
10/22/2003 9:09:13 PM
"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message news:<elWs$COmDHA.2528@TK2MSFTNGP12.phx.gbl>...
....
> 
> With your data in say A1:A5, select say C1:G1 and type =TRANSPOSE(A1:A5) and then array enter it
> using CTRL+SHIFT+ENTER at the same time.
> 
> Other way is to select your range of formulas in A1:A5, do Edit / replace and replace = with %%.
> Then copy the range A1:A5 and Edit / Paste Special / Transpose wherevere you want.  then select
> your new tranposed range and do Edit / Replace replacing %% with =.

That does it very nicely.  Thanks, Ken.  I had given up on Transpose()
because I didn't know about Ctrl-Shift-Enter.  I think I had been a
bit obtuse about the Edit/Replace; replacing the = with _something_
makes it a lot easier to fix back up than it is if you replace it with
nothing.  Mmm, yes, I remember that now.

--Erv
0
ervyoung (2)
10/23/2003 3:55:04 AM
LOL - I know the feeling - Drives me nuts when I know I've done it before, but just can't find it.
Anyway, glad you are sorted now, and appreciate the feedback.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Erv Young" <ervyoung@nc.rr.com> wrote in message
news:50a6427b.0310221955.7a48cd5d@posting.google.com...
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:<elWs$COmDHA.2528@TK2MSFTNGP12.phx.gbl>...
> ...
> >
> > With your data in say A1:A5, select say C1:G1 and type =TRANSPOSE(A1:A5) and then array enter
it
> > using CTRL+SHIFT+ENTER at the same time.
> >
> > Other way is to select your range of formulas in A1:A5, do Edit / replace and replace = with
%%.
> > Then copy the range A1:A5 and Edit / Paste Special / Transpose wherevere you want.  then
select
> > your new tranposed range and do Edit / Replace replacing %% with =.
>
> That does it very nicely.  Thanks, Ken.  I had given up on Transpose()
> because I didn't know about Ctrl-Shift-Enter.  I think I had been a
> bit obtuse about the Edit/Replace; replacing the = with _something_
> makes it a lot easier to fix back up than it is if you replace it with
> nothing.  Mmm, yes, I remember that now.
>
> --Erv


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003


0
ken.wright (2489)
10/23/2003 3:34:49 PM
Reply:

Similar Artilces:

Can I create a recurrence formula in Excel e.g. A(N+1)=A(N)+2
So if e.g. A(1)=5, and N goes from 1 to 6, I will get a sequence as follows 5,7,9,11,13,15 Enter the first 2 cells, ie in A1: 5, in A2: 7 Then select A1:A2, copy* down to A6, and Excel will fill the series as required *drag the fill handle at the bottom right corner of A2 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "daibach" wrote: > So if e.g. A(1)=5, and N goes from 1 to 6, I will get a sequence as follows > > 5,7,9,11,13,15 "daibach" <daibach@discussions.microsoft.com> skrev i en meddelelse news:D232E8D4-28DC-4E35-A2C4-6964...

formula question #27
I have an array of numbers like 100 0.01 200 0.015 300 0.02 400 0.025 500 0.03 say the range name is tab1 and an array of numbers like (call it tab2) 150 250 350 I want to look up the numbers from tab2 in tab1 and multilply it by the 2nd column from tab1 and add up the result. So, I want 150*.01+250*.015+350*.02 I tried as an array formula =sum((vlookup(tab2,tab1,2)*tab2) but this does not work. What should I be doing? TIA Say your tab2 info in col. A. In col. B type vlookup(A1,tab1!$A$1:$B$5,2,TRUE) Note: Use "true" in the vlookup, since your values in tab1 col. A and ...

Finding MAX of a conditional range???
Hi everyone, I would like to know if there is a way /set of formulas/ to find MAX/MIN/ of a range that is defined by certain conditions. To be more specific I am giving an example: I need to find MAX of range of numbers /1, 3, 6, 8, etc./ and the range is defined by two dates. To each number correspond a specific date. In other words, I would like to find MAX of the range between two dates. I tried a combitanion of MAX and SUMPRODUCT formulas, but no success. Any help is welcome. kras -- kras ------------------------------------------------------------------------ kras's Profile: ht...

Plot Range ?
I am wondering if anyone can tell me a simple macro or way on workbook open to flag when the plot range has been expanded ? For example.. I have several workbooks which have macros that automatically update the data in these workbooks and also update / expand the plot range of the charts based on the data in the workbook. I am looking for a macro that can pop up a message box letting me know when a plot range has been expanded since the last time the workbook was opened. Dan Thompson ...

How to filter a coulered cell range?
I have a range that contain a coulored cells in red yellow and green (for example), How can I fillter the yellow cells? I use office 2007 First turn on AutoFilter: Office Button > Sort & Filter > Filter The touch the pull-down and Filter by Color -- Gary''s Student - gsnu201001 "haliem" wrote: > I have a range that contain a coulored cells in red yellow and green (for > example), How can I fillter the yellow cells? I use office 2007 Place the cursor in Header Row (i.e. row 1) and press Alt+D+F+F which will apply the filter for your d...

Referencing defined range
Hello, I have three named ranges (percentSales,percentMaterial,percentLabor). When a user types into an account cell and wants to retrieve information for percentSales, they would type "Sales". How can I take this input into a function and refer to the percentSales range. I tried percent&"Sales", but this is not working. It errors out because it is not refering to the range, but just the text "percentSales". Below is an example of what I am doing. What can I do to take the input and refer to the named range. What I would like to happen =INDEX(per...

IF formula with multiple data range in cell
I'm trying to create an IF formula that returns data after searching a cell that contains more than one data range. For ex: Cell B3 contains GCVW-00001, GCVW-00002 I need the formula in C3 to return Yes if B3 contains GCVW-00001 and I need the formula in D3 to return Yes if B3 contains GCVW-00002 I'm able to get the formula to return yes or no correctly if cell B3 contains either GCVW-00001 or GCVW-00002, but the formula won't work if it contains both at the same time. right now my basic formula looks like: =IF(B3="GCVW-00001","Yes","No&q...

transposing info.
I want to transfer information from one spreadsheet to another with different titles on the columns. How do I do this? I want to direct the values on the column which has a different title column to the sheet that I want to move it to. Thanks guideme wrote: > I want to transfer information from one spreadsheet to another with > different titles on the columns. How do I do this? > > I want to direct the values on the column which has a different title > column to the sheet that I want to move it to. > > Thanks This is more of moving data to another sheet which has di...

Excel variable Range
I receive a number of spreadsheets with different number of rows. I want to copy a particular calc to a new column for all rows. I can create a macro to do all this except I do not know how to tell the macro to paste to the last row which cam be different in each sheet. Any advice please. LastRow can be calculated with iLastRow = Cells(Rows.Count,"A").End(xlUp).Row -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Gerry" <anonymous@discussions.microsoft.com> wrote ...

Entering Values and Updating Next Empty Cell in a Range
Please help, I hope there is a macro. Every time I enter a value in cell j29, I would like it to post to the next empty cell in range b139:b150. For example, if I enter 24 and it populates b139, the next value I enter in j29 should post in b140 Thanks - Tom Hi You don't say what you want to do if row 150 is already filled. This code will do what you want, and i have stopped it at B150. Remove the stop if that is what you want Private Sub Worksheet_Change(ByVal Target As Range) Dim lr As Long If Target.Address <> Range("J29").Address Then Exit Sub lr ...

Preserve number as text in Pivot table sum of values field
Does anyone know how I can create a table like this: first column: second column: third column: (product name) (product number) (sum of products) I know how to create the first column (via "row labels") and third column (via "sum values"). But for the second column, if I put the fields under "row labels" the names get indented under the product name. If I put the fields under "sum values" the names gets counted. I'd like to preserve the product numbers as text. Thanks in advance fo...

Embeded IF
Using Excel 3. I am trying to assign ABC codes to long list of products based on % of sales. A10 is part #, B10 is %. In C10, need formula to pick a code from following table. The table changes periodically. Code A=over 80% Code B=between 60 and 80% Code C=between 40...60% Code D = less than 40% -- Emily >The table changes periodically. Create a 2 column table like this... 0%...D 40%...C 60%...B 80%...A Assume the table is in the range J2:K5 Then...this formula entered in C10 and copied down: =LOOKUP(B10,J$2:K$5) -- Biff Microsoft Excel MVP ...

Excel Column Formula
I have done this before..but I cannot remember how: input a formula for an entire column (that calcs values individually for each row), that takes different values for each calculation from a different column. ex: I have column a and b with numbers, and column c that I want the summation of these values (individual rows). Please help me out! -- grandmaster ------------------------------------------------------------------------ grandmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30503 View this thread: http://www.excelforum.com/showthread.php?threadi...

Formula bar height in XL2K3 w/XP
With multi-line text (Alt-Enter) in the Formula Bar, it automagically extends the height of the bar. This interferes with column actions since the bar now obscures the column headings (A through ...). Painful when the 1st cell in a column is multi-line. Can this 'feature' be controlled? Controlled ... like toggle off and on? <Alt> + <V><F> OR <View> and check - uncheck <FormulaBar>. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ======================================...

resizing a range
I can't the following code to work. I am trying to resize a range so I can insert columns. Sub ResizeIT() Dim range1 As Range Dim range2 As Range Set range1 = Worksheets("UnitTemplate").Range("CS7") Set range2 = range1 Worksheets("UnitTemplate").Range("range1").Resize(0, 3).Select Selection.EntireColumn.Insert End Sub Any help would be appreciated! Try this: Sub ResizeIT() 'Inserts 3 columns into worksheet at cell CS7 Worksheets("Sheet1").Range("CS7").Activate Selection.Resize(1, 3).Select Selection.Enti...

Transposing list of numbers
I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like to transpose it across 7 columns and 58 rows. Is there an easy way to do this? I understand I can do the copy/paste special/transpose for 7 at a time. Thank you. -- jlhcat Sub ColtoRows() Dim rng As Range Dim i As Long Dim J As Long Set rng = Cells(Rows.Count, 1).End(xlUp) J = 1 On Error Resume Next nocols = 7 'InputBox("Enter Number of Columns Desired") For i = 1 To rng.Row Step nocols Cells(J, "A").Resize(1, nocols).Value = _ ...

Transpose question
I have a MASTER summary worksheet with this layout 07-Sep 14-Sep 21-Sep 28-Sep 05 - Oct Total No of late deliveries 36 40 20 10 9 115 No of Drivers 18 32 11 11 4 76 getting its info from WEEKLY worksheets eg 03-Sep 04-Sep 05-Sep 06-Sep 07-Sep Total No of late deliveries 7 8 7 7 7 36 No of Drivers 4 3 1 6 4 18 I would like the master layout to be like : No of late deliveries No of Drivers 07-Sep 36 18 14-Sep 40 32 21-Sep 20 11 28-Sep 10 11 05-Oct 9 4 Total 115 76 But as the values in the MASTER worksheet ...

IF FORMULA need help
Please, I am trying really hard to work this out, but still I couldn't come to result I want. This is the example (invoices - paid, not paid...) column A..................... column B...................... column C DUE DATE....................PAID ON........................COMMENT 10.11.05.....................07.11.05........................paid, on time 15.11.05.....................20.11.05........................paid, late 20.11.05...................(empty cell)......................not paid, late 04.12.05...................(empty cell)......................not paid, not late So, you...

Countif formula between numbers
I'm have a bad day and can't figure this simple question out. I want Excel to count a column of numbers and show the the total cells with values between 100 and 125. The formula I have thus far is '=countif(H5:H125,">=125<=100") why is this not working??? This has been asked about a dozen times today. Homework assingment? =sumproduct((rngA>100)*(rngA<200)). modify to suit -- Don Guillett SalesAid Software donaldb@281.com "dandigger" <dandigger@discussions.microsoft.com> wrote in message news:1D44A5E6-ECAB-441D-B9D8-409D23D0369E@microsof...

How to assign range to Double array?
How can I assign the value of a range to a Double array? The best I can do is a For Each loop. For example: Function myIRR(myVal As Range, _ Optional myGuess As Double = 0.1) Dim dVal() As Double, n as Long ReDim dVal(1 To myVal.Rows.Count) n = 0 For Each cell In myVal: n = n + 1: dVal(n) = cell: Next myIRR = IRR(dVal, myGuess) End Function If dVal() were Variant, I could copy the range simply as follows: Function myIRR(myVal As Range, _ Optional myGuess As Double = 0.1) Dim dVal() dVal = myVal myIRR = IRR(dVal, myGuess) End Function But that results in a Type...

Additional column formula in pivot table
I am a relative newbie to pivot tables. Using Excel 2003 I have, for example, 8 columns of quarterly financial data. I would like to add a 9th column to calculate the CAGR for the 8 quarters. Can that be done within the pivot table? ...

Formula #27
How do I enter a formula to calculate a 7% sales tax? If A1 holds the pre-tax price then =A1*7% will compute the sales tax while =A1*1.7 will compute the price_with_tax-included. Now all this is mathematically correct but we work in dollars and cents (or pound and pennies etc.), so we need to do some rounding to the nearest cent sales tax: =ROUND(A1*7%,2) price-with-tax =ROUND(A1*1.07,2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "MissM07" <MissM07@discussions.microsoft.com> wrote in message news:344FF6D4-...

Want to find max in a range then return a name from a cell
The spreadsheet has 5 people each person has a list describing thei sales performance. Each has their highest sale, highest over cost sale etc. I have a table with a describtion highest sale, highest over cost sal etc. I want the cell beside each title to calcute who has the highest figur and place their name in this cell. :confused: I have a spreadsheet with lists containing highest sale highest average sale etc. What I want to do is create a formula tha will find the max value within a list please note these values ar spread out and do not run on the spreadsheet side by side or one aft...

SUMPRODUCT between date range
I have successfully used SUMPRODUCT thanks to this discussion group but am having a problem trying to capture a date range. Is it possibly the way I’m entering the date? HELP! I woke up last night dreaming about this… Date of Referral Homeless/At-Risk Received MV Services? 09/02/06 Homeless yes 09/15/06 Homeless yes 10/02/06 At-Risk yes 09/20/06 Homeless no I need to capture the number of students fo...

Multiple Ranges for a Chart
I am trying to use ranges from several pages in one chart. When I set the source data to: =Sheet1!$A$2:$O$2+Sheet2!$A$3:$K$3 I get the following error: Reference is not valid HELP, what am I doing wrong? Are you trying to use ranges from multiple sheets for the same X or Y series? I've never seen that done. You can use X axis ranges from one sheet and Y axis ranges from another. "Vic" wrote: > I am trying to use ranges from several pages in one chart. When I set the > source data to: =Sheet1!$A$2:$O$2+Sheet2!$A$3:$K$3 > > I get the following error:...