Novice needs help could not find by searching

[Brodi]

Hello all,
I just signed up for yor forum to learn more about Excel. I have the latest version of MS Office.

I am horrible with formulas, don't understand them, can't figure them out when laid out in front of me (but if you need a welder, I'm your guy).

So here we go:

New spreadsheet

Sheet 1
Check register

I have the makings of a regluar check register with auto math functions similar to the many on the web.

Sheet 2
Bill pay sheet

I have my bills listed with their amounts paid and all that similar to below:

[Date due]....[Date paid] [Description] [Amount] [Paid Y/N]...[Conformation #]

10/10/16........10/15/16....Internet.......$40.05..........Y............12345678
10/13/16........10/15/16....1999 Ford.....$300............N
And so on.....
I have an identical sheet for each month of the year

Here is my question:
How do I Enter the information into sheet 2 (in any order) and have that information automatically enter on the next available line in the check register (and do the math). This could be triggered by checking a box or placing a Y in the PAID column. I do not get my bills in the order they are de so I may want to pay a bill at the bottom of the list before one at the top oif the list.

I want to pay the bills for January on sheet 2 and automatically have the register add the info and do the math without me ever changing to the page.

I will be able to populate a cell in sheet 2 with my CURRENT balance as I pay bills so I know what is available.

I really hope this makes sense to those here. Hopefully I am not asking for something that cannot be done. And please, do not spend your entire weekend trying to solve my problem, it is MY problem.

It is difficult to describe as it is in my head but I think I got the point across.

I have not created sheet 2 yet as I don't want to have to change it later so I can do whatever I need to make it work (Except make it work myself).

I hope this is simple for those here and someone can explain it in a way that I will understand it. I am computer challenged and was hoping for a cut and paste answer somewhere on the web or a pre-made spreadsheet that did what I want but could not find either so now need to try to understand it in an explained version if anyone here has the answer I am searching for.

If this has been answered before and I did not find it in search, I appologize, please refer me to the thread.

Thanks to all for sharing their knowledge with those that do not have it.

Brodi

 

[AlanY]

what are the info required in Sheet1?

if just the list of bills paid or date due with a range of dates etc you can just put a filter row on Sheet2, that will rid of the 'Check register' sheet and NO formula needed.

 

[Brodi]

AlanY,

Thanks for replying, I appreciate it.


Sheet 1 would look like this:

	Check Number	Date	Description of Transaction	Debit (-)	Credit (+)	Balance
		12/20/16	Previous balance		$435.99	$435.99
	1033	12/21/16	Groceries	$123.78		$312.21
		12/22/16	Deposit, sweepstakes winnings		$10,000.00	$10,312.21
	1034	1/3/17	Dry cleaner	$10.75		$10,301.46

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

The information would need to populate from sheet 2. I know I can make a cell here equal to one on that sheet but would like it automatically done on the NEXT available line.

Hopefully this make sense. I want to fill out sheet 2 and not touch sheet 1.

Appreciate your looking at this.

Brodi

 

[AlanY]

i've a mockup file made up, have a look to see if they meet your requirements.

https://drive.google.com/open?id=0B1h-Y2fdTl08ZUpPM2xTeHkxdUU

by changing the paid status (Y/N) on Sheet2 Column E, Sheet 1 should update automatically.
you can copy the formula in Sheet1, Cols A to G (shaded) down as the list grown.

note that most of the formula are array formula that required to confirmed with shift-control-enter (all 3 keys) together.
if you just copy them down it should be OK.

 

[AlanY]

better amned Sheet2, F3 to =IF(D3="",IF(E3="","",F2+E3),F2+E3-D3) and copy down.

to cover the single credit, e.g. Deposit, sweepstakes winnings $10,000.00

 

[AlanY]

updated

https://drive.google.com/open?id=0B1h-Y2fdTl08c1RQcnJwRDhKcmM

note that when you make a credit entry in Sheet1, delete the "Y" in the corresponding column G

 

[My Aswer Is This]

I would like for you to explain what the sheet names are.

You said:
Sheet 2
Bill pay sheet

Then you said:

and have that information automatically enter on the next available line in the check register

What is the name of the sheet you refer to now as the "check" register

When giving us sheet names please show them like this:

Sheet named "Register"

Put the sheet name in quotes so we know the exact sheet name.

Then you said:

This could be triggered by checking a box or placing a Y in the PAID column

What column is the PAID column


And what do you mean when you say "then do the math"

 

[My Aswer Is This]

Alan. It would be nice to see your script here and not just in a link. Can you not enter your script here so we can all see it without having to click on some unknown link? Clicking on links can sometimes be dangerous.

updated

https://drive.google.com/open?id=0B1h-Y2fdTl08c1RQcnJwRDhKcmM

note that when you make a credit entry in Sheet1, delete the "Y" in the corresponding column G

 

[Brodi]

AlanY,

Thank you so much for your work. It does what I wanted. Now I just need to input my info and "TRY" to rearrange it just a bit to look like I want. Hopefully I can do that without screwing it up. Yes, I am making a backup copy.

I do have a question:
If I call Sheet2 January, can I copy it to sheet 3 and have that work as well without screwing things up? Will I need to make and adjustments? I am assuming that if I can make my minor formatting changes that I can copy sheet 2 over and over for each month.
Does this sound correct?

Thank you,

Brodi

 

[AlanY]

Alan. It would be nice to see your script here and not just in a link. Can you not enter your script here so we can all see it without having to click on some unknown link? Clicking on links can sometimes be dangerous.

apology, it's just getting quite big that's all

Excel 2012
	A	B	C	D	E	F
1	[Date due]	[Date paid]	[Description]	[Amount]	[Paid Y/N]	Check Number
2	23/12/2016	14/01/2017	Internet	$40.05	Y	2061
3	23/12/2016	06/01/2017	1999 Ford	$300.00	N	2277
4	01/02/2017	01/03/2017	Internet	$201.00	N	2306
5	29/01/2017	27/02/2017	Internet	$137.00	Y	2365
6	31/01/2017	01/03/2017	Internet	$103.00	N	2300
7	28/12/2016	25/01/2017	1999 Ford	$250.00	N	2149
8	27/12/2016	20/01/2017	2000 Ford	$267.00	N	2297
9	30/12/2016	24/01/2017	1999 Ford	$138.00	Y	2063
10	12/01/2017	08/02/2017	Internet	$251.00	Y	2132
11	10/01/2017	26/01/2017	Internet	$278.00	Y	2312
12	01/01/2017	19/01/2017	Internet	$300.00	N	2417
13	24/12/2016	16/01/2017	Internet	$158.00	N	2195
14	02/02/2017	16/02/2017	Internet	$212.00	N	2313
15	20/01/2017	19/02/2017	Internet	$77.00	N	2268
16	03/01/2017	30/01/2017	Internet	$203.00	N	2134
17	04/01/2017	20/01/2017	Internet	$212.00	Y	2046
18	27/12/2016	24/01/2017	Internet	$115.00	Y	2107
19	25/01/2017	24/02/2017	Internet	$41.00	N	2284
20	27/01/2017	06/02/2017	Internet	$287.00	Y	2112
Sheet2

Excel 2012
	A	B	C	D	E	F	G
1	Check Number	Date	Description of Transaction	Debit (-)	Credit (+)	Balance	[Paid Y/N]
2						5000
3	2061	14/01/2017	Internet	40.05		4959.95	Y
4	2365	27/02/2017	Internet	137		4822.95	Y
5	2063	24/01/2017	1999 Ford	138		4684.95	Y
6	2132	08/02/2017	Internet	251		4433.95	Y
7	2312	26/01/2017	Internet	278		4155.95	Y
8	2046	20/01/2017	Internet	212		3943.95	Y
9	2107	24/01/2017	Internet	115		3828.95	Y
10					10000	13828.95
11	2112	06/02/2017	Internet	287		13541.95	Y
12							Y
13							Y
14							Y
15							Y
Sheet1

Cell Formulas
Range	Formula
F3	=IF(D3="",IF(E3="","",F2+E3),F2+E3-D3)
A3	{=IFERROR(INDEX(Sheet2!$F$2:$F$100,SMALL(IF(Sheet2!$E$2:$E$100=G3,ROW(Sheet2!$E$2:$E$100)-(ROW(Sheet2!$E$2)-ROW(Sheet2!$E$1))),COUNTIF(G$3:G3,G3))),"")}
B3	{=IFERROR(INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$E$2:$E$100=G3,ROW(Sheet2!$E$2:$E$100)-(ROW(Sheet2!$E$2)-ROW(Sheet2!$E$1))),COUNTIF(G$3:G3,G3))),"")}
C3	{=IFERROR(INDEX(Sheet2!$C$2:$C$100,SMALL(IF(Sheet2!$E$2:$E$100=G3,ROW(Sheet2!$E$2:$E$100)-(ROW(Sheet2!$E$2)-ROW(Sheet2!$E$1))),COUNTIF(G$3:G3,G3))),"")}
D3	{=IFERROR(INDEX(Sheet2!$D$2:$D$100,SMALL(IF(Sheet2!$E$2:$E$100=G3,ROW(Sheet2!$E$2:$E$100)-(ROW(Sheet2!$E$2)-ROW(Sheet2!$E$1))),COUNTIF(G$3:G3,G3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.