non-traditional cell references

[Guest]

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.

 

[loudfish]

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