Remember Me?

#1
November 14th 04, 06:19 PM
 xlnovice Posts: n/a
date

I am creating a spreadsheet for a small business that has incoming cash
on a daily basis, and outgoing payments on a monthly basis, The
payments occur on the same date every month but what i want to be able
to do is create a formula that will automatically put in the payments
on a given day every month (each month being on a seperate sheet) I was
looking to create a daterange formula but connot seem to get it to work

=IF(TODAY()=(DAY(B2:I2)),TRUE,FALSE)

This is the formula i have used (B2:I2) being the date range ie

8/11/04 to 14/11/04

--
xlnovice
------------------------------------------------------------------------
xlnovice's Profile: http://www.excelforum.com/member.php...o&userid=16456

#2
November 14th 04, 09:09 PM
 Ron Rosenfeld Posts: n/a

On Sun, 14 Nov 2004 12:19:06 -0600, xlnovice
wrote:

I am creating a spreadsheet for a small business that has incoming cash
on a daily basis, and outgoing payments on a monthly basis, The
payments occur on the same date every month but what i want to be able
to do is create a formula that will automatically put in the payments
on a given day every month (each month being on a seperate sheet) I was
looking to create a daterange formula but connot seem to get it to work

=IF(TODAY()=(DAY(B2:I2)),TRUE,FALSE)

This is the formula i have used (B2:I2) being the date range ie

8/11/04 to 14/11/04

You need a somewhat different syntax, and also you need to enter an
*array-formula*

The array-formula:

=OR(DAY(TODAY())=DAY(B2:I2))

will return TRUE if the day of the month today is same as any day of the month
in the range B2:I2. Otherwise it will return FALSE.

To enter an array-formula, hold down <ctrl<shift while hitting <enter. XL
will place braces {...} around the formula.

--ron
#3
November 14th 04, 09:25 PM
 Arvi Laanemets Posts: n/a

Hi

Here is an example of somewhat different design - I made it on fly, so you
probalbly have to improve it a bit.

Create a sheet SetUp
A1="Year:"
Into Cell B1 enter the number of year, the book is responding to.
Define the cell B1 as named range Year
Year=SetUp!\$B\$1

D1="Accounts"
From D2 down enter account codes/names/numbers - i.e. some identifier about
who is related to payment. Accounts column must be contignous!
Define the named range Accounts
Accounts=OFFSET(SetUp!\$D\$2,,,COUNTIF(SetUp!\$D:\$D," <")-1,1)

Create a sheet Payments
Selected, Account, Day, Income, Outcome
Format Account column (reasonable number of rows) as data validation list
with source
=Accounts
Fill columns B:E with payment info - select account, into Day column enter
the payment day number, into Income and Outcome columns enter according
sums. You can have both income and outcome on same row in case they have
same payment day number, but it isn't restricted to enter them on separate
rows too.
To Selected column I'll return later!
Define the named range PaymentsTbl
PaymentsTbl=OFFSET(Payments!\$A\$2,,,COUNTIF(Payment s!\$B:\$B,"<")-1,5)
Define the named range PaymentsSelected
PaymentsSelected=OFFSET(Payments!\$A\$2,,,COUNTIF(Pa yments!\$B:\$B,"<")-1,1)
PS! To avoid possible problems in future, sort the payments table Ascenfing
by Day column - and keep it always so.

Create a sheet MonthlyRep
A1="Month:"
Format cell B1 as data validation list with source
'January,February,March,April,May,June,July,August ,September,October,Novembe
r,December'
Define named range MonthX
MonthX=MATCH(MonthlyRep!\$B\$1,{"January","February" ,"March","April","May","Ju
ne","July","August","September","October","Novembe r","December"},0)

Date, Account, Incoming, Outcoming
A4="TOTAL:"
A5=IF(OR(Payments!\$B2="",MIN(DATE(Year,MonthX,Paym ents!\$C2),DATE(Year;MonthX
+1,0))TODAY()),"",MIN(DATE(Year;MonthX,Payments!\$ C2),DATE(Year,MonthX+1,0))
)
B5=IF(OR(A5="",Payments!\$B2=""),"",Payments!\$B2)
C5=IF(OR(A5="",Payments!\$B2="",Payments!\$D2=0),"", Payments!\$D2)
D5=IF(OR(A5="",Payments!\$B2="",Payments!\$E2=0),"", Payments!\$E2)
Format cells A55 with appropriate formats and copy them down for some
reasonable number of rows. When a month in cell B1 is selected, all payments
in this month with payment date <= todays date are dispalyed
Into cell C4 enter the formula
=IF(SUM(C5:C##)=0,"",SUM(C5:C##))
and into D4
=IF(SUM(D5##)=0,"",SUM(D5##))
where ## is the number of last row with formula below.

Create a sheet AccountRep
A1="Account:"
Format cell B1 as data validation list with source
=Accounts
Define the named range AccountX
AccountX=AccountRep!\$B\$1

Into Payments!A2 enter the formula
=IF(\$B2=AccountX,COUNTIF(\$B\$2:\$B2,\$B2),"")
and copy it down at least for same number of rows you have filled the
payments table (but it'll be clever to have some additional rows ready).

Date, Incoming, Outcoming
A4="TOTAL:"
Select cell A5 and define named ranges
SelectedCnt=MAX(PaymentsSelected)
Date1=DATE(Year,INT((ROW()-5)/SelectedCnt)+1,VLOOKUP(MOD(ROW()-5,SelectedCnt
)+1,PaymentsTbl,3,0))
Date2=DATE(Year,INT((ROW()-5)/SelectedCnt)+2,0)
Into cell A5 enter the formula
=IF(MIN(Date1,Date2)<=TODAY(),MIN(Date1,Date2),"")
B5=IF(OR(\$A5="",VLOOKUP(MOD(ROW()-5,SelectedCnt)+1,PaymentsTbl,4,0)=0),"",VL
OOKUP(MOD(ROW()-5,SelectedCnt)+1,PaymentsTbl,4,0))
C5=IF(OR(\$A5="",VLOOKUP(MOD(ROW()-5,SelectedCnt)+1,PaymentsTbl,5,0)=0),"",VL
OOKUP(MOD(ROW()-5,SelectedCnt)+1,PaymentsTbl,5,0))
Format cells A5:C5, and copy down for at least
12*(MaxNumberOfPaymentsForAccount) rows.
Into cells B4;C4 enter formulas for totals, like on MonthlyRep sheet.

When an Account in cell B1 is selected, all payments from start of year
until current date (or until to end of year) for selected account are
displayed.

NB! When p.e. payment date 31 is entered into Payments table, then for
months with less than 31 days the last of month is displayed as payment date
in reports!

When next year begins, you copy the worbook under new name, change the year
on SetUp sheet, Edit Accounts and Payments tables on necessitate, and are
done with it.

This setup works when payments lasts full year. When this isn't a case, you
have to add at least 2 columns (StartDate, EndDate), on Payments sheet, and
have to modufy formulas which return dates on report sheets - so that Dates
validity is checked.

Arvi Laanemets

"xlnovice" wrote in message
...

I am creating a spreadsheet for a small business that has incoming cash
on a daily basis, and outgoing payments on a monthly basis, The
payments occur on the same date every month but what i want to be able
to do is create a formula that will automatically put in the payments
on a given day every month (each month being on a seperate sheet) I was
looking to create a daterange formula but connot seem to get it to work

=IF(TODAY()=(DAY(B2:I2)),TRUE,FALSE)

This is the formula i have used (B2:I2) being the date range ie

8/11/04 to 14/11/04

--
xlnovice
------------------------------------------------------------------------
xlnovice's Profile:

http://www.excelforum.com/member.php...o&userid=16456

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Jayjg Charts and Charting in Excel 2 January 22nd 05 03:00 PM PM New Users to Excel 3 January 21st 05 07:47 AM dlg1967 Excel Discussion (Misc queries) 4 January 19th 05 03:51 PM Rich New Users to Excel 2 December 9th 04 02:06 AM Jessica Excel Discussion (Misc queries) 4 December 2nd 04 02:54 AM

All times are GMT +1. The time now is 09:18 PM.