I am trying to simplify my budget. On the first sheet you select your month
(validation) and then input your reciepts. The second sheet outputs a sum of
the monthly expenses. The problem is when you select a different month, the
data shifts to that month. I can't figure out how to save the data in the
"January" column when I select "February" on the first sheet. I know that I
could have different sheets for each month, but I figured there has to be a
way to continue with what I have. Please Help.

Can you give examples of data on both sheets and/to explain how data "shifts"
to the new month.

"GF_Chris" wrote:

I am trying to simplify my budget. On the first sheet you select your month
(validation) and then input your reciepts. The second sheet outputs a sum of
the monthly expenses. The problem is when you select a different month, the
data shifts to that month. I can't figure out how to save the data in the
"January" column when I select "February" on the first sheet. I know that I
could have different sheets for each month, but I figured there has to be a
way to continue with what I have. Please Help.

To do what you want, you'd have to use a macro.

You would generally set up your data entry sheet as a continuous recordset
that does not abruptly stop. The month (and data validation) would be set up
in column A, then your transactions are entered row by row for however long
you want.

Then your summary report can be set up as a pivot table to, say, show only
data for January or show summary data YTD by category.

This is why the data entry page is not necessarily your clean and polished
reporting page. Usually those functions are handled on separate worksheets
using Pivot Tables/lookup formulas, or by using fancy tools on one worksheet,
such as various things on the Data menu (Group and Outline, Subtotals, List,
AutoFilter, etc.).

The way you describe what you are doing sounds like you really want a User
Form. You might learn how to do that (Excel has a rudimentary one built in
that you can try...go to the Data menu and select Form).

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"GF_Chris" wrote:

I am trying to simplify my budget. On the first sheet you select your month
(validation) and then input your reciepts. The second sheet outputs a sum of
the monthly expenses. The problem is when you select a different month, the
data shifts to that month. I can't figure out how to save the data in the
"January" column when I select "February" on the first sheet. I know that I
could have different sheets for each month, but I figured there has to be a
way to continue with what I have. Please Help.

Hi,

Well as my underestanding you want to enter your actual expense data in a
sheet with specify that in which month it happened and get summary of those
data in another sheet that looks like a montly expense report so i made a
file in Excel and if you give me your email i can send it to you.

Thanks,
--
Farhad Hodjat


"GF_Chris" wrote:

I am trying to simplify my budget. On the first sheet you select your month
(validation) and then input your reciepts. The second sheet outputs a sum of
the monthly expenses. The problem is when you select a different month, the
data shifts to that month. I can't figure out how to save the data in the
"January" column when I select "February" on the first sheet. I know that I
could have different sheets for each month, but I figured there has to be a
way to continue with what I have. Please Help.

I've not been able to understand pivot tables yet, but here's an sxample of
my Workbook:

sheet1:

MONTH
JAN<------------Data Validation List ( Jan Feb Mar etc..)

18.00 18.00 18.00 18.00 18.00<-------Sum of Columns
RENT POWER ACS1 ACS2 CAR <-------Bills
1.00 2.00 3.00 4.00 5.00 <-------Individual Reciepts
17.00 16.00 15.00 14.00 13.00

Sheet2:

BILLS JAN FEB MAR APR
RENT 18
POWER 18
ACS1 18
ACS2 18
CAR 18

Here's my formula for the month columns:

=IF(Sheet1!$A$2=Sheet2!B$1,Sheet1!$A$8,"")

This all seems simple enough, but I can't get Sheet2 to keep the Jan data
when I select Feb on Sheet1 and so on. I'm just starting to look at macros,
but I'm not sure that I'm savvy enough to pull it off.




"GF_Chris" wrote:

I am trying to simplify my budget. On the first sheet you select your month
(validation) and then input your reciepts. The second sheet outputs a sum of
the monthly expenses. The problem is when you select a different month, the
data shifts to that month. I can't figure out how to save the data in the
"January" column when I select "February" on the first sheet. I know that I
could have different sheets for each month, but I figured there has to be a
way to continue with what I have. Please Help.

I've not been able to understand pivot tables yet, but here's an sxample of
my Workbook:

sheet1:

