I am currently creating a new system or coursework. I decided to create a balance sheet using Excel. I was wondering if it is possible to have a cell whereby you can enter a number for example £10 and it will add to a Total balance cell. Once it is added the cell where £10 was entered will reset itself back to £0 but the total will remain £10 in the total balance cell. I think its called continuing total or something but ive searched for ages and cannot find any info. If done it will allow the user to enter any money coming into the business at any given period of time and will add to a total.
need a formula that will give me a balance at the end, needs 2 columns with charges and credits and it allways give correct balance total on last column. have not used Excel for long time and forgot.
For the small database in my example workbook, I would like to apply credits earned at a later date to the oldest charges and create a "To Date" balance. After creating the "To Date" balance, I would like to select the smallest "To Date" balance as a way to identify the first charge that has no payment. Please see the attached for more clarity and additional information. The last column shows the desired results.
I am creating a balance sheet for my company that seems pretty simple (date, transaction type, amount, and balance), except for one thing: 10 days before every bill is due (and before every purchase is made), expense amounts are supposed to show up in the balance sheet as a deduction from the current balance, so that the sheet projects what the balance will be in 10 days. Do I need a macro for that? If so, what would it look like? It needs to be blank if it's more than 10 days out, and then suddenly appear in the balance sheet once it's 10 days away from due date.
I have is a cell with a begining balance and a cell witha current balance.
I also have a column in which each expenditure will be placed ina cell in that column in sequential order.
What i want to do is take the value in the first cell of the expenditure column and subtract it from the begining balance and the place the result in the current balance.
But after the first cell i will need to subtract each value from its previous value to continue counting down so to speak.
I am at a loss on how to move the last value which will also be the lowest value in the column to the cueent balance cell.
I need to create a sheet that will take payments away from a starting balance. The catch is that I don't know the starting balance. The interest is compounded annually at 3% and each withdrawl is different. My sheet needs to show the interest earned in each year and the account balance like a running sheet, after lets say 5 years there needs to be no money left.
I want to zero out or blank out my running balance in the columns where I do not have an amount entered. So instead of having my running balance total run all the way down the spreadsheet, I want it to be blank at the first cell where I have not entered in a new bill. I have attached an example of what I am trying to do.
I am trying to do a MS Query from another sheet in the same workbook
I created a new data source using Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
Tried read only checked and unchecked
Workbook is a .xlxs but same error with .xlsm workbook Workbook is on the desktop
I receive the below error when trying to edit a MS Query table by right clicking Table>Edit Query
ODBC Excel Driver Login Failed
The Microsoft Access database engine cannot open or write to the file "(unknown)". It is already opened exclusively by another user, or you need permission to view and write its data.
I am using below code to delete the sheet from workbook.
For iTemp1 = Workbooks.Item(funEach.Name).Sheets.Count To 1 Step -1 Sheets(iTemp1).Select If UCase(Sheets(iTemp1).Name) "TEMPLATE" And UCase(Sheets(iTemp1).Name) "WORKING" Then Sheets(iTemp1).Delete End If Next
How can i avoid the below popup..
-Data may exist in the sheet(s) selected for delettion. To permanently delete the data press delete.
I have a web query that returns a value to the first column of the row.
I have unlocked the cell (Format -> Cells, Protection Tab, uncheck the Locked check box).
If I run my web query, it works fine. If I then lock the worksheet, the web query fails, even though the cell that I am returning the web query to is not locked. Code below:
Sub create_pay_proposal(pUserId As Range, pRespID As Range, pWorksheet As Worksheet, pRange As Range, pParms As String) Dim strURL As String
'On Error GoTo WebConnError 'Error handling if unable to connect to database/URL
I have an Access table which has following fields:
ScanDate Number DataType Type Number Type1 Number Type2 Number BatchNo Number Cases Number Pages Number
Now I have a useform in Excel so that when the user selects the Date from the combobox then it should check for total batch numbers (Count(BatchNo)), total cases (Sum(Cases)),Total Pages(Sum(Pages)) where ScanDate= Date from the combobox group by Queue Number. The Queue Number consist of Type+Type1+Type2.
See attached the Excelsheet where the data should be populated to. WBCount.xls‎
I have a protected worksheet containing an SQL query. I have two cells on the worksheet unlocked so that users can enter fresh parameters to feed the query. Because I have to use MS Query to interrogate our SQL server (current IT decree), the query won't support parameters directly when the query cannot be represented graphically - so I have to manually edit the sql command text and then refresh the query - or at least that was why I wrote the code below
The problem: My macro won't unprotect the sheet before it calls the SQL.it therefore won't update the sheet to detail the DSN, command text and number of parameters (latter should be 0) - info just used to verify queryit won't run the query / return the data as the sheet is still protected Comment: I have had to remove sheet protection in order to allow the sql to execute and return data. If protection is removed, the query does execute and return data ( changes in the "parameter" cell contents do cause the query to be correctly modified) I could comment out the debug info but the query still doesn't return data if the sheet is protected.
Activesheet And worksheets(ndx) where ndx has been defined As ActiveSheet.Name I apologise If my post Is difficult To follow - especially the code.
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this WEB 1 http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.
I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run. I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
I have a running balance that works beautifully in my spreadsheet, but I cannot get it to display my balance on a line above my spreadsheet. I have attached a copy of my spreadsheet to make understanding my question easier. I have a sell price that will remain the same and as the bill is paid the balance should work itself down to $0. Currently I have three deductions in my spreadsheet but the balance only shows the first deduction.
See attached workbook which is a stock order workbook with a summary re-order sheet -
The problem I have is that as the running balance effects the re-order column the summary re-order sheet will re-order from a the date that stock is needed onwards until someone types in stock recieved to get the balance right.
I need another condition within the summary sheet sumproduct formula so that the order will only go through once and not be repeated until a new figure is added into the number issued column on a future date.
I owe 15462 in the bank, currency dont matter here, that is what I owe right now, but I want to have a cell in the frontpage with the amount left, so can I make a line called =remaining-each month
the amount should then each month be substracted from the new month and so on, until the amount is 0
can this be done?
the second page in the spreadsheet has a post with monthly pays to the bank ...
I have to balance workload equally for everyone (Name: abc, xyz, mno) and New Orders must be shared or distributed equally for every person. New Orders must be shared in such a way that every person's percentage share must be made equal by distributing or sharing New Orders. Find an attachment named issue.xls
I am using excel 2007. I am working on an account register. I am using my spreadsheet to track when deposits and withdraws are made, when bills are paid, and when check have cleared my bank. I am using condintional formating to black out rows when bills have been paid, checks have cleare, and when withdraws have been made.
I only have one issue. I am tracking my current balance at the bottom of my spreadsheet I want to be able to go back and delete all of blacked out rows once a week without losing the value that was contained within them thus leaving the current balance unchanged.
i.e
On Monday the 17 check number 2207 clears my bank so in comumn “C” enter the word “cleared” in row 210 the row then turns black and everything is fine. My current balance now is $1,678.25, at the end of the month I want to be able to go back and delete all of the blacked out rows. So I go to row 210 and remove the row but now my balance changes back to what it was before that value was entered.
I want to know is there is a way to leave the balance unchanged even after I have deleted that row?
I need to sum the values of a column based on multiple criteria. i.e. I would like to sum the column "End Balance" based on both "Company"='B' and the "GL CODE" > 4404...... and < 4406.....
I would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month: Select Creditor: Enter Payment: New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
