Sum For A Register For Cleared Items Only
Jul 22, 2009
I'm sure this has an easy solution, but I'm having a problem putting it into a working formula. Column I lists checks numbers. Column J contains the amount of the check. Column K contains a "Y" when the check clears the account, otherwise it's left blank until the item clears. Cell O2 is where I want the balance formula.
I want O2 to look at Column K & then sum the amounts from Column J where the corresponding Column K is blank. If it were just one cell, it's a pretty easy if(k4="Y","", j4), right? But since it's a range of cells, not sure how to tell it to look down the range for all uncleared checks and put the sum in one cell.
View 2 Replies
ADVERTISEMENT
Nov 11, 2011
I have a register of items that sometimes get updated, the register is stored as
From row 9:how ever many hundreds of items we have
Columns A:E
Data about the item that does not change with new revisions
Column F
Current Revision
Columns G:Z
Revisions in order (ie if there had been 8 revisions
'-, A, B, C, D, E, F, G) (with higher revisions left blank)
each of which is hyperlinked to the relevant file for that revision.
What I would like is a formula in Column F that will print
1) the letter of the highest revision (Revisions are always sequential)
2) Will hyperlink the column F to the correct revision...
I used to know how to do the first half of this formula but the spreadsheet got tinkered with and now I can't remember how to do it.
View 3 Replies
View Related
Nov 27, 2006
I have a spreadsheet that I have setup as a "Check Register". (I actually downloaded it from the Microsoft website.) I have modified it a little to have a small budget for the account at the top of the page, and I want to automate this a bit.
So, here is the question.
As I make the entries on the rows, how can I automatically update a cell with the result (the account balance) as I go?
Here is the formula I am using:
=IF(AND(ISBLANK(F20),ISBLANK(G20)),"",H19-F20+G20)
On my sheet I want G4 to display the result, as I go.
View 9 Replies
View Related
Nov 3, 2009
I have a worksheet that I have removed a good number of hyperlinks, using the remove hyperlink function. My problem is that every so often, the hyperlinks are back on the sheet, without seeing any wording for the hyperlink.
Some place, a long time ago, I saw that there was something in the program that either stopped this from happening or turned the function off or a way of restoring removed hyperlinks. Today, I can't find it.
If I run my cursor over the spreadsheet, even over blank cells, I will get the evil finger of fate (so to speak) indicating that there is a hyperlink in that cell and yes, there is. If I click the cell, the site will be activated.
How do I keep these removed hyperlinks from returning.
I've also noticed that at anytime, while working on the sheet, everything flickers just as if the links are being re-populated. After a second or two the flickering stops and yes, the links are back.
View 12 Replies
View Related
Jan 1, 2014
I have a macro that runs with worksheet_change if I delete a cell. It does not run if I just clear contents. Is it possible to have the macro run when I clear contents of a cell?
View 4 Replies
View Related
Aug 18, 2006
I have a rather large userform with numerous textboxes and comboboxes. Data entered into the text and combo boxes is written to the next empty row of a sheet. As the data to be entered into the next row may not "all" vary from the last I would like to have the data I have last selected or entered remain in the textbox or comboboxes for the next. I attach an example for a better explaination.
View 6 Replies
View Related
Jul 14, 2008
i have created a workbook that looks for files and creates hyperlink within a worksheet to these files, to help the user I have included the title from the document properties.
To get the document properties i used the DSOFile DLL from Microsoft and added it in to my project and it works fine.
My problem is that i wish to distribute this to a large group of users within the workplace and I need a method of checking if the DLL is added in and registered, if not can it be added in and registered on the fly.
I have tried placing the DLL on a common network drive, but this could be further complicated b some users taking laptops out of office but hopefully the microsoft synch of files might take care of this.
View 9 Replies
View Related
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
Nov 2, 2009
I need help with a code to detect deletion of a cell's value.
In cell I10, a user can choose one of six items from a data validation list. Right now, I have code in there to pop up a message if the user selects value "X" from cells F6:F10 and G6:G10, but cell I10 does not have value "A", "B" or "C" already selected.
What I want, is if cells F6:F10 and G6:G10 have value "X" in them, and the user deletes values "A", "B" or "C" from I10 or changes it in any way after it has been filled, then to have a message box pop up.
View 7 Replies
View Related
Aug 23, 2006
I have a legacy Excel VB application with multiple sheets. When the
users tab from sheetA to sheetB the paste buffer is cleared. This might
be due to the fact that sheetB has Worksheet_Activate processing that
does some validation and refreshes a pivot table (on sheetB).
Is there a way to reserve and restore the contents of the paste buffer,
and make sure the user can actually paste values from sheetA to sheetB
(using either Ctrl-V or Edit-Paste)?
View 3 Replies
View Related
Aug 15, 2014
Working on a Checkbook Register/Monthly Budget.In column A I have the Check # or Type of Transaction, in column D is Debit, in column F is Credit, and in column G is Balance. I am writing the Check #, Debit, Elect., or Bill in column A.What I want to do is write a formula in column H to find all the Bills in column A and add all there Debit in column D together. I don't know if I change the word Bill to a code # if that would work or not. If it does, could I use a range of number of different bills like 200-299.
Example: If (A2-A50) = "Bill" or (200-299) add the same row # in column D for a total sum.Would like to use the code # if possible so I can budget the Bills individually if possible.
Using the Formula: =IF(ISBLANK(C3),"",G2-D3+F3) in column G for Balance, don't want to mess with this formula.
View 6 Replies
View Related
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
Sep 23, 2002
I have a column of blank cells m11 throught m22. Each cell possibly holds a value of a check that has been written but has not cleared.
One cell on my sheet contains a difference of my previous bank balance and my new bank balance after an unknown number of checks have cleared.
My bank balance was $500.00 dollars on 9/22/2002
Checks that have not cleared =
m11 = $5.00
m15 = $15.00
m16 = $35.00
m20 = $6.00
-----
My account balance is now $489 dollars on 9/28/2002
$11.00 dollars is the difference.
It's obvious that checks $6.00 and 5.00 are the checks that have cleared!
What I am trying to do is find out a simple way to sum every number that is listed in m11:m22 in every sum combination possible.
Then display the combonation of numbers that = the difference when summed together
I know the long way to do this.........................
View 9 Replies
View Related
Feb 19, 2014
I'm not sure if this is impossible (surely not) or if I'm just Googling the wrong terms (most likely). I have a sheet that we use to record staff shifts and when they take their lunch breaks. By default most of our employees have a 1 hour lunch and they take it 4 hours after they start. So far so good, I've set up a formula to work out what time their lunch would start and end based on this.
N237: =IF(AND(L237>0,L237<>"NWD"),SUM(L237+(1/6)),0) - Lunch start 4 hours after start time in L237 (NWD = Non working day)
M237: =IF(N237>0,SUM(N237+(1/24)),0) - Lunch ends 1 hour after lunch start time in N237
However, not everybody follows the default - some people take lunch later, or don't take as long due to working shorter hours.
What I'd like to do is allow users of the sheet to overtype the times generated by the formulas when applicable, but if they delete their own custom value for lunch breaks I would like the formulas to re-appear and thereby return lunch times to the default values.
I could do this through VBA with a button to look for blank cells, but I would prefer it to happen automatically. Is this possible and if so what code would I need to make the magic happen?
I should maybe have mentioned that this sheet has space for 250 names and an area for each day of the week, so these formulas would appear in 1,250 different places
View 4 Replies
View Related
Apr 25, 2014
I'm trying to use the Worksheet_Change event to replace all cleared cells with a default formula. This allows users to override the default, or restore the default when they need to:
If Not Intersect(Target, Range("Data")) Is Nothing And Target.Value = "" Then
Range("DefaultFormula").Copy
ActiveSheet.Paste
ActiveSheet.Application.CutCopyMode = False
End If
Here are the results with various different regions selected:
Single cell selected and cleared -- works. Multiple cells selected and cleared -- doesn't work if the first area selected contains more than one cell. Otherwise, it works, no matter how many other areas are selected.
Example1: Selected range is (D2:E2, G2) in that order, it does not copy the formula into the cleared cells. Example2: Selected range is (G2, D2:E2) in that order, the code does copy the formula into all the cleared cells as desired.
The "IF" condition is not TRUE when the initial area has more than one cell.
View 6 Replies
View Related
Sep 25, 2013
how to search a defined range (using namned range and offset to last non empty row) and register row number for the first occurance of each ID using VBA. I made an example file where I need to search column A (search range using namned range "ID" and offset to last non empty row) and register rownumber for the first occurance of each ID in Column C.
example.xlsm
View 1 Replies
View Related
Apr 6, 2014
I need to add over 100 rows on the top of my check register worksheet, is there any way to do automatically?
View 4 Replies
View Related
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
Apr 3, 2013
I've been looking for a free checkbook register template that will automatically calculate the balance as you add or subtract various amounts.
I found one which seemed to be just what I wanted but once I had reached the end of the rows for the worksheet and tried to add more rows the automatic feature quit working.
I found another Checkbook Register which seemed even better than the first one as it had a notation at the end of the rows.
It said to " Insert new rows above this point" and it had the last row greyed out.
I tried adding a new row and tested it out by adding some amount but it did not continue with the automatic calculation either.
I'm using MS Excel for Mac 2011
View 4 Replies
View Related
Mar 18, 2008
I am trying to create a way to easily maintain a list of outstanding checks. I am able to import cleared checks into the spread sheet but cannot find a way to compare the two lists and then delete the cleared checks off the original list.
View 2 Replies
View Related
Jan 28, 2014
I'm wanting to make a checkbook register. That part I can do (I am totally an Excel idiot, but I'm fairly decent with Google and I couldn't seem to find anything about this), but I'm wondering if auto-drafts are possible. What I'd like to do is have a table of regular auto-withdrawals (or deposits, for that matter) and then on the appropriate day have them auto enter into the register. Ideally, I'd like them to enter 2-3 days in advance, or even at the beginning of the month.
For example: Netflix on the 1st, Life Insurance on the 12th, Auto Insurance on the 15th, etc.
View 2 Replies
View Related
Jun 29, 2006
I'm having a problem in a single line of code in which I'm using a Range Object to essentially select an area of cells whose contents I want cleared.
Here is the
Sheets(Left(c.Value, Len(c.Value) - 1)).Range(Cells(4, 2), " &:& ", Cells(Rows.Count, "b").End(xlUp).Offset(0, 9).Select).ClearContents
I want the range part to evaluate as (b4:whatever the last cell is before the first blank row).
View 9 Replies
View Related
Apr 27, 2013
Excel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.
This resolves the issue perfectly, and I can operate the report exactly as I would expect.
Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.
Screen shot of after re-opening, no changes:
Report looks terrible (not real data):
View 2 Replies
View Related
Aug 13, 2014
I have a spreadsheet that lists a set of actions and the days they need to be completed.
The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.
I have a dashboard on a separate worksheet, showing if the item is complete and the due date.
Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.
I have been going through my books and trying to solve this, but I run into a different issues.
Even if for now If I can get the main worksheet to display the color conditional formatting that would work
View 2 Replies
View Related
Mar 27, 2014
I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
View 6 Replies
View Related
Apr 8, 2008
i have a worksheet which is a form which feeds into another worksheet which acts as a database. i have a button which clears the form based on code which is essentially "clear contents".
the problem is i have a few cells where i would like to retain the formulas. i tried to do this with custom in validation but this did not work. i also tried to enter the code directly into the worksheet but this didnt work either (my skills are limited..). i am avoiding using protect sheet bc that in turn will affect many of my other buttons. is there something i am overlooking?
View 9 Replies
View Related
Jul 2, 2014
So i've been trying to do this invoice/inventory/client , I couldn't find a way to somehow register the whole invoice as it is, so i can later print/visualize it if i lost the copy or something.
It would be best if it just stayed stored in a variable and not always visible, and only appeared if i wanted to check/print it.
The whole invoice would be from A1 to F30
Excel 2010 btw
View 5 Replies
View Related
Dec 26, 2013
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
View 3 Replies
View Related
May 23, 2006
If i select 3 items from a list 20 items to be show on the cube this is fine and works however if i then add another item to the list i now have 21 items and for some reason it also adds itself to my 3 selected items to become 4 items. Is there anyway I can get it to be added to the 20 items and not to the 3 items..
View 4 Replies
View Related
Apr 13, 2012
I've run into a problem detecting empty cells. I'd like to search down a column of numbers and count the cells that contain numbers and ignore the cells that are empty. If the cells were made empty of their data by manually using the delete key before hand then my macro works fine. The problem is if the cells were manually made empty using the space bar to clear their contents.. then the macro seems unable to detect them as empty.
I have tried "", IsEmpty, and a few other things and nothing detects the cell as empty unless the delete key is used rather than the space bar. For this macro I really need the user to be able to clear values beforehand using the space bar, and to have those cleared cells be detected as empty cells.
View 6 Replies
View Related
