Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default Need formula that will only increase letter

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Need formula that will only increase letter

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
  #3   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by greencounter View Post
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.
Use the Absolute Reference. Insert a $ sign before the number 34.
__________________
Asobi Wa Owari Da
  #4   Report Post  
Junior Member
 
Posts: 3
Default

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

Many thanks
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need formula to increase Alpha character to next letter pwinters Excel Discussion (Misc queries) 5 April 2nd 23 07:07 PM
Increase by % Formula Trying Hard New Users to Excel 6 July 19th 08 12:33 AM
Number in cell increase with increase in font size. Value increases with increase in font.[_2_] Excel Discussion (Misc queries) 2 August 9th 07 01:58 PM
Fill Down In Rows But Want to Increase By Letter Karl Excel Discussion (Misc queries) 3 December 18th 06 08:01 AM
Increase letter by one Lee Cain Excel Programming 6 August 19th 03 10:39 PM


All times are GMT +1. The time now is 03:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"