Hello, This is my basic formula (to follow). I want to be able to copy and paste it in subsequent rows in the column (rows go from B to AJ), only changing the letter and not the number of the row. Does that make sense? Help? Thank you.

='EXPS - January'!D34+Febuary!D34+March!D34+April!D34+May!D 34+June!D34+July!D34+August!D34+September!D34+Octo ber!D34+November!D34+December!D34

So the next cell in the column should change all the D34's to E34's and so on.

Thanks in advance.

Hello, This is my basic formula (to follow). I want to be able to
copy and paste it in subsequent rows in the column (rows go from B to
AJ), only changing the letter and not the number of the row. Does
that make sense? Help? Thank you.

='EXPS -
January'!D34+Febuary!D34+March!D34+April!D34+May!D 34+June!D34+July!D34+August!D34+September!D34+Octo ber!D34+November!D34+December!D34

So the next cell in the column should change all the D34's to E34's
and so on.

Thanks in advance.

This will be orders of magnitude easier to manage/maintain if you gave
the source cells local scope defined names. For example, for the
following fields...

FreightIn | FreightOut | Duties | Taxes

...on a sheet named "Exps", the consolidation formula could be...

=SUM(Exps!FreightIn,Exps!FreightOut,Exps!Duties,Ex ps!Taxes)

Other ways:
In my Invoicing:SimpleBookkeeping addin I have a Summary sheet that
lists a Chart of Accounts in rows, and months in columns so it has the
layout of a P&L statement (Statement of Income and Expense) which I
submit to my accountant for doing my taxes. (This sheet is designed to
conform with the Revenue Agency eFile forms)

The distribution column indexes (months) on the Income/Expense sheets
used to align with the month columns (Version1) on Summary, to collect
values in the totals row using the Column() function. Now I use a
hidden row to store indexes on Summary, and use this as an offset to
the fixed column between the transaction input area and the month
columns. You could go either way, but I found using the hidden indexes
was easier maintenance. Here's a sample formula for the Expenses
columns per account row...

In the Jan-Dec cols:
=PeriodExpense

...where 'PeriodExpense' is this defined name formula...


=SUMIF(Expenses!Category,Summary!Category,OFFSET(E xpenses!EndOfInput,0,Summary!MonthIndex)

...where the name "Category" has local scope on all 3 sheets. Similarly,
the Income section on Summary has this formula...

=PeriodIncome

...which is defined as...


=SUMIF(Income!Category,Summary!Category,OFFSET(Inc ome!EndOfInput,0,Summary!MonthIndex)

Why I find this easier maintenance is because editing the defined name
formula auto-updates in the cells using it.

The Income/Expense sheets use this formula in their Jan-Dec cols...

=PeriodAmount

...where this is defined as...

On Income:

=IF(Income!Date="","",IF(MONTH(Income!Date)<Incom e!MonthIndex,"",IF(OR(Income!Amount=0,Income!Amoun t=""),"",IF(Income!CurrencyType<"",Income!Amount* Income!ExchRate,Income!Amount))))

On Expenses:
=IF(Expenses!Date="","",IF(MONTH(Expenses!Date)<E xpenses!MonthIndex,"",IF(OR(Expenses!Amount=0,Expe nses!Amount=""),"",IF(Expenses!CurrencyType<"",Ex penses!Amount*Expenses!ExchRate,Expenses!Amount))) )

...so each month column only collects transaction amounts for their
respective month. Otherwise the cell is left blank so I only see
transaction amounts.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Use the Absolute Reference. Insert a $ sign before the number 34.

Thank you both for taking the time to reply. I will try these.

Many thanks