Creating Account Spreadsheets

May 16, 2006

I have agreed to create a simple accounts system for a friend of my mothers. I have no problem when it comes to manipulating cells to interact ( sums) and then creating an aesthetically pleasing spreadsheet. However, as with all things excel, there is an easier way to doing something and a person usually doesn't realise that till the end.

I was hoping that somebody might be able to direct me to a resource that would be ideal for constructing a 12 month revenue and expenses account. I just want it to be as simple as possible for the lady that I am doing this for (all I want her to do is enter how much she made for one day and how much she spent), and everything around this I can make interact.

ADVERTISEMENT

Complex If Statement To Calculate Date Range From Create Account To Stop Account

Sep 7, 2012

Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.

original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}

New formulas needed or revised from the above:

Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)

Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"

Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.

Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)

[Code] .........

View 1 Replies View Related

Creating Macro That Converts Spreadsheets To PDF?

Feb 3, 2014

So I am starting to get ambitious with Excel now and am looking at creating a macro that will automatically create a pdf file of several worksheets at the same time. However this is my problem. Using the current version of Excel there is a wonderful record macro button, however when I try to include a function that would create a PDF, it says that I need to save any changes before creating the PDF. However when I save the file, I have to stop recording which defeats the whole purpose of creating the macro in the first place.

How can I use the record macro function to create my pdf macro without learning visual basic? If I cannot, how to write what it is that I am trying to accomplish?

View 1 Replies View Related

Creating Summary Spreadsheet From Two Seperate Spreadsheets

Mar 16, 2007

I have two spreadsheets. I need to be able to match information from detailed spreadsheet for specific information from a lookup spreadsheet.

The detailed sheet (call it Purchases) has information about what was purchased during a month.

Columns: ...

View 9 Replies View Related

Mapping Account . How To Add Additional Data In To Account#

Aug 21, 2009

I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?

View 9 Replies View Related

Creating VBA Code To Lookup Return Values In Spreadsheets

Apr 20, 2014

I have 3 workbooks "ID numbers, 07 Car,and 08 Car".

I need a VBA code that will use the value in column C in each workbook, look up the value in workbook "ID numbers", column C thru G, and return
values into each workbook:

Workbook ID number Column C add values to Each workbook (Column B)
Workbook ID number Columns D thru E) add values to Each workbook (Columns D thru E)

See 07 Car workbook highlighted in orange, every product below the orange highlight must look like for both workbooks this after the code is ran.

View 5 Replies View Related

Creating Separate Reports In Separate Spreadsheets

Nov 21, 2006

I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.

I want to do this automatically.

I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).

Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.

Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet

There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...

View 9 Replies View Related

Account Reconcilition

May 21, 2006

to write a macro to put the background color where a certain figure sum is zero. Eg. Column A contain as
5
-5.25
12
-5
-12

If one select A1 to A5 and run the macro the color for all cell should be green except for A2. Infact the macro will point out which amount is not deposited into the bank. (All +tives are for Deposits and all –tives are for sales)

View 9 Replies View Related

Revenue Summation By Account Name

Aug 15, 2013

Attached is my sample data...Indicative Data_Revenue 2.xlsx

My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.

The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage

Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.

View 9 Replies View Related

Counter For Transactions Within Account

May 11, 2009

I need to have a running counter of transactions within an account.

Solved with: C2=COUNTIF($B$4:B4,B4)
(and copy down)
Example:
ACCTNO AMT Trans#
100125.001
100121.002
100122.50 3
1001 2.00 4
1001 5.00 5
100127.00 6
1013 .50 1
1013 2.50 2
1013 13.00 3

I need to solve for the Trans# I've included it here for clarity, but I need to be able to get that number based on the ACCTNO. Notice the Counter Resets to 1 when the ACCTNO changes from 1001 to 1013.

View 6 Replies View Related

Account For “negative” Time

Dec 9, 2009

