Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Can anyone povide step by step instructions on how to do the follo

I would like the excel spread sheet i created to do the following:

Ive created an excel spreadsheet using simple =sum(cell) commands for the
purpose of a personal budget.. And i would like upon opening of this
spreadsheet for firstly it to ask the following, so when i type my amount
into an amounts box it will use the correct cell position.

All 3 questions that follow would be on the one page, with an option to add
another expense entry onto the spreadsheet, or finish & save, then goto view
the budget.

1) Be able within a drop menu select the heading (EG entertainment or
House & Garden etc) so it selects the correct "column" for the amount which
will be entered in Q:3.

2) Then, Select a date from a drop menu so that it selects the correct
"row" so that when i enter the amount spent on this day for the particular
expense it selects the correct cell. that Q:3 refers to.

3) Then, have an entry box which would have been referenced by the
previous selections so that the amount i type into it will be correctly
placed onto the spreadsheet. according to colun and row.


After entered.. when pressing on add another expense it saves the amount
onto the field... then blanks the previous options selected so a new criteria
can be entered in and saved... etc etc.


This surely is possible.. ive spent money to try and learn this but im just
getting no-where.. Please help me if you can..


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Can anyone povide step by step instructions on how to do the follo

Sounds less like a specific question and more like a project which is what
many of us do for a fee.

--
Don Guillett
SalesAid Software

"Daniel Bunt" <Daniel
wrote in message
...
I would like the excel spread sheet i created to do the following:

Ive created an excel spreadsheet using simple =sum(cell) commands for the
purpose of a personal budget.. And i would like upon opening of this
spreadsheet for firstly it to ask the following, so when i type my amount
into an amounts box it will use the correct cell position.

All 3 questions that follow would be on the one page, with an option to
add
another expense entry onto the spreadsheet, or finish & save, then goto
view
the budget.

1) Be able within a drop menu select the heading (EG entertainment or
House & Garden etc) so it selects the correct "column" for the amount
which
will be entered in Q:3.

2) Then, Select a date from a drop menu so that it selects the correct
"row" so that when i enter the amount spent on this day for the particular
expense it selects the correct cell. that Q:3 refers to.

3) Then, have an entry box which would have been referenced by the
previous selections so that the amount i type into it will be correctly
placed onto the spreadsheet. according to colun and row.


After entered.. when pressing on add another expense it saves the amount
onto the field... then blanks the previous options selected so a new
criteria
can be entered in and saved... etc etc.


This surely is possible.. ive spent money to try and learn this but im
just
getting no-where.. Please help me if you can..




  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default Can anyone povide step by step instructions on how to do the follo

There are a couple of approaches to take to easily solve this.

On the same sheet you could simply have 2 cells with Data Validation set up
to get the budget category entry and date from and another cell to enter the
amount into and a Worksheet_Change() event to detect when the budget amount
entry changes and go put the amount in the proper cell.

Another way, and this seems a little more like what I think you are looking
for, would be to set up a user form that would do much the same actions - 2
list boxes and an text entry box and a couple of command buttons should do it.

Third way would be to set up much like 1st way I described, but on a
separate sheet in the workbook.

If Excel's Help on topics such as Data Validation, the worksheet On Change
event and user forms don't get you the information you need, feel free to get
in touch with me at HelpFrom @ jlathamsite.com (remove spaces).

"Daniel Bunt" wrote:

I would like the excel spread sheet i created to do the following:

Ive created an excel spreadsheet using simple =sum(cell) commands for the
purpose of a personal budget.. And i would like upon opening of this
spreadsheet for firstly it to ask the following, so when i type my amount
into an amounts box it will use the correct cell position.

All 3 questions that follow would be on the one page, with an option to add
another expense entry onto the spreadsheet, or finish & save, then goto view
the budget.

1) Be able within a drop menu select the heading (EG entertainment or
House & Garden etc) so it selects the correct "column" for the amount which
will be entered in Q:3.

2) Then, Select a date from a drop menu so that it selects the correct
"row" so that when i enter the amount spent on this day for the particular
expense it selects the correct cell. that Q:3 refers to.

3) Then, have an entry box which would have been referenced by the
previous selections so that the amount i type into it will be correctly
placed onto the spreadsheet. according to colun and row.


