Bills reminder - missing & required formula(s)?

Hi all,

I stumbled across this webpage (http://www.get-digital-help.com/2011...nder-in-excel/) which has just what I need minus a few options I'm hoping you can help me with...

The Bills Reminder template uses data from one worksheet (called Bills!) to display in another (called Reminder!) as a summary of those bills that are due within the 'dates' entered on the Reminder worksheet. See the 2 images of these 2 worksheets.

Using the downloaded template I am able to recreate the tables (in my existing workbook) just like the template, & edit the formulas to reflect the name of my source worksheet (named 'FutureDebits!').

- No Bill, Due Date or Amount is displayed, ie " " is displayed only. Is there meant to be anything in COLUMN $A$1:$L$1?
- In each of the formulas it refers to ROW(A/B/C1), should anything be in ROW(A)?


The formulas below show the reference:

Array formula in cell B1:
=IFERROR(INDEX(Bills!$B$3:$B$15, SMALL(IF((MONTH($C$2)=(MONTH(Bills!$C$3:$C$15)+(COLUMN($A$1:$L$1)-1)*Bills!$E$3:$E$15))*((DAY($C$2)<$C$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Bills!$C$3:$C$15)>=$C$4)*(DAY(Bills!$C$3:$C$15)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)), ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1, ""), ROW(A1)), 1), "")

Array formula in cell C1:
=IFERROR(DAY(INDEX(Bills!$C$3:$C$15, SMALL(IF((MONTH($C$2)=(MONTH(Bills!$C$3:$C$15)+(COLUMN($A$1:$L$1)-1)*Bills!$E$3:$E$15))*((DAY($C$2)<$C$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Bills!$C$3:$C$15)>=$C$4)*(DAY(Bills!$C$3:$C$15)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)), ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1, ""), ROW(B1)), 1)), "")

Array formula in cell D1:
=IFERROR(INDEX(Bills!$D$3:$D$15, SMALL(IF((MONTH($C$2)=(MONTH(Bills!$C$3:$C$15)+(COLUMN($A$1:$L$1)-1)*Bills!$E$3:$E$15))*((DAY($C$2)<$C$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Bills!$C$3:$C$15)>=$C$4)*(DAY(Bills!$C$3:$C$15)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)), ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1, ""), ROW(C1)), 1), "")


- Also... Is there a way to change/add to:
a) the formula to show bills that have a 'Due Date' but do not have a 'Frequency' set (ie. single occurring bill)?
b) filter the displayed bills on the Bills worksheet... instead of ranging between the 2 'Dates' fields & using todays date in Reminder! worksheet, make the range set by two date fields/cells, called 'Start date' & 'End date'?

- Are the headings "Array formula in cell B1/C1/D1" meant to be "Array formula in cell B7/C7/D7"?

Thanks in advance for your awesomeness..

HI, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

- No Bill, Due Date or Amount is displayed, ie " " is displayed only. Is there meant to be anything in COLUMN $A$1:$L$1?
NO
it returns 1-12 = months 1 to 12

- In each of the formulas it refers to ROW(A/B/C1), should anything be in ROW(A)?
no,
its to increment the small (k)


- Also... Is there a way to change/add to:
a) the formula to show bills that have a 'Due Date' but do not have a 'Frequency' set (ie. single occurring bill)?
yes but wouldnt it be easier for you just to put 1 frequency for everything?


b) filter the displayed bills on the Bills worksheet... instead of ranging between the 2 'Dates' fields & using todays date in Reminder! worksheet, make the range set by two date fields/cells, called 'Start date' & 'End date'?
not sure what you mean by this


- Are the headings "Array formula in cell B1/C1/D1" meant to be "Array formula in cell B7/C7/D7"?
yes believe so

you had a REF in your small(K)
which made your formula spit out blanks
i looked at the formula closer

ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1
i didnt understand why this was needed the min(row) bit
so i took it out and the formula continues to work with just
ROW(FutureDebits!$P$2:$P$204)-1

the engine of the formula is then just basically the IF Logic
(MONTH($C$2)=(MONTH(FutureDebits!$O$2:$O$204)+(COLUMN($A$1:$L$1)-1)*FutureDebits!$P$2:$P$204))*((DAY($C$2)<$C$4)*(DAY(FutureDebits!$O$2:$O$204)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(FutureDebits!$O$2:$O$204)>=$C$4)*(DAY(FutureDebits!$O$2:$O$204)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(FutureDebits!$O$2:$O$204)>=$D$4))

this formula is rather difficult to maintain little alone change to fit within dates

may i suggest a VBA solution instead?
essentially have your table (with additional field for paid in full)

create a new table with data in table PLUS every line of frequency (within finite amount of years 1-3?, maybe be rather large if you go over) minus the paid in full items

