Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incrementing cell references slower than by 1
I've transposed the information from one worksheet to a second worksheet in
order to see the information linearly. The cells are referenced directly, but in the original document, everything is in the same line. Basically, I want all the cells in rows 2-8 of the new worksheet to be referenced to row 2 of the old worksheet; rows 9-15 of the new to be referenced to row 3 of the old, etc. When I copy/paste, instead of using row 3 (of the old), it uses row 9. Same goes when I try to use row 4, it uses row 16. Is there any way to preserve that? If more clarification is needed, I can do so, or even provide a link to the file of what I'm trying to do. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incrementing cell references slower than by 1
Hi,
What does your data look like on sheet1 and what do you want it to look like on sheet2. Normally we use 1. Paste Special, Transpose, 2. The TRANSPOSE function, 3. The OFFSET function, the INDIRECT, or INDEX functions. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Rich" wrote: I've transposed the information from one worksheet to a second worksheet in order to see the information linearly. The cells are referenced directly, but in the original document, everything is in the same line. Basically, I want all the cells in rows 2-8 of the new worksheet to be referenced to row 2 of the old worksheet; rows 9-15 of the new to be referenced to row 3 of the old, etc. When I copy/paste, instead of using row 3 (of the old), it uses row 9. Same goes when I try to use row 4, it uses row 16. Is there any way to preserve that? If more clarification is needed, I can do so, or even provide a link to the file of what I'm trying to do. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incrementing cell references slower than by 1
Rich,
There are more than a few ways to do this: here's one, using formulas. In cell A2:A8, enter 1 in each cell. Then in cell A9, enter =A2+1 and copy down until you get a complete set of numbers corresponding to the rows of your original data (say you have 10 rows (rows 3 to 12) so copy down for 72 rows....) Then, in B1:??1, enter the numbers 1 through however many columns you want the old values to be wrapped into - let's say 8 columns, so enter 1 through 8 in B1:I1. Then in cell B2, enter the formula: =INDEX(Sheet1!$3:$100,$A2,(COUNTIF($A$2:$A2,$A2)-1)*MAX($B$1:$F$1)+B$1) and copy over and down to match your new table. Increase or decrease the 100 of Sheet1!$3:$100 as needed, and change the sheetname as well. Personally, I would then copy the entire table and paste values, and finish by deleting column A and row 1. HTH, Bernie MS Excel MVP "Rich" wrote in message ... I've transposed the information from one worksheet to a second worksheet in order to see the information linearly. The cells are referenced directly, but in the original document, everything is in the same line. Basically, I want all the cells in rows 2-8 of the new worksheet to be referenced to row 2 of the old worksheet; rows 9-15 of the new to be referenced to row 3 of the old, etc. When I copy/paste, instead of using row 3 (of the old), it uses row 9. Same goes when I try to use row 4, it uses row 16. Is there any way to preserve that? If more clarification is needed, I can do so, or even provide a link to the file of what I'm trying to do. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
macros making files slower and slower | Excel Discussion (Misc queries) | |||
Incrementing cell reference | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
incrementing mm/yy based on value in other cell | Excel Worksheet Functions |