UserForm For An Expense Report

Jun 24, 2008

I have an expense report with one row labeled as "Auto", and 7 columns labeled with Sunday, Monday, Tuesday, etc. I'd like to double-click a cell within that row and have a userform (or something else) pop up with 2 spots for data entry: "Personal" and "Company". A user would enter dollar amounts in one or both fields. After they're finished, I would like the total of what they entered to populate the cell that was double-clicked, but still have that breakdown available or even be able to change it by re-double-clicking the cell. Is that even possible?

To add complication, at the end of the row are two additional columns that total personal and company expenses. I'd have to have all personal expense amounts sum together in its column, as well as all company expenses sum together in the other column.

View 10 Replies


ADVERTISEMENT

Change Pivot Table Report Filter From Userform Textbox / Combobox

Aug 17, 2013

I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using combobox on userform.

Sample Data

ID
NAME, INIT
GENDER
DEPT
SALARY
DOH
LOCATION
RAISE

1
Smith, J.
F
Sales
$41,250.00
2/2/1982
Boston
$45,375.00

[Code] .....

VB:
Sub CreatePivot()
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("Employees Data").Select
Range("A1").Select

[Code] .....

Error:
Private Sub ComboBox1_Change()
ComboBox1.Value = objTable.PivotFields("DOH")
End Sub

View 1 Replies View Related

Summary Different Day Expense

Feb 4, 2010

I am trying to summary my differentday food data. i want to add total in SUmmary table.

View 6 Replies View Related

VBA Code To Change Pivot Table (Report Filter) From Userform Textbox / Combobox

Aug 16, 2013

I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using Textbox on Userform. I've attached an excel file as an example.

View 2 Replies View Related

Expense Account Lookup

Aug 19, 2007

The expense account we work with involves several currencies due to the international nature of the business. With that, each row must show the currency involved and the formula used from one line to the next makes it repeat the currency until changed i.e. if cell B22 has a date entered then Cell H22 will reflect the currency from Cell H21 =IF(B22="","",H21). If Cell H21 showed CDA for Canadian currency then H22 would also become CDA. Once changed manually then all cells below will now reflect the new currency until changed again.

Using the lookup function we collect the individual amounts of each currency and run totals at the bottom. Therefore, if there were three entries in SGD (Singapore Dollars) and two entries in EUR (European Euros) etc. that each row will do a lookup by the three letter currency code and collect the sum of each currency. SGD could repeat later again and when changed manually will be included in the lookup.

At the present we are entering the three digit codes manually i.e. SGD. What we would like to do is automatically have the bottom be able to pickup the currency change when a new currency is entered on the individual rows on the top part of the expense sheet. So if Cells H21:H23 were SGD and Cells H24:H25 were ERU that Cell A58 would show SGD and cell A59 would somehow be able to grab the ERU etc. One of the same currencies may repeat itself and the lookup will capture the additional totals but SGD would only show once.

View 9 Replies View Related

Payment Expense Spreadsheet With Gst

Nov 3, 2006

I used erecord the other day to do my activity statement for the first time (I have just started a home based business which has not yet started trading but I had to send the BAS for the purchases that I had made for the business) and it was quite easy to use and you can send it electronically to the ATO which saves a lot of hassle particularly as I am very not accountancy litterate. However I am trying to develop an expenses/payment spreadsheet similar in function to erecord but that allows me to categorise the inputs.

My headers are:
Date
Cbookref = (drop down validation box) similar to a chart of account #
Category = ie - advertising accounting fees etc.. uses a look up function with cbook ref to populate field
Description .........................

View 5 Replies View Related

Expense Sheet - Correct Day And Date

Oct 21, 2011

We have a form (an Expense Sheet) that has the date entered in the "Monday" cell using the =Today() formula (the rest of the week uses =Today()+1, =Today()+2, etc).

I am betting you already see the problem...this only works if you open the form on a Monday....before I point out the error to the document owner I thought I better have a solution...

So...what is the best way to self-populate the "Monday" cell with the correct date for the Monday of the week?

View 2 Replies View Related

Expense Tracker By Month For Multiple Inputs Then Add Together?

May 14, 2014