from there you would be able to use advanced filter to filter on date?

see attached my suggestion of vba

takes your power query table
fills in a sheet called list with full list including frequency
frequency is how often it repeats...like you want it
1 = 1 month
2 = 2 month
..
36= 36 months
etc

0 will be picked up only once


from list you can then create new sheet called filtered using advanced filter (filter macro)


issues i ran into
the macro for advanced filter uses American date format
mm-dd-yyyy instead of dd-mm-yyyy

if you want to focus on transaction type
you can manually use advanced filter instead of macros

maybe shift the data to columns to the side and have your filtered list in the same sheet
** you can copy to range for advanced filter in different sheet manually...it has to be done via VBA

where do you want reminder data coming from?
filtered debits or just debits sheet?

Re: warranty
formula should work as it is single line no repeats?

maybe Filtered! is not needed now, thinking about it... the only reason Debits! was filtered was to cater for the formula I was originally using (ie. to show only recurring debits), if the vba doesnt need it then it'll be one less thing to do.. So yeah go off the Debits! sheet.

Re:warranty that's right there's no repeats.

ok filtered wont be needed by the looks of things
futuredebits will be converted "LIST" like in my example in POST #8

i may be slow to respond as it is budget re-forecast season

warranty section answered

use formula for it because...only single occurrence
makes it easier to manage

i tried to use EDATE instead of date(year,etc etc) but i couldnt get it to work...

Formula:

Please Login or Register  to view this content.

Q:

What significance does today() have in your reminder? because your just setting the from and to dates anyway?

ok two step process

Run bills to extract data from Debits sheet to Expandedlist (change as required)

This will setup expandedlist with
Debits data "expanded" to have recurring transactions repeat and excluding items which are "finalised"

on expanded list there is a command button for refresh
if no dates are entered msgbox appears stating so and nothing happens

changed the autofilter code because dates in VBA are finicky


will always try to use US format which is mm-dd-yyyy

i think the term frequency is being misinterpreted
interval and frequency they are two distinctly different things
frequency = how many times you want to repeat
interval (which i dont have any coding for) is how long between repeats

can be done but the logic of the loop needs to be rebuilt

yes you would need the interval field would need to be added if you wish for it to occur
otherwise it would be what it has now where the frequency is the interval limit

however....i do see your expanded list growing substantially if for instance you have something that occurs monthly and you have it set to repeat for next 10 years
10 x 12 = 120 lines for single item

in this regard...today() may have relevant again....to "purge" anything less than today

have a think about it....i will be quite again for awhile.....next iteration of budget has come around again

didn't work on purging yet, may not be required? if your macro becomes too long to update (as the filter works without having to update data every single time)

added interval column and changed logic of the loop to calculate intervals
ie birthday 12 months interval = frequency x interval
in this example 12 x 12
meaning 12 times appearing every 12 months

council however is 12 x 3
12 frequency
3 months interval
12 lines with 3 months in between
etc
play around with it to see if it all makes sense

added filter button and clear button which works on the due date Start and end on expandedlist sheet

things to be mindful of
must have start date in order to filter...cannot just put end

**added subtotal in expandedlist

can make "finalised" not case sensitive but i think you should be controlling it via DV anyway in case spelling is wrong

a) is there a way in VBA to include/exclude certain columns? Can the code {Range(Cells(1, 1), Cells(1, 18))} be modified to suit or does it have to be a range? (eg. I want to add 'Notes' & 'Payment Type', & exclude columns like Qty, Unit Price, Freight, & a couple others)
yes, probably several ways of doing it
come back with exactly what you want and ill see what i can conjure up

b) With a 'Recalc' button on ExpandedList sheet, when click it runs the Bills macro, when it is finished the active sheet becomes Debits. Can it be made so the active sheet remains/is ExpandedList sheet?

small fix, just put a worksheet.select right at bottom of code

