Reply to thread

Message: [QUOTE="GS, post: 7404104"] <FWIW> In my personal bookkeeping app I use 'FiscalPeriod' columns to the right of (Income/Expense) transaction details, one column for each month of the fiscal year. The row above the month labels containing their respective indexes (ie:Jan=1,Feb=2...). To capture transaction amounts into their respective month columns I use the following formula... =IF(OR(Date="",MONTH(Date)<>MonthIndex),"",IF(CurrencyType<>"",Amount*ExchRate,Amount)) ...which leaves empty cells blank for a cleaner look to the section (I hate looking at zeros when not needed). You could modify it for your needs by implementing the month indexes concept without the defined names as long as the correct col/row abs/rel refs are used. *Note* that all names have local scope (sheet-level) because they are duplicated on both the Income ledger sheet and the Expenses ledger sheet. 'Date' is a col-absolute/row-relative dynamic defined name range for entering the transaction date. 'MonthIndex' is a col-relative/row-absolute defined name that specifies the where the month indexes are stored. (This is hidden by matching font color to fill color) 'CurrencyType' is a col-absolute/row-relative defined name used for 'flagging' foreign currency amounts via a DV dropdown. To the right is ('ExchRate') where the foreign currency exchange rate is entered. 'Amount' is a col-absolute/row-relative defined name used for the transaction 'items' total (excluding taxes). (Transactions may use multiple records (each row is a 'transaction record') if the items are spread over more than one accounting 'Category' or 'Subcategory') For maintenance simplicity, the above formula is stored in a defined name ("PeriodAmount") so I only need to make changes in one place (per sheet where defined/used). In this case, the formula entry for all cells in the fiscal period section is... =PeriodAmount To use the formula without defined names... =IF(OR($B6="",MONTH($B6)<>Q$4),"",IF($H6<>"",$J6*$I6,$I6)) ...where the first transaction starts in row 6, month indexes are in row 4, and the fiscal period section starts in col "Q". You could copy it down and across OR preselect the entire area and use the keyboard combo 'Ctrl+Enter' to populate all selected cells. (I use a 'BlankRecord' [hidden row] for inserting more rows where/when needed, and so the 12 formulas are already in place) IMO, using defined names makes things easier to understand. (I just hate having to trace refs to see what/where they point to when it's so much easier to use self-describing names) -- Garry Free usenet access at [URL]http://www.eternal-september.org[/URL] ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion [/QUOTE]

Verification