Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series Question?
I've been trying to figure this one out and have only come up with
workaround, rather than direct solution: Suppose in Sheet1 I have the following: A1=First B1=Middle C1=Last A2=George B2=W C2=Bush A3=John B3=Fitzgerald C3=Kennedy ... and so on and so forth, where data is entered across 3 columns fo however many rows. Assuming I have too many rows to manually input each cell number, ho do I get all the data to list in the SAME ROW on a new Sheet2. I other words, I am looking for a series or other automatic way to get on Sheet2, the following: a1=First a2=Middle a3=Last a4=George a5=W a6=Bush a7=John a8=Fitzgerald a9=Kennedy ... and so on and so forth. I could simply copy each column from Sheet1 and past into the sam column in Sheet2, but if I want to maintain the order, I have to the start adding and copying extra columns and what not. This secondar method works, but I figured there must be some direct method. If, in column A of Sheet2, I enter in the first 9 cells from which t take data and then try to make it a series and automatically fill i the remainder of the column, it does not work. The links don't follo the pattern. However, I've noted that if I don't make it a formula (i.e., enter "a1" rather than "=a1"), Excel can properly figure out th pattern. So I'm at a loss. I'm by no means an Excel expert, so hopefully thi isn't a really dumb question. Thank -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series Question?
On Sheet2 in Cell A1 put the following formula
=OFFSET(Sheet1!$A$1,TRUNC((ROW()-1)/3),MOD(ROW()-1,3)) then drag fill it down the column. -- Regards, Tom Ogilvy D Love wrote in message ... I've been trying to figure this one out and have only come up with a workaround, rather than direct solution: Suppose in Sheet1 I have the following: A1=First B1=Middle C1=Last A2=George B2=W C2=Bush A3=John B3=Fitzgerald C3=Kennedy .. and so on and so forth, where data is entered across 3 columns for however many rows. Assuming I have too many rows to manually input each cell number, how do I get all the data to list in the SAME ROW on a new Sheet2. In other words, I am looking for a series or other automatic way to get, on Sheet2, the following: a1=First a2=Middle a3=Last a4=George a5=W a6=Bush a7=John a8=Fitzgerald a9=Kennedy .. and so on and so forth. I could simply copy each column from Sheet1 and past into the same column in Sheet2, but if I want to maintain the order, I have to then start adding and copying extra columns and what not. This secondary method works, but I figured there must be some direct method. If, in column A of Sheet2, I enter in the first 9 cells from which to take data and then try to make it a series and automatically fill in the remainder of the column, it does not work. The links don't follow the pattern. However, I've noted that if I don't make it a formula, (i.e., enter "a1" rather than "=a1"), Excel can properly figure out the pattern. So I'm at a loss. I'm by no means an Excel expert, so hopefully this isn't a really dumb question. Thanks --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series Question?
Excellent! Thanks!
Generally, where can I learn more about toying with this formula? noticed if I paste it in a cell other than A1 of Sheet2, the result differ somewhat. I'd like to see how the different parts work so I ca tinker as needed -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Series Question?
put it in a cell
with the cell selected, go to the formula bar, select subsets of the formuls and hit F9 to evaluate that subsection (the subsection must be a section that can be evaluated). Then hit the escape key to "un-evaluate" that portion - if you hit enter with it still evaluated, the formula will have that section replaced with the evaluated result. This should you allow you to examine it. also, You can always put the major parts of it in row 1 and drag them down to see how they are working. -- Regards, Tom Ogilvy D Love wrote in message ... Excellent! Thanks! Generally, where can I learn more about toying with this formula? I noticed if I paste it in a cell other than A1 of Sheet2, the results differ somewhat. I'd like to see how the different parts work so I can tinker as needed. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Series Question | Charts and Charting in Excel | |||
Data Series question | Charts and Charting in Excel | |||
Data Series Question | Charts and Charting in Excel | |||
Another Series polulate question | Excel Worksheet Functions | |||
Pivotchart Multiple series question | Charts and Charting in Excel |