MONTH
JAN<------------Data Validation List ( Jan Feb Mar etc..)

18.00 18.00 18.00 18.00 18.00<-------Sum of Columns
RENT POWER ACS1 ACS2 CAR <-------Bills
1.00 2.00 3.00 4.00 5.00 <-------Individual Reciepts
17.00 16.00 15.00 14.00 13.00

Sheet2:

BILLS JAN FEB MAR APR
RENT 18
POWER 18
ACS1 18
ACS2 18
CAR 18

Here's my formula for the month columns:

=IF(Sheet1!$A$2=Sheet2!B$1,Sheet1!$A$8,"")

This all seems simple enough, but I can't get Sheet2 to keep the Jan data
when I select Feb on Sheet1 and so on. I'm just starting to look at macros,
but I'm not sure that I'm savvy enough to pull it off.


"Toppers" wrote:

Can you give examples of data on both sheets and/to explain how data "shifts"
to the new month.

"GF_Chris" wrote:

I am trying to simplify my budget. On the first sheet you select your month
(validation) and then input your reciepts. The second sheet outputs a sum of
the monthly expenses. The problem is when you select a different month, the
data shifts to that month. I can't figure out how to save the data in the
"January" column when I select "February" on the first sheet. I know that I
could have different sheets for each month, but I figured there has to be a
way to continue with what I have. Please Help.

sheet1:
(column a b c d e)
MONTH
JAN<------------Drop Down Menu( Jan Feb Mar etc..)

18.00 18.00 18.00 18.00 18.00<-------Sum of Columns
RENT POWER ACS1 ACS2 CAR <-------Bills
1.00 2.00 3.00 4.00 5.00 <-------Individual Reciepts
17.00 16.00 15.00 14.00 13.00

Sheet2:
(column a b c d e)
BILLS JAN FEB MAR APR
RENT 18
POWER 18
ACS1 18
ACS2 18
CAR 18

Here's my formula for the month columns:

=IF(Sheet1!$A$2=Sheet2!B$1,Sheet1!$A$8,"")

This all seems simple enough, but I can't get Sheet2 to keep the Jan data
when I select Feb (or some other month) on Sheet1. I'm just starting to
look at macros, but I'm not sure that I'm savvy enough to pull it off.


"Toppers" wrote:

Can you give examples of data on both sheets and/to explain how data "shifts"
to the new month.

"GF_Chris" wrote:

I am trying to simplify my budget. On the first sheet you select your month
(validation) and then input your reciepts. The second sheet outputs a sum of
the monthly expenses. The problem is when you select a different month, the
data shifts to that month. I can't figure out how to save the data in the
"January" column when I select "February" on the first sheet. I know that I
could have different sheets for each month, but I figured there has to be a
way to continue with what I have. Please Help.

Here's some code to copy the data:

do:

Alt+F11 to get into Visual Basic Editor
ALt+ I and select "Module"
Copy/paste code into module
RUN macro

You could a button from the FORMS toolbar and asign this macro to it.

HTH

---------------------------------------------------------------------------------------

Sub CopyToSummary()
'

MonthsofYear = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul",
"Aug", "Sep", "Oct", "Nov", "Dec")

With Worksheets("Sheet1")
currMonth = .Range("A2") '<=== Drop down cell to select Month
.Range("A4:Z4").Copy '<=== copy columns A to Z (totals)
End With

monthnum = Application.Match(currMonth, MonthsofYear, 0) + 1 'Jan will
be column B

' copy data with Jan = Column B

With Sheets("Sheet2")
.Cells(3, monthnum).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End With

End Sub

"GF_Chris" wrote:

I've not been able to understand pivot tables yet, but here's an sxample of
my Workbook:

sheet1:

MONTH
JAN<------------Data Validation List ( Jan Feb Mar etc..)

18.00 18.00 18.00 18.00 18.00<-------Sum of Columns
RENT POWER ACS1 ACS2 CAR <-------Bills
1.00 2.00 3.00 4.00 5.00 <-------Individual Reciepts
17.00 16.00 15.00 14.00 13.00

Sheet2:

BILLS JAN FEB MAR APR
RENT 18
POWER 18
ACS1 18
ACS2 18
CAR 18