I’m trying to get the difference between times, BUT I want to account for “negative” time. So if I project that an employee will finish a task at 2 am, and they finished at 2:30 am, I want the result to be -:30, not just :30. If they finished at 1:30 then it would just be :30. Hence, my dilemma. I can get the difference, but didn't think Excel could recognize negative times. Right now I am using =MOD((A1-A2),1) or =(A1-A2+(A1<A2))*24/24 and in cells A1+A2 I am using military time.

View 2 Replies View Related

VBA To Fill References ='Account (1)'!$C$5

Feb 26, 2009

I need to produce a grid displaying data pulled from local references within the workbook but Excel will not auto fill the references in the right way.

i need

='Account (1)'!$C$5
='Account (2)'!$C$5
='Account (3)'!$C$5

down to

='Account (498)'!$C$5
='Account (499)'!$C$5
='Account (500)'!$C$5

is there a way to imput these references using VBA script?

View 10 Replies View Related

Forumla To Get The Average Per Account?

May 31, 2007

If you Have a list of account numbers and amounts, is there a way in one forumla to get the average per account?

View 9 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

Formula Account For All Possible + Or - Figures

Nov 23, 2007

I am working with percentages, and trying to avoid #DIV/0! Errors at the same time.
Lets assume column A holds last years figures, and column B holds this years figures, column C calculates the difference in percentages.

I am having dificulty where say A1 = 0 B1 = 10 (C1 Should be 100%)
then what happens if A2 = 5 and B2 = 0 (C2 Should be - 100%)

Is there a common formula that will account for all possible + or - figures in A and/or B that will also suppress #DIV/0! Errors?

View 9 Replies View Related

SUM Account For Added Rows

Jan 4, 2007

I've created a simple budget sheet outlining various group expenses eg: Fixed, Flexible, Variable, and their totals sums.

How can I add all totals while still allowing for a new row to be inserted in any group?

eg:..............Row E

1........Fixed Expense group
2..................$20.00
3..................$40.00
4..................$40.00
5....Total sum $100.00
6.......Flexible Expenses group
7...................$10.00
8...................$20.00
9...................$20.00
10..................$10.00
11..................$50.00
12...Total sum $110.00
13......Variable expenses group
14..................$10.00
15..................$10.00
16..................$10.00
17..................$20.00
18..................$10.00
19....Total sum $60.00
20--------------------
21..Grand Total $270.00

View 4 Replies View Related

Sum Of A Column Based On Different Account Codes

Jan 3, 2014

I am trying to find the revenue generated by different groups of accounts to figure out which group is worth the most, and rank the others.Here is a attached screenshot of what I am working with, cell BK22 is the revenue of all of the accounts coded AADA but i need to find an easier way other than plugging it in for all 3500 cells.

View 6 Replies View Related

Number Formatting (in Account Numbers)

Apr 20, 2009

I currently have account numbers formatted 1.234.5678 and need to convert them all to 1234.5678. how this can be done without manually keying 22,000 account numbers?

View 3 Replies View Related

Sumif Last Two Digits Of Account Equals To 60

Feb 15, 2012

ABaccountamount60000-502,503.4060000-601,143.4860000-70847.7260000-80282.1860000-85238
.1870000-50156.8560000-89200.0080000-80300.0090000-70400.0070000-6020.00

i need formula that will sum column "B" if the last two digits of column "A" = 50,60 or 70.

View 2 Replies View Related

SUMIF To Add Range Of Account Number

Apr 6, 2013

I have account numbers in Col F and values H on sheet1

I want to set up a formula on sheet account classification to add the values in Col H based on a range of account numbers from 3000 to 3017.

View 6 Replies View Related

Map Account Numbers From Text File

Sep 23, 2007