I am looking to take the data off of a "detail" sheet and put it to a summary page. I want the summary page to find the capital and expense from "details" sheet by the month on the "details" sheet. Then for every month add all the expenses and capital and put as 2 values per month, Capital and expense, on the summary page. I am not really sure where to begin but have added my excel workbook that I have started.

View 2 Replies View Related

Calculate An Average Daily Expense Cell

Feb 6, 2009

I've set up a simple spreadsheet to keep track of my food expenses every month. The first column is for the date, the second is for the daily total expenses and then the next three columns are where I add the data which is then calculated into the daily total column.

I also have a total at the bottom for the entire month. Now what I want to do is I want to also have underneath the grand total, a cell which keeps track of my average daily expenses. Basically I want to divide the total expenses by the number of days which I've entered data. Now normally this would be fine but because I've applied the formula to all the cells in the expense column, it automatically lists every day as "0" rather than leaving it blank. So when it does the average calculation it's dividing my total by 30 days rather than by only the 7 days I have data for.

View 14 Replies View Related

Categorized Daily Expense To Monthly Basis

Jan 8, 2010

While I was working my daily expense I come up with this issue. I do eat outside while I am on work. Sometimes I go to Pizza, sometime I go to mexican etc etc. The common between them is word FOOD. I would like to modify the formula suggested by Ron Coderre

=SUMPRODUCT((TEXT($A$2:$A$14,"mmm")=$E3)*($B$2:$B$14=F$2)*$C$2:$C$14)

See the attached file to get more idea of my question. Then I would Like to Highlight Entire Rows which contains a Specific text.

View 5 Replies View Related

Creating Personal Expense Tracking Form

May 14, 2006

I'm trying to make a spreadsheet that will track my expenses. What happens is I enter in my daily expenses in a "Notes" worksheet. This includes the date, whether it's a debit/credit, and what category is it (rent, tuition, entertainment, work income etc). It looks like this

Date - - - - - - Debit - - - Credit - - - Category
1-May-06........................$500...........Rent
4-May-06........................$30 ..........Dining out
etc..

Then I have another worksheet called "Expense outline" which pretty much sums all expenses in each category and displays a summary. So it would show how much I have spent in total on each category for each month. Looks something like this

------------------May-----------June---------July...
Rent..................$300
Cable/TV............$50
Internet.............$50
Dining out..........$100
Entertainment.....$50

So what I did for the Entertainment summary for the month of May was, I used a SUMIF(column of categories, "Entertainment", column of credits). This will look for the category name "Entertainment" in my "notes" worksheet, and sums the corresponding amount from the credit row. The problem is, I also want to include it so that it will automatically differentiate between the different months. Right now, when I'm choosing the column of categories for May, I select only the cells in the month of may when I'm choosing my column of categories and credits.

For example:....................

View 3 Replies View Related

Creating Individual Report Cards (worksheets) Based On Template For Report Card

Aug 30, 2013

I have two worksheets in my report cards:

1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.

Student
Math
Reading
Science

Jimmy
75
84
100

[code].....

2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.

Student Name
=Data!$A2

Math
=Data!$B2

Reading
=Data!$C2

Science
=Data!$D2

How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?

I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.

View 1 Replies View Related

Copy Data From Weekly Report To Monthly Report

Jul 2, 2008

How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.

View 4 Replies View Related

Report Structure (create A Report Either By Using Or Without Using VBA)

Apr 12, 2009

find the attached Example file. I need to create a report either by using or without using VBA.

View 3 Replies View Related

Track A Number Of Expense Items Accross 15 Worksheets With Up To 500 Rows Accross 30 + Columns Per Worksheet

Jan 12, 2010

I'm looking to use excel to track a number of expense items accross 15 worksheets with up to 500 rows accross 30 + columns per worksheet. Many of the learned people in this forum have helped me get this far, now I need some more assistance - please.

In my speadsheet I have a vlookup formula that returns a value from another worksheet. Here's an example.=IF(ISERROR(VLOOKUP($D3,Room_Configs!$A$1:$BO$3006,MATCH(M$1,Room_Configs!$1:$1,0),FALSE)),"",VLOOKUP($D3,Room_Configs!$A$1:$BO$3006,MATCH(M$1,Room_Configs!$1:$1,0),FALSE))

