Petty Cash Spreadsheet

Jan 4, 2009

i am trying to set up a spreadsheet for petty cash so will have a description and an amount on the left of screen and numerous columns on the right for the transactions to be split between ...... Is there any way for me to tell it that if A2=fuel truck then the amount in column B2 needs to also be in column G2?

ADVERTISEMENT

Excel Petty Cash - Department And Nominal Code Separator?

May 22, 2014

I have a petty cash sheet set up:

A1 - Date
B1 - Receipt No.
C1 - DEPT CODE
D1 - NOMINAL CODE
E1 - Reason
F1 - Amount
G1 - Running total (I've put a little formula in there to keep the balance - clever eh!)

So - this is fine - except I have to add the total amount for each department/nominal code each month.

What I'm doing at the moment is copying and pasting, sorting the sheet by:

Department
Nominal Code
Date

Then doing a manual add up of each and adding it to my month end report.

I'm really not an expert at all - but I thought there must be an easier way for me to do it. After inputting the petty cash there MUST be a way that it can automatically calculate it?

Or is this going to be a difficult thing to do requiring expertise in pivot tables etc?

View 2 Replies View Related

How To Create Cash Up Workbook

Apr 15, 2014

I am new to macros on excel but I am trying to create a cash up workbook. This is what I want it to do:

First Sheet should be a summary of the month with each day. From there I want it to generate 4 worksheets for every day's cash up. (which should be Daily Cash up, Credit Card Sales, Cheque Sales & Account Sales).

The Daily Cash up Sheet must pull info from the other 3 and must allow me to input cash values manually. But the cc sales, cheques sales and account sales must be pulled from the other sheets.

After completion of cash up there should be a button on the Daily cash up sheet to say Finalise, where when it is pressed it summarises all totals on the first sheet. So when I open the workbook every morning, there should be a button on the first sheet to create a new 'cash up'.

Then there should also be a button on the first worksheet for month end, so that when you press month end it finalises the month and generates a new work book for the next month.

View 7 Replies View Related

Building A Cash Register

Mar 6, 2007

I want to build a cash register from excel.

I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.

Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).

To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.

How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).

View 5 Replies View Related

Break Even Calculations In Cash Flow Row

Jan 20, 2014

I have a row which shows date by month and then a row which shows cash flow. I would like a formula to calculate the break even point in the cash flow row and return the month which this occurs. I would also like to show the number of units required to break even.

View 2 Replies View Related

Monthly To Quarterly Cash Flows

May 13, 2014

I am building a Discounted Cash Flow.

I have monthly cash flows rolling for 10 years, which I need to sum up into quarterly cash flows.

View 1 Replies View Related

Split Amount Into Different Cash Denomination

Mar 25, 2011

The amount is $1256,

Cash Denomination will be $500, $100, $50,$10,$5 and $1. I need to split the amount into the number of pieces of each domination to present to the bank when i need to withdraw salary for the workshop workers.

500100501051
1256221011

View 5 Replies View Related

Discounted Cash Flow Worksheet?

Jan 26, 2013

I am looking for a discounted cash flow worksheet where I can set the period of time to a certain number of years, set the terminal value at the end of that period of time, as well as having the ability to set the discount rate. When all three things are set, I would like to see the annual cash flows as well as the present value amount.

View 1 Replies View Related

Debug Selection & Cash Register

Jun 7, 2009

I'm working on a sheet where I can specify sales by selection of month (Sheetname= ZoekenMaand).