I receive a large text file each month from a vendor, This file contains account numbers, account descriptions and costs associated with the previous months period. There are 5 separate business units included in this report. I am looking for a way to translate the account numbers pprovided by the vendor to fit with in the account numbers we use. A Vlookup will work by creating a Map that translates their numbers to match ours, but this would need to be copied in monthly and as there are 15k rows or more it will likely take a while to process. I am wondering if others have attempted this and have advise as to what works best.

My plan was to create an Excel file that reflects the account numbers provided with our account numbers and then use a VLookup to match the data received with this master file as it were. ( So I would have a file called AccountMap which contains 4 columns, Cols A & B is the info provided by the vendor and Cols C & D are our codes and descriptions ). Then when the data arrives I would enter my vLookup and go from there.

Presently the steps are:
Open Vendor Text File in Excel
Copy a Business Unit and related Data to new WorkSheet
Insert Vlookup to map the vendor accounts to our accounting system format
Rearrange the data to meet our import requirements
Export the modified range as a Text file
Import into Accounting system

View 3 Replies View Related

Create Ledger Account Based On Cashbook?

Mar 16, 2014

I want to create leger accounts In cashbook there is account head (ex. Hand Loan A/c, Salary A/c, Exam A/c and so on)

View 2 Replies View Related

Copy Ledger Account Names And Numbers To Each Row?

Apr 22, 2014

I've been working with ledgers to analyze companies financials in detail mostly using pivot tables and charts.

I usually get the data in a format that has the account name and number as a title on top and then each entry for this account under the title. In order to get my pivots working I need to copy the account name and number to each row until the next title.

Here is a picture to understand what I mean: Makrokyssäri.png

So is there a formula or a macro that could avoid doing the copying manually?

View 3 Replies View Related

Drag Formula Across A Row And Still Take Account Of Mixed Reference

Mar 15, 2009

1. I have data A1, A2, A3....down a column.

2. I have one value on C2 ( constant value)

3. the formula is a very simple multiplication formula. I want to multiply every single value of column A to C2. But it needs to be in across a row that starts in
D4. So,formula needs to be in (D4, E4, F4) there are other datas in the worksheet

4. So when I input =$A1*$C$2 in D4 it calculates it fine but when i try to drag the formula across the row so [E4=$A2*$C$2, F4=$A3*$C$2...]

it repeates the same fomula as D4 and does not adjust for the increase in column number.

View 3 Replies View Related

Create Ledger Account In Multiple Sheets?

Mar 13, 2014

I have CASHBOOK Worksheet and I want to create a LEDGER Account in Multiple sheet

View 2 Replies View Related

TRANSPOSE Rows To Columns - Account Types

Apr 23, 2014

I have a huge file with names, addresses and account types & numbers. However, there's a separate row for each account type instead of listing the person's name once and indicating all of their account types on that row as well in separate columns. I've seen several post regarding different Macro codes to use; however, none work for my project since I have several columns that need to be compared beginning on A2:K2 to A24628:K24628. I have attached a small example for your review.

Transpose Needed.xlsx

View 2 Replies View Related

Extending Range Function To Account For Different Row Lengths?

Apr 25, 2014

VB recorder in Excel. The macro is designed to process a spreadsheet that will have the same number of columns and same type of data in each column, process this data with a formula (hat tip: AlKey+XOR LX) and then clean up the columns ready for use in another program.

I have got it working but when I alter the row length the macro only works to the final row in the recording spreadsheet. I realize this is because when recorded the macro I told VB to set the range to a given row.

I remember there is a way of setting the Range function that tells VB to always go to the bottom of the rows filled with data but I can't find it for the life of me.

see the attached file the training spreadsheet.

[Code] ......

DdSample_training_data.xlsx
process.docx‎

View 4 Replies View Related

Mid Function :: Match Account Number With Description

Apr 20, 2009

I am trying to use a Mid function to help me in my vba problem.

In my first table I have a list of account numbers. E.g. 'ZZ500543'

In my second table I have a list of account descriptions. E.g. 'Denis Morgan ZZ500543 leak from bath'

I need to match the account number in Table1 with the correct account description in Table2. I thought a Mid function would be the best option to pick out the account number in Table2? Is this right? How would I go about using it?

View 14 Replies View Related

Offsetting Positive And Negative Values Within Each Account

Apr 13, 2014

I have a sheet which contains credits and debits for several accounts:

Customer
Amount

ABC
2000

XYZ
3000

XYZ
1000

ABC
1000

ABC
-5000

XYZ
1000

What I need to do is match negative and positive values within each account and highlight them. I have been able to put together a VBA code from google and other forums to match within all rows.

Code:
Sub matchdata()
On Error Resume Next

For Each cl In Columns(10).SpecialCells(2, 1)
Err.Clear

[Code] ........

1. I need to adapt the code so as to do matching within accounts. Example within ABC and XYZ separately.There will be 100s of accounts.
2. If possible, I need to offset a credit (negative value) with multiple debits. Example: ABC -5000 would offset ABC 2000 and ABC 1000. I am not worried about the remaining balance.

View 1 Replies View Related

Account For Two Variables - Automating Percentages Calculations

Apr 25, 2014

What I do at my job is schedule production runs for bakery items. these items consist of many ingredients that go into them (Flour, Sugar, etc.) The products go into the run at certain percentages (20% of the run is flour, 30% is eggs, 15% is Sugar, etc) Many times we will have two or more types of a product, sugar for example. between the different types they can all have a different cost per pound, so what we try to do is use the cheapest stuff first to save money. So for part of this excel sheet what I would like it to do is to automate what percentage I should use the sugar based on cost. the total percentage for the sugar would add up to 100%.

for the second half of this sheet, there is another variable to account for. for this product we also need to hit target specifications so the product will be ok to sell. The example is Protein in this situation. we have a target protein that we need to hit per serving of 24.2 grams. I have the file set up to convert the protein percentage to protein per serving in grams, so that is already done.

my problem with this sheet is I can only seem to do one or the other. I can set it up to use the cheapest product first and add up to 100%, but it won't hit the target protein. Or I can have it set up to hit the target protein, but then I won't hit the 100%, it's be above or below. I need it to account for both variables and then give me the suggested percentages.

So is there a way that I can have it formulate what percentage to use something at to hit a target and equal 100%?

tl;dr I need to account for two variables. hit target protein, and have the total percentage equal 100%

View 1 Replies View Related

Searching For Afolder With Different Account Names On C: Drive

Jul 13, 2006

I have a loads of folders with different account names on my c: drive. What I want to do is put in part of the name rather than al ofl i.e. Manchester United, would just put in Manchester I have seen the * used but was not sure how this works

View 2 Replies View Related

Repeating Account Codes To Be Deleted If They Hold The Same Value.

Jan 17, 2007

I have attached a reduced example spreadsheet.

I have thousands of IDs each of which store a column called IM Limit. The ID is repeated on many rows - for each date that they have come into the system. For each ID over a period of time the IM Limit may change or it may stay the same. If it stays the same I would like all the other rows deleted, if it changes once during the period then I would like only two rows left behind each containing the unique value. So in the end I will have a list of codes where a unique combination of id and the im limit remains behind.

Does anyone know or have a VBA script that would be able to loop through the spreadsheet and delete the unwanted records.

View 3 Replies View Related

Display Account Numbers Where Stock Items Are Not Present?

Sep 3, 2013

I have a list of account numbers with stock attached to each account. I want to show all account numbers that do not have any one of 5 items attached to it. See my attached (simplified) example, show all accounts that dont have Bananas or Oranges, account number 222 and 444 would be displayed.

View 5 Replies View Related

Find Specific Account Value From A Range Of Text Strings

Jan 6, 2014

Need formula to find a specific account no. from a range of text and return that account's value as a usable numeric.

