Home |
Search |
Today's Posts |
#1
|
|||
|
|||
cell formatting
I have two questions which somewhere down here have probably already been
asked and answered, but the truth is I just don't have the time to look, presently. (1) I have my functions in the "balance" column so that it reads whether the last entry is a debit or a credit, and adds/subtracts as needed. However, below the latest balance figure, how do I get the function to just remain BLANK if there has not been a new debit or credit entry, rather than the big line of $0.00's??? (2) How do I create a system where it looks for a certain designation, such as "Company A" and puts the balance for that company separately in it's own section, in addition to the general ledger? I haven't determined whether to have a separate sheet for each item or just put them in different places on the main spreadsheet for those companies. Please explain the difference in doing it each way, please. Thank you very much, Jambora |
#2
|
|||
|
|||
"MSN" wrote
.... (1) I have my functions in the "balance" column so that it reads whether the last entry is a debit or a credit, and adds/subtracts as needed. However, below the latest balance figure, how do I get the function to just remain BLANK if there has not been a new debit or credit entry, rather than the big line of $0.00's??? One way is to suppress extraneous zeros from showing in the sheet via: Click Tools Options View tab Uncheck "Zero values" OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#3
|
|||
|
|||
This works well as long as the balance remains zero, which frankly is not
long. For instance, now, the balance of debits and credits is $2,015.16. It now repeats this value down to the end of where my current cell formatting ends, instead of what I would like to see is the last entry showing the last balance, then the balance below that to the end be empty cells, as they are in the debit and credit columns, which of course are not functions. Close, but only half a cigar....:) Jambora "Max" wrote in message ... "MSN" wrote ... (1) I have my functions in the "balance" column so that it reads whether the last entry is a debit or a credit, and adds/subtracts as needed. However, below the latest balance figure, how do I get the function to just remain BLANK if there has not been a new debit or credit entry, rather than the big line of $0.00's??? One way is to suppress extraneous zeros from showing in the sheet via: Click Tools Options View tab Uncheck "Zero values" OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
I need something like SUM(H20-F21+G21)IF(F21ORG21<0)
Can you see what I'm getting at? If so please correct any syntax errors. Thanx. Jam "Max" wrote in message ... "MSN" wrote ... (1) I have my functions in the "balance" column so that it reads whether the last entry is a debit or a credit, and adds/subtracts as needed. However, below the latest balance figure, how do I get the function to just remain BLANK if there has not been a new debit or credit entry, rather than the big line of $0.00's??? One way is to suppress extraneous zeros from showing in the sheet via: Click Tools Options View tab Uncheck "Zero values" OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
I don't know how these newsgroups deal with attachments such as this .xls
attachment so you can see what I'm trying to do, but here it is if it works. If it doesn't work simply that way, how do I get something printed on an email to the group, but in excel form? Any way? Jam "Max" wrote in message ... "MSN" wrote ... (1) I have my functions in the "balance" column so that it reads whether the last entry is a debit or a credit, and adds/subtracts as needed. However, below the latest balance figure, how do I get the function to just remain BLANK if there has not been a new debit or credit entry, rather than the big line of $0.00's??? One way is to suppress extraneous zeros from showing in the sheet via: Click Tools Options View tab Uncheck "Zero values" OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
Here's the sample file with revised formulas for col D ("Balance") in Sheet1
http://flypicture.com/p.cfm?id=54072 (Right-click on the link: "Download File" at the top in the page, just above the ads) File: Jambora_MonthlyMoneyAccounting_wksht.xls ---------------- Revised formulas for col D --------------- Put in D4: =C4 Put in D5: =IF(AND(B5="",C5=""),0,D4-B5+C5) Copy D5 down to D52 Then just suppress extraneous zeros from showing in the sheet via: Click Tools Options View tab Uncheck "Zero values" OK This way will achieve the blank effect you want for "new" rows w/o any debit or credit, until the inputs are made -- Btw, in future please *do not* post any attachments to the newsgroups -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jambora" wrote in message ... This works well as long as the balance remains zero, which frankly is not long. For instance, now, the balance of debits and credits is $2,015.16. It now repeats this value down to the end of where my current cell formatting ends, instead of what I would like to see is the last entry showing the last balance, then the balance below that to the end be empty cells, as they are in the debit and credit columns, which of course are not functions. Close, but only half a cigar....:) Jambora |
#7
|
|||
|
|||
See response to your earlier post. I've provided a link to a sample file
with the revised formulas for col D ("Balance"), using a construct along the lines of, e.g.: In D5: =IF(AND(B5="",C5=""),0,D4-B5+C5) D5 copied down And zero values display switched off -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jambora" wrote in message ... I need something like SUM(H20-F21+G21)IF(F21ORG21<0) Can you see what I'm getting at? If so please correct any syntax errors. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
up to 7 functions? | Excel Worksheet Functions | |||
Special cell formatting | Excel Discussion (Misc queries) | |||
Formatting a cell to populate on a certain date | Excel Worksheet Functions | |||
Conditional Formatting Cell and Row | Excel Worksheet Functions | |||
Formatting dates in the future | Excel Worksheet Functions |