This works brilliantly. Now here comes the tricky part. What I'd like to do is append that formula with another one to do a vlookup on a second worksheet.

If both lookups return a value then I'd like the value of the 1st vlookup returned in the cell. If the value of the 1st vlookup is "0", then I'd like the value of the 2nd vlookup returned, and if the 1st and 2nd vlookup values are blank then a "0" is returned.

The name of the 2nd work sheet is "Non_Network_Equip"

Finally, it would be really great if the font colour for values returned from the 2nd vlookup forumla was blue.

View 9 Replies View Related

SAP Report

Apr 16, 2008

I am working in SAP, I am attempting to convert a report in excel and I'm get the following error codes/debugger messages:

1ST Error Message is : Path/File access error: 'VBD1.tmp'

Then I press okay and get a Visual Basic Error:
Run Time Error '1004'

Methon 'Copy' of object'_Worksheet' failed

If I press debug I get a bunch of Visual Basic sheet, with the following highlighted

Set ws = SAPSheets.item(SheetIndex)

View 9 Replies View Related

Created Userform And Command Button But Data Entry Not Allowed In Userform

Jul 16, 2012

I have created a userform and a command button to bring up the user form but when I click on the command button and the user form pops up I am not able to enter any data, the entire page freezes

This is the code

Private Sub CommandButtoncancel_Click()
unloadme
End Sub
Private Sub CommandButtonOK_Click()
With Workbooks("RETS results version 2.xlsm")

[Code] ......

View 1 Replies View Related

Macro That Deletes Sheet With Control & Shows UserForm Causes UserForm To Disappear

Jun 15, 2009

This is weird - if you delete a sheet that contained a control then

a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End
b. public variables lose their value

These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?

View 9 Replies View Related

Aging Report ...

Nov 25, 2008

I am trying to customize an aging report, column will be 30, 60, 90 120 + I would like the amount to show under each column. The charge amount for $252.38 each day.

View 9 Replies View Related

Rollout Report

Jul 14, 2008

So this is the problem, there are 65 files that are stored in a folder, each file has 8 tabs, all these tabs within each file are identical in every aspect ( i.e cell ranges and names). The max number of rows in a tab is 81. To break it down:

