Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
non-traditional cell references
I have quarterly data, which I want to divide into monthly colums. For example:
Sheet 1 - Cell A1 has data for Q1 Cell B1 has data for Q2...etc In sheet 2 - Cell A1 has data for Q1 Month 1 Cell B1 has data for Q1 Month 2 Cell C1 has data for Q1 Month 3 Cell D1 has total for Q1 Cell A1 has data for Q2 Month 1 Cell A1 has data for Q2 Month 2...etc The column reference needs to increase by one every fourth column, but I can't figure out a way to do it. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
non-traditional cell references
On 19 Mar, 22:17, Jim wrote:
I have quarterly data, which I want to divide into monthly colums. For example: Sheet 1 - Cell A1 has data for Q1 Cell B1 has data for Q2...etc In sheet 2 - Cell A1 has data for Q1 Month 1 Cell B1 has data for Q1 Month 2 Cell C1 has data for Q1 Month 3 Cell D1 has total for Q1 Cell A1 has data for Q2 Month 1 Cell A1 has data for Q2 Month 2...etc The column reference needs to increase by one every fourth column, but I can't figure out a way to do it. Not sure if you want Sheet1 to pickup from Sheet2, or the other way around. However, the principle is the same: In order to be able to copy the same formula across your columns in Sheet1, you could include a reference to the column number in an additional row (usually at the top of the columns: A B C D E 1 Column Index 4 8 12 16 2 Column Header (visible) Q1 Q2 Q3 Q4 3 Data =INDIRECT(ADDRESS(1,B $2,,,"Sheet2") When you copy the formula from B3 to C3, the index number increments from 4 to 8, and it should pickup the right cell in Sheet2. HTH Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 traditional chinese to simplified chinese | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Changing Cell References To a Different Worksheet in the Same Cell | Excel Worksheet Functions | |||
how can i change the traditional chinese font to simplified chine. | Excel Discussion (Misc queries) | |||
Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) |