c) I dont know if this is possible... as the xlsm is opened can the ExpandedList sheet be updated automatically by running the Bills macro?, & by default show the ExpandedList sheet (like above)?
(the start & end dates would be blank, could i use something like ws1.Range("B1").FormulaR1C1 = "=Today()-30" & ws1.Range("B2").FormulaR1C1 = "=Today()+90" ?
yes using workbook_open> call macro and yes ...add extra formula like you have noted
do you want the set dates all the time or just when it opens?

come back with part a and post your new file with the recalc in there
ill do the next revision on the 3 tweaks
may be a little time between though....next budget file is due....

so the minor changes have been added

workbook open will run bills and filter macro
i will put a 'infront on future updates as i really dont want it to run everytime i open the file when fixing code


added two dates to bills macro so it will appear every time you update by default

after bills is run it will return back to expandedlist sheet

inserted application screen updating so the screen doesn't "flicker" as much

now onto your other issues

your mock basically takes out
Qty UnitPrice Freight Discount
however in your debits sheet...total is a formula still
im guessing you want to paste value instead of formula?


sumif wouldnt work...maybe sumifs could work but it wouldnt update based on filter...it would update based on date
so if you change filter without changing dates it wont update

added sumproduct by subtotal formula which gets around that problem

normally you can bring across everything and hide or bring across in segments
but you want it in different order to original data?

TransactionType Category DOP-EntryDate POP POP-Location Recipient Sub-Recipient Sub-RecipientInfo TotalPrice Payment Type Notes Odometer (Kms Due) DueDate

to confirm are you really sure these are the columns you want in this particular order
if it a lot of work re-coding if you want changes afterwards

hmmm yes supposedly you can use variables....but what this will turn out to be
would be instead of copying ranges you can copying single cells
many many single cells
i worry about performance

order is my biggest concern at the moment
if your order is the same as your debits sheet then i can possible use hiding and copy visible cells only approach

re:performance i see your point, who wants to be waiting for it to calculate.

I checked/compared Debits! to ExpandedList!, the order of columns are the same (ie. they are in ascending order {column A to Z}) except for 'DueDate' which is on the end in ExpandedList! sheet, it doesn't have to be. If it solves the problem I reckon place/move/insert it after Total Price (column N), then use hide/visible approach?

ExpandedList! sheet:

TransType B
Category C
Entry date D
POP E
POP Loc F
Recipient G
Sub-Recip H
Sub-RecInfo I
TotalPrice N
PaymType R
Notes U
Odomo. W

DueDate O

* after having a closer look at the ExpandedList! sheet, I could be wrong but it looks like the code needs to know where the DueDate & TotalPrice columns are to filter & sum, so perhaps using a variable probably wouldn't have worked anyway.

so took your suggestion...sort of
used data Column D as what columns to exclude

Please Login or Register  to view this content.

essentially takes those columns and hides them

problem i foresee with this variable column is.....the code for due date is fixed....i guess can put logic to look up this...maybe

i put a table in there...make it look nicer
havent put the conditional format for credit yet...shouldnt be hard to do later

the problem with having variable headers if you would need the logic in the body of the macro to change the formulas for subtotal

alternatively you can make it copy every applicable row and clean it up unwanted columns afterwards

copying every row and cleaning up unwanted columns would be easier and probably cleaner...
will circumvent anything breaking the due date logic
would also probably make the subtotal formula's easier to manage

i am unsure if it would be more efficient though
i have no idea on how big your eventual file will be

Please Login or Register  to view this content.

up to the end of the For loop....everything works fine
then after that...it kind of goes pear shape if you touch the headers in Data sheet
also bear in mind the filter macro runs off what column Due date is in
along with all the formulas etc

Please Login or Register  to view this content.

just call this macro at end of bills
it will highlight credits red

Please Login or Register  to view this content.

i combined 3 into one as i am unsure why you would need to do each one individually
however...the issue remains if your Columns side your formula may not work

Sorry I musnt have been clear enuf, what i was wanting was each code/formula to insert in to the macro called 'Bills' to suit the updated version - for the subtotal, credit & debit located on the ExpandedList! sheet (I need this now because columns have been moved around, messing up your previously coded references, see example below), & if possible to find out how to write it like you have to be dynamic (ie. so the formula can calculate the whole range of displayed rows) - for my own future reference

for example:

previous code for Debit:
"=SUMPRODUCT(--(R5C1:R" & k & "C1=R[-1]C[0]),SUBTOTAL(9,OFFSET(R5C9:R" & k & "C9,ROW(R5C9:R" & k & "C9)-MIN(ROW(R5C9:R" & k & "C9)),0,1)))"

- oh, i was doing the Recorded Macros thru excel individually for clarity for myself (to recognise which formula was which).

Please Login or Register  to view this content.

This code now looks at where TOTAL PRICE header is and adjusts the formula accordingly

likewise i have changed the filter macro to run and look for DUE DATE header

of course...if either of these headers are omitted the whole thing will not work properly...however considering these are probably the two month important fields in the whole sheet...i doubt they will be omitted

phew~
that was a long one i must admit
i seldom make it page 4 of a thread and i certainly learnt a few new tricks or two

great that i got you back on the wagon on teaching yourself VBA.....that's how i learnt most of what i know
you can see it in my code...its mainly hack and slash but it does the job most of the time

these forums were a great enabler in order for me to learn

once you understand the basic fundamentals....the rest is mainly syntax and maths problems solving (and somewhat knowing limitations of excel itself) and there are many resources online or otherwise to help with that