After entered.. when pressing on add another expense it saves the amount
onto the field... then blanks the previous options selected so a new criteria
can be entered in and saved... etc etc.


This surely is possible.. ive spent money to try and learn this but im just
getting no-where.. Please help me if you can..


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Can anyone povide step by step instructions on how to do the f



"JLatham" wrote:

There are a couple of approaches to take to easily solve this.

On the same sheet you could simply have 2 cells with Data Validation set up
to get the budget category entry and date from and another cell to enter the
amount into and a Worksheet_Change() event to detect when the budget amount
entry changes and go put the amount in the proper cell.

Another way, and this seems a little more like what I think you are looking
for, would be to set up a user form that would do much the same actions - 2
list boxes and an text entry box and a couple of command buttons should do it.

Third way would be to set up much like 1st way I described, but on a
separate sheet in the workbook.

If Excel's Help on topics such as Data Validation, the worksheet On Change
event and user forms don't get you the information you need, feel free to get
in touch with me at HelpFrom @ jlathamsite.com (remove spaces).


++++++++++++++++++++++++++++++++++++
Thankyou JLatham

Ive now learnt how to create a Data Validation List..

What i did was create 2 fields..
1st field.. Date Selection Validation List
2nd Feild.. Category Of Expense validation List

My only question at this point is...

How do i create a data entry box, then have it so the data entered will
use the validation selections to put the text into the right cell?

EG
Select Date: ( 03 Jan 07 )
Select Expense: (Entertainment)
Enter Amount : ( $25 ) Then press Enter

I want to type $25, hit enter, it saves, and it appears below on the correct
field, it then clears the box for another entry)

Expenses | Home & Garden | Entertainment | Food |
Date
01 jan 07
02 jan 07
03 Jan 07 $25
04 Jan 07
05 Jan 07


thankyou for your patience with this.
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default Can anyone povide step by step instructions on how to do the f

Daniel,
Things are a little sparse as far as how and where your data is laid out
(what sheet(s) and what cells), but this will give you an idea of where to go
from here.

This assumes the following layout:
Everything is on the same worksheet
The budget table begins at A1 (the word "Expenses" is in A1)
and for this example,
your date selection is at H1
your category selection is at H2
and your amount entry is at H3

Then this code all goes into the worksheet's code section. To get to the
right place, right-click on the sheet's name tab and choose [View Code] from
the pop-up menu, then cut and paste this into that area in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theCategory As Long
Dim theDate As Long

If Application.Intersect(Target, Range("H3")) Is Nothing Then
Exit Sub
End If
If Range("H1") = "" Or Range("H2") = "" Then
Exit Sub ' can't match on no entry
End If
Application.EnableEvents = False
theCategory = Application.Match(Range("H2"), Rows("1:1"), 0)
theDate = Application.Match(Range("H1"), Columns("A:A"), 0)
ActiveSheet.Cells(theDate, theCategory) = Target.Value
'clean up entries for next entry
Range("H1:H3") = "" ' clear out entries made
Range("H1").Select ' ready for next entry
Application.EnableEvents = True
End Sub

To explain: the 1st 3 lines of code check that the change was to the amount
entry, and ignores any other changes made on the sheet.
The next 3 lines of code make sure our MATCH() statements will work, and if
they wouldn't (empty cell(s) at H1 or H2) then again, we just get out.

The .EnableEvents keeps the event from firing when we put the value on the
sheet so that we don't waste the extra time coming in and checking where that
happened and finding it wasn't in H3 and leaving again.

We 'steal' Excel's worksheet function MATCH() to find out the row number
that the date you chose was on in column A, and in which column of row 1 the
category appears in. MATCH() returns a number representing the position in
the searched list.
We then use the Cells() property of a worksheet to use those values from
MATCH() to tell it where to put the value you typed into H3 on the sheet.
Then the 3 input cells are cleared out and we go back up to H1, all ready for
another entry.

Before we leave, we turn .EnableEvents back on so that this will all happen
again when you make the next entry into cell H3.

Hope this helps get you going. Just change the various cell, row and/or
columns referenced in the code to match the way your sheet is really laid out
and it should be a good basis for getting it to work.