Tab 1 : Instructions (this will never change and will always be constant among all 65 files)
Tab 2: Row 1 will be the same for all files, rows 2-9 i don't need, except for the info on cell C3 which I need to be copied through S11:S33. S10 needs to equal A3 with the format of R10. And then just have the next files tab info below this table with the same logic being followed.
Tab 3: Same logic as tab 2 but now the range is from S11:S53.
Tab4: Same logic, just copy and paste tables for all files, and doing the same thing with column S hat was done in tab 2 in order to be able to filter tha data.
Tab 5: Same logic, with H1 being filled by the descriptor on Tab2( This will enable one to filter though the information since the descriptor in cell 3 from tab 2 is the short name given to each files which differentiates the data.
Tab 6; Same logic
Tab7: Same logic, but would like a drop down box on right to filter by service centers.
Tab 8: Same logic, but would like to have drop down on rigt in which I can select the given short name ( i.e cell 3 from tab 2 of each file)

This way I can have one master file which encompasses all the information from all 65 files in one file and be able to sort through the data in each tab by the short name description. I need a macro for this since these files get updated in a weekly basis.

Any help on writting such a code would be greatly appreciated, my excel VBA skills are non existent!. I have attached the template of how one of these files looks like. There all the same except with different names of course and all are stored in one folder.

View 12 Replies View Related

VBA Report With Forms

Dec 5, 2009

sometimes pivot tables won't give me the details I want the way I want. any time my boss asks me for a monthly report I have to do everything manually from the pivot table and insert the data on another table where I put the filteres data. I want to be able to get a monthly sales report per store that will populate itself in a daily basis, I also want to be able to select a store, style, size and date from a form in Excel and see the report in a sheet. Aproblem that I have found in order to use the size as a parameter is that the size comes in the same line as the item description and do not know how to make excel to check just for the size in the description field.
The attached file contains sales data for a better understanding of my case.

View 14 Replies View Related

Report The Max Number And The Min

Oct 31, 2005

I have a coulmn with results in that are in a decimal point example .01, .02.
At the bottom of the coulumn I want to report the max number and the min
number but it does not. Also in one column my results are listed as <0.05
etc. At the bottom of this column I want to report the max and min but it
does not. I believe because they are not whole numbers. Is there a way to
report these numbers. I tried an IF formula where if <0, max(i12:i36) but
that did not work.

View 12 Replies View Related

Generate Report With %

Jan 8, 2008

Analysis of Profit is very impt in business , that is why i keep trying to find way to use excel to generate profit report.

I have been trying to solve this for last one month. even when i bath... But no result.

May i ask any one here know how to convert my data to a report using . Below table said every thing.

Project (10)  HIJ1My Raw Data File  2BrandMonthProfit In %3Brand AJan 0730%4Brand BFeb 0750%5Brand AJan 0730%6Brand BFeb 0750%7   8   9I Need Report In New Work Sheet  10   11BrandJan 07Feb 0712Brand A30%30%13Brand B50%50% Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

To Write A VBA Report

Dec 9, 2008

i have accumulated a lot of data on an excel sheet now i need to be able to run 2 reports that will spit out the data in a specific way but i have no clue but i have made a word document to show how i would like it to spit out

View 9 Replies View Related

Summary Report

Apr 30, 2009

I'm working on some homework for a CIS class, that is pretty straight forward...except I don't know how to make a summary report in excel.

View 9 Replies View Related

Create Per Day Report

Jun 25, 2009

I've three columns excelsheet and want to create Per day report.

Date,Records,Status

I want to count per day records and status.

This will be self-explanatory when you check this screenshot

View 9 Replies View Related

Making A Report

Jun 6, 2006

1- I 'm working with a table wit many Sheets.

I want to make a Report Sheet with selective ranges(cells)
the selective cells should paste as link ( in order to be updated cells)

2- then how to Zip this worksheet to zip file ( to save it)

3- If I have a Macro and i want to perform this macro to all sheet except one sheet how to that ?

View 6 Replies View Related

Report Not Working

Mar 8, 2007

Just to fill you in on what I am doing, I am am putting a report macro in my spreadsheet that looks to see if a value meets the criteria if it does then it will copy a selection of cells data from that sheet into a new sheet. It will do this for all the sheets so in theory I should end up with a simmilar kind of report function to access. The problem is to when I do this the data does not appear, I do not know why.

Private Sub cmdPoor_Click()
Dim sheet As Worksheet
For Each sheet In Worksheets
If Not sheet.Name = "Main" Then
If Not sheet.Name = "Template" Then
If Not sheet.Name = "Help" Then
sheet.Select
If Range("L44").Value = Poor Then
Dim strName As String
Dim strSurname As String
strName = Range("L41").Value
strSurname = Range("L42").Value...................

View 4 Replies View Related

Best Way To Create A Report

Apr 10, 2007

As it is now I use several loops to scan one sheet to collect the data and paste it onto a second sheet. The first sheet has several rows of data, denoted by one of four colors, that I have to collect and put into a report grouped by color. Each color represents the current status of a part of a project. The while loops stop on no data in column one. So each loop runs down the sheet, looks at the data row color and rejects it if it is not the right color for that section of the report. I am sure there is a better way to do this. I was thinking maybe create a temporary data base, collect the data adding fields for the current status then use a sql for each section of the report and insert rows as required.

While Worksheets("All Work"). Cells(iGetRow, 1).Value <> Empty
Set Cell1 = Cells(iGetRow, iGetCol)
Set Cell2 = Cells(iGetRow, iGetCol + 4)
Set rCurrent = Range(Cell1, Cell2)
rCurrent.Select
With Selection.Copy
If rCurrent.Interior.Color = Purple Then 'Purple is a constant
Worksheets("Report").Paste Destination:=Worksheets("Report").Cells(iPutRow, 1)
iPutRow = iPutRow + 1
End If
iGetRow = iGetRow + 1
End With
Wend

Data Structure:.............

View 2 Replies View Related

Userform Loads Combobox Values Upon Userform Initialize

Oct 1, 2009

I have one userform that loads combobox values upon userform Initialize. Though through a second userform changes can be made to anotherworkbook this workbook is saves any changes. when i close the second userform i need to rerun the 1st userform Initialize event to update the combobox's incase changes have been made.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved