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 :rolleyes: -- xlnovice ------------------------------------------------------------------------ xlnovice's Profile: http://www.excelforum.com/member.php...o&userid=16456 View this thread: http://www.excelforum.com/showthread...hreadid=278138 |
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 :rolleyes: 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 |
Hi
Here is an example of somewhat different design - I made it on fly, so you probalbly have to improve it a bit. Let's start with empty workbook. 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 A1:E1 enter headers 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) A3:D3 enter column headers 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 A5:D5 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:D##)=0,"",SUM(D5:D##)) 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 Now we have to return to sheet Payments 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). Return to AccountRep sheet. Into range A3:C3 enter column headings 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 :rolleyes: -- xlnovice ------------------------------------------------------------------------ xlnovice's Profile: http://www.excelforum.com/member.php...o&userid=16456 View this thread: http://www.excelforum.com/showthread...hreadid=278138 |
All times are GMT +1. The time now is 08:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com