Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Step by Step | Excel Discussion (Misc queries) | |||
Need step by step instructions to try, not a demo. Can't remember | Excel Worksheet Functions | |||
Need step by step instructions to try, not a demo. Can't remember | Excel Worksheet Functions | |||
FRUSTRATED!!! COMBO BOX AND PROGRAMMING IT | Excel Discussion (Misc queries) | |||
I need step by step instructions to create a macro for 10 imbedde. | Excel Worksheet Functions |