On Thu, 29 Sep 2005 16:38:01 -0700, "Aussie CPA"
wrote:

Hi,
I'm hoping someone could please help me.

I have a spreadsheet setup which has on one sheet columns of data that
represent data for a particular month. The column heading is setup to advance
forward a month depending on the point in time during the year ( eg in August
2005 the first column heading is set at Sep-05. In Sep-05 the first column
heading will become Oct-05 and so on).

I want to populate each of the columns with data that looks at another
worksheet with information arranged in columns defined by a particular month.
The month headings in this sheet are fixed and do not change.

I can't simply put a sum formula in the first sheet though as the applicable
column the formula looks at will change depending on the month. (ie. The
applicable sum formula may be in column B one month but need to be in column
A the following month).

I realise I can simply move the formulas one column to the left each change
in month however I was hoping to have the sheet function automatically.

I have a hunch an array formula may help but I am not that skilled in them.

Hopefully my question makes sense. Can someone help please?


Thanks,

Adam Wood

You can put a formula on sheet 1, and don't need to use array
formulae. There are no doubt other solutions, but try this one.

Assuming your information is in cells C1:E10 on sheet2, and that row 1
contains the month name headings, give the range C1:E10 the name
"data", and C1:E1 the name "months" .

On sheet1 Assuming the column heading is in A6 and that A6 is in the
same format as the month headings on sheet2, enter the following in A7

=INDEX(data,ROW()-5,MATCH(A$6,months))

Then copy this down column A to A15.

You'll have to change the "-5" bit to match your layout. This is the
difference in this example, between the "6" of A6, and the row "1" of
the range C1:E1 "months" range on sheet2

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________