I'm trying to automate the calculation of my prepayment schedule. The linked image http://img402.imageshack.us/img402/2...mentqueryp.jpg shows the basic layout of how it will look. The yellow cells are going to be the only input cells but i'm unsure what formula will achieve the desired result i'm looking for across the remainder of the spreadsheet. Essentially I need the formula to look at the period (start and end dates) the invoice covers and apportion it correctly. The apportionment isn't a straight equal division per month though as it has to be calculated according to what element of the expenditure hasn't been realised yet.
For example in the car park rent line the figure of £8,000 in July is derived from the fact that that is how much hasn't effectively been incurred yet as it relates to the remainder of the invoiced period (Aug - Mar) and is calculated as 8/12 x £12000.
I hope you understand what i'm requesting, if not, let me know and i'll try to provide a better explanation.
I have a column of data that may or may not have a formula in them. The cells are formatted in Accounting, w/o the leading $ sign. (i.e. 133.57) The value zero shows up as a hyphen or dash.
The issue is the format of the cell. In that attached worksheet, i inserted two formulas in cell b6 and b7. Both formulas are exactly the same, but the formatting is different. One is in the accounting format, but the other is in general. When I run the code, only the general formatted cell gets found.
My real worksheet is in the accounting formatted code, so I don't want to change my worksheet's numbers into a general format. How I can find the zero value thats in an accounting format?
Below is a strip down version of the code.
VB: Sub test()[COLOR=#333333] [/COLOR] Dim rLookInADR As Range Dim foundcell As Range Set rLookInADR = Range("b1:b380") Set foundcell = rLookInADR.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole) MsgBox (foundcell.Row) End Sub [COLOR=#333333][/COLOR]
I just downloaded an income/expense template from Zillow to manage my rental properties. The template works great but it's only set up for 5 houses and I need more columns as I own more than 5. In the attached template the houses are listed by property codes and that is the column that I need to add to.
IF B1 has a possible value ranging from 1 - 5, and IF the value in E1 is equal or great than 2,5,10,10,15 BUT 2,5,10,10,15 need to match to specific ranges set in B1 1=2, 2=5, ,3=10 ,4=10 ,5=15 THEN IF TRUE "WITHIN" IF FALSE "NEEDS UPDATE"
Got help earlier with this formula: =IF(AND(B1=1,E1<=5),"WITHIN","NEEDS UPDATE") :D Worked Great! This was my attempt at expand that formula:
I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:
I would like to master the dreaded array formulas. Any Excel based accounting consolidation tool or other consolidation tool out there that I could adapt to consolidate group accounts on a monthly basis.
I am trying to create this macro for my accounting journal What I want to happen is that in my sheet1 if the 1st cell in column a is "CASH" then the whole row should be copied and pasted in sheet 2. i want this to happen from the first cell in a column until the very last data in column a which means i am not certain up to what row number it will have data since this is a journal with uncertain number of transactions.
I have the macro shown below, which I found in a 2003 issue of the journal of accountancy - and it works great. However, it only works on a data set that begins in cell A1. I want to incorporate it into a spreadsheet I have where my data set begins in cell E15 and goes down from there(column E will be the only column that this macro will need to run on and I need it to work on a data set that will vary in length). This macro performs a Benford analysis, which analyzes the first and second number of a data set.
Dim Arrayone(0 To 9) As Integer Dim Arraytwo(0 To 9) As Integer Dim Arraythree(0 To 9) As Integer Dim Arrayfour(0 To 9) As Integer Dim Arrayfive(0 To 9) As Integer Dim Arraysix(0 To 9) As Integer Dim Arrayseven(0 To 9) As Integer Dim Arrayeight(0 To 9) As Integer Dim Arraynine(0 To 9) As Integer Dim Arrayzero(0 To 9) As Integer Dim Arraytwotest(10 To 99) As Integer
Dim x, I
Dim Row As Long, Col As Long, Step As Long, Colcells
I am trying to determine if a specific date is what week in a 4-4-5 accounting month. See attached file. Is there a formula that can be used to do this???
I have a custom sort order that I use for sorting military ranks in a number of massive spreadsheets I am using. Normally the data we get has a long version for the ranks (column 1) and a rank code (column 2). When the rank code is there it is simple to sort by that column in descending order, but without it I need a custom sort. The problem is there are up to three equivalent ranks at each level across Army, Navy and Air Force as you can see in the attachment.
Is there a way I can set up a custom sort based on both columsn of data but sorting only on the second column. I could do vlookups but there are up to 50,000 rows and I don't really want to keep that formula there, and as I use it across multiple sheets (yes I know all one sheet, but it is segregated data and used in different situations) including a vba written value would have to occur for every row on every sheet - not the best option in my opinion - maybe it is.
I have a macro set up to copy and paste data from worksheets into worksheet 1. But the number of worksheets often changes. Is there a way to write this to include all worksheets even if the number changes?
Sometimes I will receive this file and there will be additional tabs. I just never know how many.
I copy down 500 rows knowing there will never be more data than that. And for each worksheet I paste to sheet 1 I add 500 rows to not paste over other data.
I would like the computer to read it as "select last indexed worksheet, copy paste to index 1, repeat while selecting the left adjacent worksheet until you reach index 1.
For each new worksheet selected add 500 rows to the last pasted amount."
If I would like to compute a running balance, how do I make the balance display conditional so that I don't have the balance displayed all the way down the page? I like to copy the formula and the formatting down the page, but I don't want to see the running balance unless I have entered info in the row.
I'm trying to run the code below to add functions to a couple of columns untill the value of the cell is equal to "End". I am recieving the error "Object variable or With block variable not set (Error 91)" and I'm not sure why. I have set my WITH up and I have defined the object. Any thoughts would be greatly appreciated. Thanks.
Option Explicit
Private Sub CommandButton1_Click() Dim shtXL As Excel.Worksheet Dim wbkXL As Excel.Workbook
Set shtXL = wbkXL.ActiveSheet With shtXL Do Until ActiveCell.Value = "End"
I export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.
Here are the steps I go through: 1. Clear the first 3 rows and the last 5 rows 2. Copy the totals in the last row of the data and paste them in the first row 3. Subtotal the different categories in row 2 and add a validity check 4. Add a new column A with a formula to add customer numbers on each line =IF(ISERROR(FIND("00000",B5)),A4,B5 5. Copy the formulas and paste values over them 6. Filter the data to show blanks under the "Doc Date" column, delete all rows 7. Filter the data to show blanks under the "Type" column, delete all rows 8. Filter the data to show "Total:" under the "Apply No" column, delete all rows 9. Turn off filters, format all numbers in accounting format 10. Check the validation at the top to ensure no transactions were deleted
I am trying to create a worksheet that tracks the age o f the inventory in my warehouse. I am charged each week at an increasing rate and want to be able to track what these charges will be. I would like to be able to sum up the data below in a pivot table that shows how many units are falling into each age group, this would add up to a max o f 10 weeks o f increasing charges.
3/1/14 received 1000 units 3/6/14 ship 600 units 3/8/14 charge for 1 week at 400 units 3/8/14 receive 500 units (900 pieces on units) 3/13/14 ship 300 units 3/15/14 charged for 100 units at 2 weeks and 500 units at 1 week
This needs to be able to assume this is going on for 10 different items, I would like to be able to track the items independently and in bulk.
Our accounting software exports various data (in this instance, birthdays) thusly in Excel, making it impossible to sort. This is just an example of 2 employees, obviously in a longer report there would be a lot more names.
How can I get the name and birthdates in 2 columns so I can sort them by either field?
Is it possible to update a cell with an additional number eg from 22 to 23 when a template is opened. In accounting packages for example, every time you open an invoice it automatically increases the invoice number to the next higher number. I was thinking I might be able use excel to create an inwards goods receipt for items received for repair.
I have a number of equity trades (both purchase and sales). I need to know the book cost of those sale trades to figure out the realized gain/loss according to First-in-first-out method.
Since the unit sold may included units bought at different time and different price, so I have to first exhaust the first lot that I purchase before moving into second lot, and so on and so for.
Column G - J are for illustration. I highlighted K10, K11 and K12 are book cost that I want to calculate. Instead of manually separating out units in each lot previously bought. Is there an automatic way? I would not mind adding new columns to ease calculation.
In the accounting number formats, the available currency symbols are Dollar ($), Pound (₤), Euro (€), and Yuan (¥). But how can I add a custom currency symbol? For example instead of writing "$1,000", I want to write "BDT. 1,000" or "৳ 1,000". How can I do that?
Im trying to count people working during times of the day. They work shifts from 8am to 9pm. I need to know at each 30 minute interval of the day how many are working. IE at 12:00 or say 13:30 I need to know how many are working. I also need to be able to account for people that are sick. IE If a person works during the day I wouldnt count them as sick at night.
Im data is set up like Column A agent name Column B agents start time Column C agents end time Column D holds two variables either sick or support.
I have found a thread that counts the people working but doesnt account for the sick or support variables. I didnt link that thread as I thought it went against the rule "STATE WHAT YOU WANT TO DO, NOT WHAT YOU THINK YOU NEED TO ACHIEVE IT "
I have to illustrate that the average life of a set of accounts is 6 weeks. I figured that the best way to add significance to this length of time is to show in either a pie, column or bar chart the relevancy of 6 weeks within a 52 week year.
Sounds fine, right? Well, I went blank after that. I couldn't think of how to set up my worksheet in order to generate a chart of this nature.
So, I guess I'm wondering how you guys would set up a table that could then be used as the source for either of the aforementioned charts.
I am having a terrible time with Excel today. The version I'm using is Excel 2010.
I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.
The simple division is =13588/12
The output in accounting format should be 1,132.33.
Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.
I make our employee schedule at work, and so in Excel I have eight sheets for one workbook. (Employee schedule, then each day of the week) On each day is a table separating the shifts. Morning, Split and Night.
I was wondering if there was a way for Excel to look at the schedule sheet, and then automatically place each employee and their shift on the following day sheets. (example: Joe has 2-10 on Monday, 10-6 on Thursday and Friday) So the Excel puts Joe underneath Split 2-10 for Monday, and Morning 10-6 on Thursday and Friday.