If you have got your data entry over in column A (as A1, A2, A3 instead of
H1, H2, H3) and your budget stuff actually starts in B1, then you'll need to
adjust the placing of the value into the matrix in the CELLS() reference.
Simply adjust that to account for the extra column offset as:
ActiveSheet.Cells(theDate, theCategory+1) = TargetValue

Also, if that's the kind of setup you have, then set up the lists so that
you are choosing the Date in A1, so that you won't get tripped up by always
having a MATCH() to your chosen category show up in column 1 when that
MATCH() is executed in the code across Row 1. Either that or bump your data
validated lists down to start below row 1.

"Daniel Bunt" wrote:



"JLatham" wrote:

There are a couple of approaches to take to easily solve this.

On the same sheet you could simply have 2 cells with Data Validation set up
to get the budget category entry and date from and another cell to enter the
amount into and a Worksheet_Change() event to detect when the budget amount
entry changes and go put the amount in the proper cell.

Another way, and this seems a little more like what I think you are looking
for, would be to set up a user form that would do much the same actions - 2
list boxes and an text entry box and a couple of command buttons should do it.

Third way would be to set up much like 1st way I described, but on a
separate sheet in the workbook.

If Excel's Help on topics such as Data Validation, the worksheet On Change
event and user forms don't get you the information you need, feel free to get
in touch with me at HelpFrom @ jlathamsite.com (remove spaces).


++++++++++++++++++++++++++++++++++++
Thankyou JLatham

Ive now learnt how to create a Data Validation List..

What i did was create 2 fields..
1st field.. Date Selection Validation List
2nd Feild.. Category Of Expense validation List

My only question at this point is...

How do i create a data entry box, then have it so the data entered will
use the validation selections to put the text into the right cell?

EG
Select Date: ( 03 Jan 07 )
Select Expense: (Entertainment)
Enter Amount : ( $25 ) Then press Enter

I want to type $25, hit enter, it saves, and it appears below on the correct
field, it then clears the box for another entry)

Expenses | Home & Garden | Entertainment | Food |
Date
01 jan 07
02 jan 07
03 Jan 07 $25
04 Jan 07
05 Jan 07


thankyou for your patience with this.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Can anyone povide step by step instructions on how to do the f

Thankyou so much..
JLathem.. You definatly have have made one Excel user very happy. Ill give
this a really good working over to get it working for me.. If all fails..
would you consider allowing myself to email you the budget which i have
created? Just so we are on the same viewpoint of what im trying to do.

Thnx again :)

"JLatham" wrote:

Daniel,
Things are a little sparse as far as how and where your data is laid out
(what sheet(s) and what cells), but this will give you an idea of where to go
from here.

This assumes the following layout:
Everything is on the same worksheet
The budget table begins at A1 (the word "Expenses" is in A1)
and for this example,
your date selection is at H1
your category selection is at H2
and your amount entry is at H3

Then this code all goes into the worksheet's code section. To get to the
right place, right-click on the sheet's name tab and choose [View Code] from
the pop-up menu, then cut and paste this into that area in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theCategory As Long
Dim theDate As Long

If Application.Intersect(Target, Range("H3")) Is Nothing Then
Exit Sub
End If
If Range("H1") = "" Or Range("H2") = "" Then
Exit Sub ' can't match on no entry
End If
Application.EnableEvents = False
theCategory = Application.Match(Range("H2"), Rows("1:1"), 0)
theDate = Application.Match(Range("H1"), Columns("A:A"), 0)
ActiveSheet.Cells(theDate, theCategory) = Target.Value
'clean up entries for next entry
Range("H1:H3") = "" ' clear out entries made
Range("H1").Select ' ready for next entry
Application.EnableEvents = True
End Sub

To explain: the 1st 3 lines of code check that the change was to the amount
entry, and ignores any other changes made on the sheet.
The next 3 lines of code make sure our MATCH() statements will work, and if
they wouldn't (empty cell(s) at H1 or H2) then again, we just get out.

The .EnableEvents keeps the event from firing when we put the value on the
sheet so that we don't waste the extra time coming in and checking where that
happened and finding it wasn't in H3 and leaving again.

We 'steal' Excel's worksheet function MATCH() to find out the row number
that the date you chose was on in column A, and in which column of row 1 the
category appears in. MATCH() returns a number representing the position in
the searched list.
We then use the Cells() property of a worksheet to use those values from
MATCH() to tell it where to put the value you typed into H3 on the sheet.
Then the 3 input cells are cleared out and we go back up to H1, all ready for
another entry.

Before we leave, we turn .EnableEvents back on so that this will all happen
again when you make the next entry into cell H3.

Hope this helps get you going. Just change the various cell, row and/or
columns referenced in the code to match the way your sheet is really laid out
and it should be a good basis for getting it to work.

If you have got your data entry over in column A (as A1, A2, A3 instead of
H1, H2, H3) and your budget stuff actually starts in B1, then you'll need to
adjust the placing of the value into the matrix in the CELLS() reference.
Simply adjust that to account for the extra column offset as:
ActiveSheet.Cells(theDate, theCategory+1) = TargetValue

Also, if that's the kind of setup you have, then set up the lists so that
you are choosing the Date in A1, so that you won't get tripped up by always
having a MATCH() to your chosen category show up in column 1 when that
MATCH() is executed in the code across Row 1. Either that or bump your data
validated lists down to start below row 1.

"Daniel Bunt" wrote:



"JLatham" wrote:

There are a couple of approaches to take to easily solve this.

On the same sheet you could simply have 2 cells with Data Validation set up
to get the budget category entry and date from and another cell to enter the
amount into and a Worksheet_Change() event to detect when the budget amount
entry changes and go put the amount in the proper cell.

Another way, and this seems a little more like what I think you are looking
for, would be to set up a user form that would do much the same actions - 2
list boxes and an text entry box and a couple of command buttons should do it.

Third way would be to set up much like 1st way I described, but on a
separate sheet in the workbook.

If Excel's Help on topics such as Data Validation, the worksheet On Change
event and user forms don't get you the information you need, feel free to get
in touch with me at HelpFrom @ jlathamsite.com (remove spaces).


++++++++++++++++++++++++++++++++++++
Thankyou JLatham

Ive now learnt how to create a Data Validation List..

What i did was create 2 fields..
1st field.. Date Selection Validation List
2nd Feild.. Category Of Expense validation List

My only question at this point is...

How do i create a data entry box, then have it so the data entered will
use the validation selections to put the text into the right cell?

EG
Select Date: ( 03 Jan 07 )
Select Expense: (Entertainment)
Enter Amount : ( $25 ) Then press Enter

I want to type $25, hit enter, it saves, and it appears below on the correct
field, it then clears the box for another entry)

Expenses | Home & Garden | Entertainment | Food |
Date
01 jan 07
02 jan 07
03 Jan 07 $25
04 Jan 07
05 Jan 07


thankyou for your patience with this.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default Can anyone povide step by step instructions on how to do the f

If things are as you've described (and as I've interpreted your description)
it should work for you. But if it gives you troubles, sure, attach the
workbook to an email and send it to (remove spaces) HelpFrom @
jlathamsite.com It'll get here.

"Daniel Bunt" wrote:

Thankyou so much..
JLathem.. You definatly have have made one Excel user very happy. Ill give
this a really good working over to get it working for me.. If all fails..
would you consider allowing myself to email you the budget which i have
created? Just so we are on the same viewpoint of what im trying to do.

Thnx again :)

"JLatham" wrote:

Daniel,
Things are a little sparse as far as how and where your data is laid out
(what sheet(s) and what cells), but this will give you an idea of where to go
from here.

This assumes the following layout:
Everything is on the same worksheet
The budget table begins at A1 (the word "Expenses" is in A1)
and for this example,
your date selection is at H1
your category selection is at H2
and your amount entry is at H3

Then this code all goes into the worksheet's code section. To get to the
right place, right-click on the sheet's name tab and choose [View Code] from
the pop-up menu, then cut and paste this into that area in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theCategory As Long
Dim theDate As Long

If Application.Intersect(Target, Range("H3")) Is Nothing Then
Exit Sub
End If
If Range("H1") = "" Or Range("H2") = "" Then
Exit Sub ' can't match on no entry
End If
Application.EnableEvents = False
theCategory = Application.Match(Range("H2"), Rows("1:1"), 0)
theDate = Application.Match(Range("H1"), Columns("A:A"), 0)
ActiveSheet.Cells(theDate, theCategory) = Target.Value
'clean up entries for next entry
Range("H1:H3") = "" ' clear out entries made
Range("H1").Select ' ready for next entry
Application.EnableEvents = True
End Sub