Account number:012345678 XZ
Account value as of 12/31/2013:$12,345,56

Account number:987654321
Account value as of 12/31/2013:$876,543.21

View 12 Replies View Related

How Do I Hardcode A Formula Into A Macro And How Do I Take Report Size Into Account

Mar 23, 2009

Please see attached two books,

The first book is a report that is generated on my reporting systeM (Report I use everyday)
The second report is the one that I have created myself using a macro (macro report)

I have to manually manipulate this report everyday and extract some information from it to

a custom report(macro report) that we use everyday.

I have created a macro for this - please see my code below.

I need some help with some small issues I am having,(Highlighted in yellow)

1) I need to include a Total line, but the size of the report varies everyday from say

around 30 rows to over a 100 rows. Is there a way i can make the total always 2 rows below

the report, please see attached for an example.

2) I need to hardcord a formula in on Column F and G. But again the report varies in size,

the columns do not change but there could be lots more rows. how do i take this into

account

The formula i need to hard code into column F is

=(IF(($D$60>=0)*AND($D$60<=5000000),0.0009))+IF(($D$60<=10000000)*AND($D$60>5000000),0.0007

)+IF($D$60>10000000,0.0005)

D60 in the above formula is just the total of all the other months summed together.

For Column E the formula is just =F*E But again the size in the report varies.

View 13 Replies View Related

Sum Accounts Based On Middle 2 Numbers In Account Code

Jul 17, 2012

I have a large workbook that contains hundreds of account codes and values. For example, cell A2 contains the account code "55021357122 Global Business Sales", cell A3 contains code "55021753221 Global Business Purchases" and so on through the column. Column B contains the values.

I need a formula that will sum all the values in column B if the middle 2 number are "21" (positions 4 & 5 in the number). My current range for column A is A6:A366. I've tried SumIFs but that fails on the array because it fails on the MID function.

View 6 Replies View Related

Formula To Add Up Values Based On Range Of Account Numbers

Apr 2, 2013

I would like a formula to add up values in Col E based on a range of account numbers in Col C

For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc.

View 9 Replies View Related

Account Numbers In Column A Where The Customers Have Purchased At Least 1 Accessory

Aug 5, 2008

I have account numbers in column A where the customers have purchased at least 1 accessory, where the accessory numbers are in column B. If they have purchased more than 1, their account number will be duplicated in column A. I would like to represent the data so that the account number is unique in column A, accessory 1 in column B, accessory 2 in column C and accessory 3 in column D.

Is there any way of doing this?

I'm open to suggestions in MS Access.

View 9 Replies View Related

Combine Rows Based On Account Number/product No

May 8, 2007

I am curious if there is a way to combine the content of rows based on a change in account number(column A). The tricky part of it is that an account number could be in the file once and another could be in the data twelve times. The list is sorted by account number. I want to take the product code and description and keep placing in the combined row for each account number. Attached is a sample of the data.

View 6 Replies View Related

Excel 2003 :: Templates For Personal Bank Account Management

Dec 10, 2013

I really need a comprehensive excel file which can manage my bank account id, savings, transactions, withdrawals, transfers etc etc. could be better if it can show any graphical interface with charts as well. I am willing to have a file so that i can manage more than one bank accounts. ( Excel 2003 is my version)

View 3 Replies View Related

Number Formatting :: Show A Variety Of Account Numbers Properly

Jul 22, 2009

I am trying to find the right format that will show a variety of account numbers properly.

I have these types of numbers displaying like this right now:

9.04585E+13 (should be 90458524211818)
785656.001 (showing properly)
NRDE1312089 (showing properly)
SCGA 396.00 (showing properly)

The formatting is set to general, which works for all but the top type of account number. I tried setting to a custom format like "#.#######" ...The only problem with that format is that the "." shows up whether there is a decimal in the number or not. What's the way around that?

View 9 Replies View Related

ADVERTISEMENT