Autofill from column to row

I have a worksheet with data in a column and want the data on another sheet 
in a row, sorted in another way. I tried to use autofill to get data from 
every 25th cell in the column to the row on the other sheet, ['sheet 1'!F2"] 
in cell B2, ['sheet 1'!F27] in cell C2, ['sheet 1'!F52] in cell D2 and so on. 
Excel autofill wont understand what I want, is there any other way of solving 
this?
0
Holmberg (4)
3/22/2005 9:23:06 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
936 Views

Similar Articles

[PageSpeed] 17

One way

In the destination sheet:

Put in B2:

=OFFSET('Sheet 1'!$F$2,COLUMNS($A$1:A1)*25-25,)

Copy B2 across to D2

B2:D2 will return the desired contents, viz.:

in B2: ='Sheet 1'!F2
in C2: ='Sheet 1'!F27
in D2: ='Sheet 1'!F52
etc

(at intervals of each 25th cell down)
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Magnus Holmberg" <Magnus Holmberg@discussions.microsoft.com> wrote in
message news:555BCAF4-F77B-4B3E-AF6D-5E1995641E10@microsoft.com...
> I have a worksheet with data in a column and want the data on another
sheet
> in a row, sorted in another way. I tried to use autofill to get data from
> every 25th cell in the column to the row on the other sheet, ['sheet
1'!F2"]
> in cell B2, ['sheet 1'!F27] in cell C2, ['sheet 1'!F52] in cell D2 and so
on.
> Excel autofill wont understand what I want, is there any other way of
solving
> this?


0
demechanik (4694)
3/22/2005 10:00:41 AM
Reply:

Similar Artilces:

Creating a combination chart using cluster column & stacked bar t.
I need to create a combination chart, using cluster column and stacked bar types. I need to "cluster" two columns side by side without space in between, with stacked bars within each of these columns. Then, have space along the x axis, and have 2 more columns clustered made up of stacked bars within. I am charting 2 years side by side, with different types of data within the columns, so need to stack/segment the column. How do I create and combination chart, and how do I select these 2 types to combine together? HELP, PLEASE?!? If you stagger your data, you can create sid...

Reports
Sorry if this may seem an obvious question; I've recently started with CRM 3.0 and have been staring at it for too long! I have created a custom field on the case form called TimeSpent. On my journey to Utopia (to send e-mail automatically to client with Case Desc, Time Spent, Time Remaining) my immediate basic requirement is just to have TimeSpent as one of the columns. So, until I get to Utopia, I generate report - Export to Excel - click to drill down - and I have most of columns except for my custom TimeSpent! Can anyone help me Regards Did you publish your customizations? Hi F...

adding values from 2 columns
I am trying to compose a personal cash flow chart where I can enter outgoings and income for every day of the month and see what the balance is on any day. I need to get a value in Col F which adds the value in Col C (daily income), subtracts the value in Col E (daily outgoings) and shows the balance. Also I may have to specify several rows if I have multiple outgoings on a particular day. Any ideas gratefully received. Gaby Sounds like you want a checkbook register. Something like this? A B C D E Start Bal $1,023.32 Date Item Expense Deposit Balance $10.12 $1,013.20 $23.3...

Conditional hiding / unhiding of rows ?
1.) I want to know whether there exists a possibility (other than with macros) to hide or unhide with the help of a condition, as in: if(C38="blab"; unhide(rows 38:52); keep them hidden) Is that possible or is the conditional formating just limited to nice colors of certain cells if they fulfill some kind of condition regarding another cell? 2.) I want to know how I could insert in my worksheets, left to column A, two columns 1 & 2 with a little "+" in order to display, when clicking on it, the hidden rows? Does anyone understand what I mean? I am not sure if I do...

Counting number of rows based on mutiple criteria
Does anyone know how to count the number of rows based on mutiple criteria on other columns? For example I want to count the number row that meet the criteria of "Yes" in Col 2 and "Yes" in Col 3. The result would be 2 in the example below. I tried different combinations of Vlookup, Countif and Sumif and could not come up with anything that worked. Col 1 Col2 Col3 A Yes No B Yes Yes C No No D No Yes E Yes Yes Your help is greatly appreciated! Hi, =SUMPRODUCT((B1:B10=&qu...

Products by column constants
A ver y simple question for your experts. I have a column of number that I want to multiply by a constant ( a price list tthat I need t increase by 5%). How to do? I can do one line at a time but how do I d the entire column -- Message posted from http://www.ExcelForum.com conn Enter 1.05 in a cell. Copy this. Select your data column and Paste Special>Multiply>OK>Esc. If you set up your formulas initially with a multiplier cell in the formula you can just change the value in the multiplier cell. e.g. Formula in C1 =(A1+B1)*D1 Change the value in D1 and the result in C1 will...

Printing a worksheet in two (or more) columns
Hi, I've got an extremely long spreadsheet table comprising two columns of data. I'd like to print these data in a more compact form - in the same way that the entries are organised in a telephone directory. Say on average my page is 50 rows high - my first two columns on page 1 would appear on the left hand side and be rows 1 through 50, row 51 would continue at the top of the page, but on the right-hand side. Row 101 would appear at the top left hand side of page 2, etc. I'd like to keep the orientation of the columns in portrait (I did find a printer-associ...

how can i relate between two columns in two sheets?
Question no too clear - you can use this space to give a detailed question. But here goes: On Sheet1 I can type formulas such as =Sheet2!A1 =SUM(Sheet2!A:A10) If I type = and then click on the cell A1 of a worksheet called My Yearly Totals, I will get the formula ='My Yearly Totals'!A1 Note that a sheet name having spaces get surrounded by single quotes. Does this answer your question? best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters REMEMBER: Microsoft is closing the newsgroups; We will all me...

How can I automatically remove duplicate data within a column?
Is there a way to automatically remove duplicated data within a column in Excel? For example, I have a column of over 10000 rows. The cell contents are call numbers, many of them are duplicate and I'd like to remove them without sorting and manually removing them. Chip Pearson has a means for doing this at http://www.cpearson.com/excel/deleting.htm If it is just a single column, though, you can use Data>Filter>Advanced Filter. Once there, select Copy to a new Location and Unique Records only. That will give you a list of unique values in your column - i.e., no duplicates...

Dup check 2 columns
Using Excel 2007, I have a two column list with over 100,000 rows. Example: Jane Doe U Jane Doe John Doe John Doe U John Doe Jack Doe Jack Doe I want to run a dup check to make sure there is only one Jane, Jack or John Doe, but the copy I want to stay is the one with a U behind it if there is one (wouldn't matter which row is kept for Jack). From what I've done so far, the dup check simply keeps whichever record is in the top row. Seems like it should be an easy fix, but I'm lost. Please help. Hi, If the rows you want to keep ar...

Unhide rows?
In Excel 2002, rows that were previously hidden will not "unhide." Rows that are hidden now will only unhide by using the undo command. Columns hide and unhide successfully, but rows do not. Protection is not on for either worksheet or workbook. Does anyone have an idea why this is happening and how to fix it? Nevermind. I forgot that I had a column with instructions to filter certain rows, the rows I couldn't unhide -- gee, what a coincidence. "Scot" <greatscotinla@hotmail.com> wrote in message news:9Ea4f.1079$fc7.1027@newsread3.news.pas.earthli...

Moving a row from one sheet to another wrongly leaves blank-row artifact.
Moving an entire row within a sheet in Excel XP is implemented correctly: Select the entire row, cut it, go to the target location, and Insert Copied Cells. The target row is inserted (pushing all rows below it down), and the source row is deleted (bringing all rows below it up, as it were). NOT so when the target is in another sheet in the same workbook. Excel inserts the target row correctly, but fails to delete the source row. The row still exists (only it's blanked out). You have to remeber to, tediously, return to the source and delete the blank row. Which means that if you intende...

creating a function for dividing two columns
I am trying to keep track of softball stats. I trying to make a column of batting averages. I have at bats in column b, hits in column c with the results in column d. I cant get the function right so it will display the correct result. use this =SUM(C1/B1) in cell D1. Format D1 as a number with 3 decimal places. Steve Hubbard "cflan" <cflan@discussions.microsoft.com> wrote in message news:212BF952-55B9-45DF-BC8C-E8B670FA6010@microsoft.com... > I am trying to keep track of softball stats. I trying to make a column of > batting averages. I have at bats in column b,...

first row
Hi group Im using excel as a telephone database (cannot get my head around access). Anyway with my first row which has name, contact number etc etc, I would like to always keep visible, so as when I scroll down the list of names I can always see this first row, is this possible? or do I have use access?? TIA -- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com S...

Find Value; Return Row number
Goodmorning everybody! I was wondering if someone could help me with the following problem: "I wanted to make a macro which would find the first value (counting from a1) that matches the value stated in C14 of "FORM". When it gives a match I want to return the row number to "Sheet2". I tested it with dates of which I was certain it had to find a match (as I entered them myself in "Sheet1") Still it returns: "Named Argument not Found" (Runtime error 448)" I hope someon sees my mistake(s). Thanks in Advance!! Sub TestDelete() l = Sheet...

combining 2 columns of data
hello my problem is i have a list of names (one column of first names, and one column of last names), my boss says we need to have the first and last names in the same column. Is there any way that the Data can be combined without having to retype all 5000 names?? You can write a formula in a new cell to combine cells. For instance if the first name is in A1 and the last name is in B1, you can write a formula in C1 like this: =A1&" "&B1 This joins the value of A1 with a space and the value of B1. If you need to you can arrange it to be last name comma first name: =B1&a...

How do I un-hide a column in excel
Gurus, One of my colleagues has hidden a column in Excel. Actually most columns are hidden. I can't seem to be able to unhide it. There is a vertical bold blue line right down the center of my spreadsheet. I want to get rid of it. -- Spin Click on the box between the column heading and the row heading (A1 or R1C1) to highlight the whole of the worksheet. Click on <Format><Column><Unhide> Regards. Bill Ridgeway Computer Solutions "Spin" <Spin@spin.com> wrote in message news:4hniecFerumU1@individual.net... > Gurus, > > One of my coll...

Select non-consecutive rows
Hi, Is there a way to select non-consecutive rows besides Ctrl + click. If I want to insert a blank row above about 40 rows, this can become quite tedious. -- Thanks! Dee Hi only possible with VBA in this case -- Regards Frank Kabel Frankfurt, Germany "dee" <dee@discussions.microsoft.com> schrieb im Newsbeitrag news:23B3F46E-C956-4FCC-9880-08B9A200B3A2@microsoft.com... > Hi, > > Is there a way to select non-consecutive rows besides Ctrl + click. If I > want to insert a blank row above about 40 rows, this can become quite tedious. > > > -- > Tha...

Delete rows with macro code
Hi, I would like to try and find a macro code that does the following: 1. Delete rows when cells in column C3:C2000 are less than (<) 50, and; 2. Delete rows when cells in column F3:F2000 are exactly equal to (=) "0" This 'exactly' is important as I have some blank cells in column F which are greater than 50 and I need them to remain within the sheet. If anyone can give me any assistance on this, I would be grateful. Many Thanks, Greg Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "C").End(xlUp).Row For i = iLastRow ...

No rows in dataset when reading in xml?
Hi, Ive had a bit of a look around for other people with this problem and cant find anything that solves it in my case, so I'm afraid im going to have to bother you all with a post of my own. Essentially, I am trying to read in an xml schema and then an xml document into a dataset using ReadXmlSchema and then ReadXml. When i do this, i end up with a table in the dataset (which i assume is created when i read the schema), but no rows representing my document. I read in a couple of places that validation failing against the schema would cause data from my xml file, so i validated my docum...

Excel (MacOS X) and Autofill text in columns.
Why does my Excel (MacOS X 10.3.5) no longer autofill text in columns? I have checked the prefs are set up to do so. I never used to have this problem in 9.2.2 and cannot see what I have done wrong. Nobdoy yet has fathomed this out. HELP! Captain Dud - > Why does my Excel (MacOS X 10.3.5) no longer autofill text in columns? I > have checked the prefs are set up to do so. I never used to have this > problem in 9.2.2 and cannot see what I have done wrong. Nobdoy yet has > fathomed this out. HELP! < If you don't receive relevant replies here in microsoft.public.excel....

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26863 You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

Thick column lines
Hello I want to create thick column lines but don't know how. Thick lines would enable me to separate the data from one column to next more clearly. Please help. Thank you. Regards hash Format / Cells / Borders / Select thick border and click on diagram to tell Excel where to apply it on the cell(s). -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- There's no 'I' in 'Team' ---------...

Is it possible to make all cells in a column have the same formula?
I'm working on a spreadsheet so I can keep track of my earnings and hours worked. The formula result in column F row x is always column D row x multiplied by column E row x. Is there a way to just type in the data and have the spreadssheet just finish it? As always, post your efforts for comments -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <shans91@hotmail.com> wrote in message news:b8152793-cee9-42fe-842e-54e7559d8fdf@s19g2000prg.googlegroups.com... > I'm working on a spreadsheet so I can keep track of my earnings and > hours worked. ...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...