To explain: the 1st 3 lines of code check that the change was to the amount
entry, and ignores any other changes made on the sheet.
The next 3 lines of code make sure our MATCH() statements will work, and if
they wouldn't (empty cell(s) at H1 or H2) then again, we just get out.

The .EnableEvents keeps the event from firing when we put the value on the
sheet so that we don't waste the extra time coming in and checking where that
happened and finding it wasn't in H3 and leaving again.

We 'steal' Excel's worksheet function MATCH() to find out the row number
that the date you chose was on in column A, and in which column of row 1 the
category appears in. MATCH() returns a number representing the position in
the searched list.
We then use the Cells() property of a worksheet to use those values from
MATCH() to tell it where to put the value you typed into H3 on the sheet.
Then the 3 input cells are cleared out and we go back up to H1, all ready for
another entry.

Before we leave, we turn .EnableEvents back on so that this will all happen
again when you make the next entry into cell H3.

Hope this helps get you going. Just change the various cell, row and/or
columns referenced in the code to match the way your sheet is really laid out
and it should be a good basis for getting it to work.

If you have got your data entry over in column A (as A1, A2, A3 instead of
H1, H2, H3) and your budget stuff actually starts in B1, then you'll need to
adjust the placing of the value into the matrix in the CELLS() reference.
Simply adjust that to account for the extra column offset as:
ActiveSheet.Cells(theDate, theCategory+1) = TargetValue

Also, if that's the kind of setup you have, then set up the lists so that
you are choosing the Date in A1, so that you won't get tripped up by always
having a MATCH() to your chosen category show up in column 1 when that
MATCH() is executed in the code across Row 1. Either that or bump your data
validated lists down to start below row 1.

"Daniel Bunt" wrote:



"JLatham" wrote:

There are a couple of approaches to take to easily solve this.

On the same sheet you could simply have 2 cells with Data Validation set up
to get the budget category entry and date from and another cell to enter the
amount into and a Worksheet_Change() event to detect when the budget amount
entry changes and go put the amount in the proper cell.

Another way, and this seems a little more like what I think you are looking
for, would be to set up a user form that would do much the same actions - 2
list boxes and an text entry box and a couple of command buttons should do it.

Third way would be to set up much like 1st way I described, but on a
separate sheet in the workbook.

If Excel's Help on topics such as Data Validation, the worksheet On Change
event and user forms don't get you the information you need, feel free to get
in touch with me at HelpFrom @ jlathamsite.com (remove spaces).


++++++++++++++++++++++++++++++++++++
Thankyou JLatham

Ive now learnt how to create a Data Validation List..

What i did was create 2 fields..
1st field.. Date Selection Validation List
2nd Feild.. Category Of Expense validation List

My only question at this point is...

How do i create a data entry box, then have it so the data entered will
use the validation selections to put the text into the right cell?

EG
Select Date: ( 03 Jan 07 )
Select Expense: (Entertainment)
Enter Amount : ( $25 ) Then press Enter

I want to type $25, hit enter, it saves, and it appears below on the correct
field, it then clears the box for another entry)

Expenses | Home & Garden | Entertainment | Food |
Date
01 jan 07
02 jan 07
03 Jan 07 $25
04 Jan 07
05 Jan 07


thankyou for your patience with this.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Step by Step fak119 Excel Discussion (Misc queries) 2 August 31st 06 08:11 AM
Need step by step instructions to try, not a demo. Can't remember [email protected] Excel Worksheet Functions 1 June 15th 06 09:24 PM
Need step by step instructions to try, not a demo. Can't remember [email protected] Excel Worksheet Functions 0 June 15th 06 10:05 AM
FRUSTRATED!!! COMBO BOX AND PROGRAMMING IT Russell-stanely Excel Discussion (Misc queries) 6 July 7th 05 09:19 PM
I need step by step instructions to create a macro for 10 imbedde. diana Excel Worksheet Functions 3 January 31st 05 02:56 AM


All times are GMT +1. The time now is 10:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"