Array formulas

[mlshefrin]

i have a large spreadsheet for budgeting purposes. there are multipl
worksheets within the book that have statistics on anywhere betwee
10-30 general ledger accounts. these worksheets have a column for 200
Actual expenses, 2003 Budget, and another column for 2004 Budget fo
each account.

for example -

GL #1234.000 - Office expenses

2003 Actual 2003 Budget 2004 Budget

January ### ### ###
February ### ### ###
March ### ### ###
etc

i then have a worksheet that lists the entire budget but in rows rathe
than columns. it appears like this:

Jan Feb Mar Apr May
1234.000 Office Exp ## ## ## ## ##

how can i get the information in the columns from the detaile
worksheet easily over to the total budget information now listed i
rows?

i think i need to use array formulas and transpose but am havin
trouble making it work.

Thanks

 

[BrianB]

I would first have a (hidden ?) summary sheet in the same format as th
others to take the summary formulas, then link this to the officia
summary by using the =INDIRECT() worksheet function.

The trick here is to have a hidden row and/or column to take the sourc
column or row number and use this within a concatenated address
Something like this in column 1 of the final sheet :-

=INDIRECT("SummarySheet!A" &A1)

here, row 1 columns contain the source column numbers, so if number 1
is in cell A1 it will look up cell SummarySheet!A10