Everything works fine for the months "januari" and "februari" (yes, it's in dutch ) but from "maart" it doesn't seem to work any more.

View 3 Replies View Related

VBA Code To Open A Cash Drawer!!!

Feb 16, 2010

I have Created an Excel Spreadsheet to work as a EPOS system. The only Problem i am having finishing it off is getting the cash Drawer to open at the end of a sale.

I have my Cash Drawer connected via a Rj 45 in the Back of my Receipt Printer. The Printer i have is a Star Tsp-600. I have Found out that the Open Drawer Codes are 7 or 27,7,10,50,7. I am not entirly sure what they mean though. The code that i have at the moment doesnt work it brings an error "53" file not found. This is my code

View 3 Replies View Related

Filter For CASH NOMS And Keep Everything Thats In The Criteria

Sep 24, 2009

i have rec data in which I need a macro that will filter for CASH NOMS and keep everything thats in the criteria but delete everthing else in cash Noms

criteria is named range "Criteria" (I5:K10)

So from this

Sheet1  ABCDEFGHIJKL4        Criteria   5RecTeamAmountRef    RecTeamRef 6CASH NOMSAMP1132.36CAETRN    CASH NOMS*AMP*.AIMS 7CASH NOMSAMP215.36.AIMS546.3      *WTRN96 8CASH NOMSINTEREST915.12WTRN96      *AMPTRH 9CASH NOMSFA5-65.36AMPTRH      *.WSTFGH 10CASH NOMSFA6-9.01CBWSSG      *WTWHAT 11CASH NOMSCA-9.01CBWSSG        12CASH NOMSDES-9.01MSFAST        13CASH LIFEUUTU-9.01CBWSSG        14CASH LIFEAMP1-9.01CBWSSG        15CASH LIFEAMP232.12.WSTFGH        16CASH LIFEAMP315975.36CBWSSG        17CASH LIFEAMP4-9.01OPTMOD        18CASH NOMSFA1-9.01CASH WTWHAT         Excel tables to the web >> Excel Jeanie HTML 4 .........................

View 9 Replies View Related

Cash Flow Distribution Modeling

Nov 24, 2004

I am trying to model the distribution of cash flows from a real estate investment. This is a bit complicated, but basically A1 through A37 are a series of cash flows after payment of debt service from month 0 (closing) to month 36. The real estate project has two investors, the Institutional Investor and the Developer. In this case, the Institutional Investor contributes 90% of the required project equity and the developer contributes the remaining 10%. The cash flow is distributed according to a set of parameters specifying cash flow percentage allocations and IRR targets, collectively known as a "cash flow waterfall"." As with all for-sale residential development deals, the early cash flows are negative and then turn positive as unit sales occur. The order of distribution of the cash flow remaining after debt service is paid is as follows (the waterfall):

1. Both investors receive the return of their invested capital from cash flow distributions.
1. Hurdle 1 (after the return of equity to both investors), the Institutional Investor receives 90% of the monthly cash flows and the Developer 10%, proportional to their respective overall equity contributions, until both investors achieve a 12% IRR (all IRR's to be calculated from deal inception/first dollar invested).
2. Hurdle 2, (after Hurdle 1 is satisfied), the Institutional Investor receives 60% of the monthly cash flows until the Institutional Investor achieves an 18% IRR.
3. The Final Split (after satisfying Hurdle 2) allocates monthly cash flow 50% to each investor.

On an overall project basis, the Developer should realize a disproportionate share of the cash flow after the second hurdle is hit. This is what is known as the "promote." The most recent 36 month (37 including Closing) projected cash flows are as follows:

($1,315,567) - Closing
($113,697) - Month 1
($178,397)
($168,667)
($150,365)
($36,506)
$16,662
$120,283
$107,443
$107,443

I realize this is complicated, but this structure is quite common to real estate, private equity, venture capital and hedge fund investments (along with most any joint venture deals with multiple classes of equity participants). The canned software that is out there that performs this type of anlysis is both expensive and rigid, and doesn't typically enable the required sensitivity analysis in structuring a deal.

View 9 Replies View Related

Offset Function For Cash Flows In Different Years

Sep 5, 2007

I have got three period like this

Period: 2010 2015 2018
Cash: 100 200 300

I would like to use a function (i am thinking offset will do the work) to put these cashflows on a timeline i.e. 2010,2011,2012 etc...

The three periods described above will be changeable, so the cashflows will adjust accordingly...

View 9 Replies View Related

Cash Register With Keyboard Numerical Keypad

Aug 22, 2007

With reference to Cash Register example that I downloaded from Roy Cox; is it possible to link the numerical keypad that is created in the file to the PC Keypad.
I realise I would need to use keypress functions but am unaware of the ascii for these keys. Also; how do I limit that the keypad writes it to the label? Otherwise, would I need to create a new userform instead of what was created by Roy.

View 3 Replies View Related

Projection Spread Sheet For Cash Flow Purposes

Jun 2, 2009

I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.

A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...

I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.

View 9 Replies View Related

Payback & Discounted Formulas For Uneven Cash Flows

Nov 7, 2006

formula in excel to calculate payback and discounted payback periods with uneven cash flows.

EX.
Yr CF
0 ($2,000)
1 160
2 200
3 350
4 395
5 432
6 440
7 442
8 444

View 8 Replies View Related

Add Overtime Hours Bases On Reason Code And Whether Cash Or Time

May 31, 2013

How can I total overtime based on a "Reason Code" and whether the employee chose "Cash" or "Time" compensation?

View 2 Replies View Related

Formula To Have Spreadsheet 2 Pick Items From Pass / Fail Column On Spreadsheet 1

Jan 23, 2012

Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?

View 4 Replies View Related

Multi-Spreadsheet Formula Down To Populate The Other Cells In The Total Spreadsheet

Jan 4, 2010

I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?

The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:

='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)

The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.

View 4 Replies View Related

Select Data From Spreadsheet Column If Condition In 2nd Spreadsheet At Same Time Is Met

Aug 11, 2013

I have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet.

files: twilight sheet squirrel temperature

View 6 Replies View Related

Spreadsheet That I Have A Lot Of Macros That Are Attached To A Customized Toolbar Saved In The Same Spreadsheet

Jun 26, 2008

I have a spreadsheet that I have a lot of macros that are attached to a customized toolbar saved in the same spreadsheet. I saved this is a read-only file. When I open as read-only and run my macros (testing), I save it as another file. When I then open the "template" to do the same thing, the toolbar/buttons now reference the file I previously saved as something else. Help please? Is there a macro that would delete all macros before saving the file as something else?

View 9 Replies View Related

Merge Files? (add Data From One Spreadsheet To The Appropriate Places On Another Spreadsheet)

Feb 6, 2009

Often I need to add data from one spreadsheet to the appropriate places on another spreadsheet. For example:

Sheet A has 10,000 records with these fields: id#, name, address, place of employment.

Sheet B has 5,000 records these fields: id#, GPA, college major, type of degree.

Some of the records in B contain information for the same id#'s as sheet A. I want to add this information together so that a Sheet C will have these fields: id#, name, address, place of employment, GPA, college major, type of degree.

View 3 Replies View Related

Copy Certain Cells From 1 Spreadsheet To Other Spreadsheet Depending On Condition

Jan 13, 2010

I have noticed that the basic problem I have is a common one on this forum with different varibles for different people. I have attached a dummy copy of the spreadsheet that I am using.

I need to copy cell information for one spreadsheet to one of 2 other spreadsheets depending on a dropbox condition. The master spreadsheet is the Issues spreadsheet, and depending on whether the user chooses Transferred Complaints or Transferred Offences (in Column K) I need to transfer certain cells to the Complaints or Offences spreadsheets.

The information I need to transfer from Issues is: .....

View 13 Replies View Related

Get Totals From One Sheet In Spreadsheet To Show On Another Spreadsheet?

Apr 7, 2014

I have 2 sheets in my excel spreadsheet. One tracks data for a number of projects five different employees are working on. The other sheet is where I want to total up the number of minutes each employee has worked on their individual projects. I tried writing an IF statement like below but I am only getting the total in the first field even if the employee's name is not Employee 1....

[Code] .....

How I can write this so their totals show up in the correct row?

View 3 Replies View Related

How To Auto Populate From Main Spreadsheet To New Spreadsheet

Mar 11, 2012

I have a Main Customer Spreadsheet. I want to Auto Populate FROM the Main Customer Spreadsheet to a New Spreadsheet. I want to be able to key in a customer name on the New Spreadsheet and take the info for that customer from the MAIN Spreadsheet and fill in the blanks. I need to be able to do this several times a day.

View 3 Replies View Related

Spreadsheet To Note Book And Back To Spreadsheet

Apr 14, 2007

It also renames the CommanBarPopop with the new filename.This allows the user to open both Projectworkbooks/files (If required) and load each CommandBarPopup for different filenames .Therefor opening the Userforms and worksheets for the CommandBarPopup clicked ...

View 9 Replies View Related

Compare Before Spreadsheet Data To After Spreadsheet

Apr 2, 2014

Wondering if there is an easy way to compare 2 spreadsheets that should have identical data on them? The first spreadsheet (Before) has the output data from 'before' a code fix was applied. The second spreadsheet (After) has the output data from 'after' a code fix was applied. The spreadsheets have 7 columns of data and almost 500 rows.

I've already copied the data from the source datasets provided by my IT folks into Notepad (.txt) files and then used Excel to open them as fixed width spreadsheets. I have 1 workbook with 1 spreadsheet with 'before' data. And, I have 1 workbook with 1 spreadsheet 'after' data. And, I have another workbook that contains both worksheets. So, I'm ready to go whenever I get hints of what to do next. :-)

I need to be able to show my client that we did not impact the data with the code fix that was applied. I want to be able to show my client contacts (business folks) an end result via Excel that confirms that I actually compared the 2 sheets and there were no differences. In other words....I can't just show them a formula with '0' as it end result (even tho that's basically what I'm trying to prove).

View 6 Replies View Related

Importing Data From Spreadsheet To Spreadsheet

Apr 24, 2006

I need to write a macro that will import data contained in another spreadsheet, but am unsure how to do this. I have several (about 15) spreadsheets that contain data. I need to import key bits of this data into one central spreadsheet that will be used for reporting purposes. I only need 2 cells worth (values) from each source spreadsheet, to be pasted into the destination spreadsheet, into designated cells.

The source spreadsheets are usually closed down and kept on a file server, which my PC has access to. Ideally I want to activate this macro with a control button - i.e. I press the button once and the macro goes off and collects/updates each field with the latest data stored in each of the source spreadsheets.

View 7 Replies View Related

Auto Copying Text (Not Data) From Cell In One Spreadsheet To Another Spreadsheet Cell?

Nov 4, 2012

I wish to Automatically copy the TEXT that is written from Spreadsheet 1 cells D5 to F5 to Spreadsheet 2 cells F5 to J5 .... a similar range of cells.

Is there a formula I can use or do I need to venture into the programming side of things.

View 7 Replies View Related

Open Second Spreadsheet Minimized On Open Of Spreadsheet + More

Oct 27, 2009

I have a spreadsheet that on open needs to open a 2nd spreadsheet minimised and delete 2 named sheets in the 2nd spreadsheet.

On close of the first spreadsheet it should then copy 2 sheets with the same name from itself into the 2nd spreadsheet.

This is to start a spreadsheet that will track project risks and issues that I will post further details for as other functions are required.

View 9 Replies View Related

Tab To Tab In A Spreadsheet?

Jan 24, 2014

I have a master tab in a spreadsheet where it contains account #'s (A); their respective account name (B); sales rep (c); region (d) & notes on the account(e)

I also have tabs for each individual region on the spreadsheet.

I was wondering that once I update any of the cells on the individual region tabs...if that could automatically update on the master tab, where that account etc is located...or would it be easier to update the master tab with notes; change in rep, etc...and have that automatically fill over on the individual tabs?

View 5 Replies View Related

VBA To Sum Each And Every Row On Spreadsheet

Jan 28, 2014

The intent of this sub is to sum each and every row on a spreadsheet. However, it keeps summing the same row (Row 1) for as many rows that are on the spreadsheet.

Code:

Sub SumAllColumnsInAllRows()
Application.ScreenUpdating = False
Dim bottomA As Long
bottomA = Range("A" & Rows.Count).End(xlUp).Row
Dim rng As Range
Dim lColumn As Long
For Each rng In Range("A1:A" & bottomA)

[Code]...

correct this so that the code sums each row?

View 2 Replies View Related

Filtering 2nd Row On Spreadsheet Instead Of 1st Row

Mar 11, 2014

I have a spreadsheet with Row 1 as the header and Row 2 as sub-headers. I want to filter automatically to 2nd row, but whenever I press on Ctrl+Shift+L, I have the first column filtered. I have another spreadsheet that whenever I press Ctrl+Shift+L, it automatically filters the 2nd row instead. I'm not pretty sure what's the difference. I have my spreadsheet frozen on cell B3 (not sure if it has something to do with freezing cells. I know I can select 2nd row before pressing on Ctrl+Shift+L, but is there some other way to have this automated?

View 2 Replies View Related

Formula That Goes Into Every Tab In Each Spreadsheet?

Jan 17, 2014

I have a set of spreadsheets (Circa 50) that each have around 50 tabs of information on, one set of spreadsheets are from 2012 and another are from 2013.... basically i need to write a formula that goes in to every tab in each spread sheet and basically tells me the entries that are in the 2012 sheets but do not appear in the 2013 sheets. The results of this are the most important thing. All of the names that i need to search are in Column A of each spreadsheet and tab .

View 3 Replies View Related

Get Info From One Spreadsheet To Another

Feb 27, 2007

I want to be able to connect two spreedsheets.

I have a "surplus inventory" sheet and a "inventory shipped" sheet. How can I input how much I shipped in the "inventory shipped" sheet and make it take away from my "surplus inventory" sheet automatiaclly?

View 9 Replies View Related

Add A User Name To Spreadsheet

May 6, 2007

I have designed a spreadsheet in the office where I work, and think it would be quite flashy (gimiky) if I could have the spreadsheet show the name of the user logged on.

At the minute if I try and access a sheet currently open by another user it will come up with the standard message 'workbook1.xls is currently in use by xxxxx' then you open a read only copy until they have finished.

I was thinking there must be some way of importing this information to excel to display something like 'welcome (xxxxxxxx)(user currently logged on)'

The other part of my problem is that the excel sheet will only display the network uername of the user, but I have seen spreadsheets that cross reference this to the Microsoft Outlook global address list on the MS Exchange server, and display the users actual name.

In a nutshell, I just want to know is there anyway the excel spreadsheet can display the username currently logged into the shared spreadsheet.

View 13 Replies View Related

Add A Counter To A Spreadsheet

Mar 28, 2008

we have a spreadsheet which we encourage our employees to use but not sure if they are or not. is it possible to put a counter onto the spreadsheet so we can see how many times it is being opened

View 10 Replies View Related

Spreadsheet Sorting..

Nov 14, 2008

I'm trying to sort this spread sheet so that each email address will have its own column assigned to it. Ie jaz@hello.com will alway be column C and harry@hello.com will always be in D. The problem is I've around 1200 rows of infor and the email addresses are all over the place..

View 5 Replies View Related

Spreadsheet Naming

Aug 24, 2009

We utilize a shared spreadsheet in our department named "Arrangement Requests".
From time to time and seemingly very random the sheet renames itself. When this happens anyone adding information to it becomes unable to save since the original "no longer exists".

This causes problems because everything we do is in real time and expected to be viewed by all as soon as save has been indicated.

View 13 Replies View Related

Spreadsheet Links

Sep 9, 2009

I've created a spreadsheet which pulls data from about 5 other spreadsheets via links in the formula.

Unfortunately I am having trouble with these updating. I have to open the 5 individual spreadsheets in order for the data to pull across otherwise I get VALUE in every cell in the amalgamated spreadsheet.

Any thoughts why this should be the case? I've tried it in the past and it's always worked fine.

View 11 Replies View Related

Print A Spreadsheet.

Nov 2, 2009

I wanna print a spreadsheet. but i want the first 5 columns to be the header on every page.

View 2 Replies View Related

ADVERTISEMENT