Here's my formula for the month columns:

=IF(Sheet1!$A$2=Sheet2!B$1,Sheet1!$A$8,"")

This all seems simple enough, but I can't get Sheet2 to keep the Jan data
when I select Feb on Sheet1 and so on. I'm just starting to look at macros,
but I'm not sure that I'm savvy enough to pull it off.




"GF_Chris" wrote:

I am trying to simplify my budget. On the first sheet you select your month
(validation) and then input your reciepts. The second sheet outputs a sum of
the monthly expenses. The problem is when you select a different month, the
data shifts to that month. I can't figure out how to save the data in the
"January" column when I select "February" on the first sheet. I know that I
could have different sheets for each month, but I figured there has to be a
way to continue with what I have. Please Help.

I don't think this will do what the poster really wants to accomplish.
Sheet1 is not going to "store" or "remember" what you had typed in for
January data. As soon as you use this macro to save January data to Sheet2
and then change the drop down in A2 to February, you have to delete all your
individual receipts (why such a manual process??). In your OP, you said you
may need to add another January receipt, but your worksheet does not change
to accommodate that. You will run this macro to record Feb receipts, change
A2 to January, delete the individual receipts still left on Sheet1, add your
new January receipt, and then when you run the macro, it is going to
overwrite the January data you had originally. While you can change
Operation: =xlNone to Operation: =xlAdd to get the new info added to what you
already had, you are still making this way too hard on yourself and leaving a
lot of room for error. When you delete your original transactions, how do
you know what comprised your totals on Sheet2? How will you audit yourself
to make sure you did in fact enter that receipt?

You either need to use a User Form or set up a more permanent tracking sheet
that you can enter data for whatever month's receipts you want whenever you
want. It can really be set up quite simply (Date in column A,
description/payee in B, category in C, amount in D), then Sheet2 can just use
SUMIF or SUMPRODUCT formulae to tally your results by month.

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Toppers" wrote:

Here's some code to copy the data:

do:

Alt+F11 to get into Visual Basic Editor
ALt+ I and select "Module"
Copy/paste code into module
RUN macro

You could a button from the FORMS toolbar and asign this macro to it.

HTH

---------------------------------------------------------------------------------------

Sub CopyToSummary()
'

MonthsofYear = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul",
"Aug", "Sep", "Oct", "Nov", "Dec")

With Worksheets("Sheet1")
currMonth = .Range("A2") '<=== Drop down cell to select Month
.Range("A4:Z4").Copy '<=== copy columns A to Z (totals)
End With

monthnum = Application.Match(currMonth, MonthsofYear, 0) + 1 'Jan will
be column B

' copy data with Jan = Column B

With Sheets("Sheet2")
.Cells(3, monthnum).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End With

End Sub

"GF_Chris" wrote:

I've not been able to understand pivot tables yet, but here's an sxample of
my Workbook:

sheet1:

MONTH
JAN<------------Data Validation List ( Jan Feb Mar etc..)

18.00 18.00 18.00 18.00 18.00<-------Sum of Columns
RENT POWER ACS1 ACS2 CAR <-------Bills
1.00 2.00 3.00 4.00 5.00 <-------Individual Reciepts
17.00 16.00 15.00 14.00 13.00

Sheet2:

BILLS JAN FEB MAR APR
RENT 18
POWER 18
ACS1 18
ACS2 18
CAR 18

Here's my formula for the month columns:

=IF(Sheet1!$A$2=Sheet2!B$1,Sheet1!$A$8,"")

This all seems simple enough, but I can't get Sheet2 to keep the Jan data
when I select Feb on Sheet1 and so on. I'm just starting to look at macros,
but I'm not sure that I'm savvy enough to pull it off.




"GF_Chris" wrote:

I am trying to simplify my budget. On the first sheet you select your month
(validation) and then input your reciepts. The second sheet outputs a sum of
the monthly expenses. The problem is when you select a different month, the
data shifts to that month. I can't figure out how to save the data in the
"January" column when I select "February" on the first sheet. I know that I
could have different sheets for each month, but I figured there has to be a
way to continue with what I have. Please Help.