|
Post by gadgets88 on Jul 25, 2007 11:44:38 GMT 8
I have a file, let's call it SALARY.XLS contain 12 worksheets, representing 1 month per worksheet.
Each worksheet contains employee information: the number of days (absences), the number of hours worked, the salary rate, the SSS/HDMF/WHtax deductions, vale, etc.
The problem is that the computation of each month sometimes overlap with the other months. My question is: how do I refer to a cell that resides on a different worksheet?
Ex. I want to deduct the July SSS contribution (data located in the July worksheet) in the August computation (located in the August worksheet). Any Experts in the house?
Thank you!
|
|
|
Post by whoopi on Jul 25, 2007 11:49:15 GMT 8
GADGETS, go to the cell in the Aug worksheet where you want the computation to appear, enter the formula you want preceded by the equal sign "=", example:
you want to deduct the July total (subtrahend) from the August total (minuend).
=Minuend-Subtrahend
your minuend can be entered manually as a figure,
=200-Subtrahend
or if the minuend is already in a cell (e.g., in B20), just click on that cell after entering the = sign
=(click on)B20-Subtrahend
now to enter the subtrahend which is in the July worksheet (e.g., in C20 of the July worksheet), just go to the July worksheet and click on C20 and hit ENTER. you will see this formula on your Formula Bar in the August worksheet
=B20-'July Worksheet'!C20
and on the cell itself you will see the result (or the difference, since this is a subtraction).
|
|
|
Post by Ben Dover on Jul 25, 2007 12:21:51 GMT 8
wud you be willing to take a look into non-expert's answer?
you can also start a formula with the plus (+) sign etc...pinaka madali is to test the procedure first...choose any cell from worksheet A then hit the plus (+) or equal (=) button..click on the worksheet B...choose any cell which contains, let's say a number... click on it then hit (enter).
when you get the idea how its done, you can perform more complex formulas using the same principle.
|
|
|
Post by shuzzs on Jul 25, 2007 13:12:41 GMT 8
Normally, to enter a formula, you should start with a = sign.
To refer to cells in other worksheets, just precede the cell location with the worksheet name followed by the ! (exclamation) sign.
Example: =Sheet1!A1+Sheet2!A1
This adds the value in cell A1 in worksheet Sheet1 with the value of cell A1 in worksheet Sheet2.
You may also use the syntax that whoopi has described: =B20-'July Worksheet'!C20
wherein you should enclose a worksheet name that has space(s) (i.e. July Worksheet) with single quotes (').
HTH.
|
|
|
Post by gadgets88 on Jul 25, 2007 16:50:16 GMT 8
Another question please. Each worksheet contains data performance of a particular bank. The data include the interest rate that fluctuates every month. After one year, I would like to compare all banks, say 10 banks (that's 10 worksheets). How do I make a table to show how each bank performed in each month. Example: Worksheet 1 contains Metrobank Worksheet 2 contains BPI and so forth. Each worksheet resembles what a passbook looks like. You start with money on bank then they put the maturity date with the corresponding gross interest rate. Every maturity day, the passbook gets updated and rolled forward. At the end of the year, I would like to create a chart to show how each bank performed. Can anybody help me do this? Sorry for asking too many questions. Recently decided to grope my way through Excel. The way business is going nowadays, it pays to do everything myself. Can't afford to hire that hotbabe secretary to do the math for me! Would appreciate if somebody can refer a useful reference book. Thanks! P.S. Waitaminnit... your responses are so clear I got it! Thank you very much! Whoopie/Tolits/Shuzzs:
|
|
|
Post by whoopi on Jul 25, 2007 18:57:14 GMT 8
GADGETS, the simplest way is to just e-mail me the file and i'll do it for you ;D
when you say you want a table to compare data, do you mean a table-table, or a chart (pie/line/bar graph)?
whichever way, first thing you do is create a new worksheet in the file so as not to mess up the other worksheets.
now if you're thinking of just a plain table, then just line up the data in the cells using the click-on-the-cell-then-ENTER trick you learned above, to transfer the contents of a cell from one place to another (or from one worksheet to another).
Example:
BANK Php DEPOSIT MATURITY DATE INTEREST RATE
Metrobank 10,000 July 31, 2007 .5% BPI 20,000 August 31, 2007 .5% BDO 30,000 December 31, 2007 1%
but if you're thinking of a chart, the table above is still useful, and the next thing you do is click on Insert tab on the top navigation bar, and select the Chart option. choose the type of chart you want. just follow the instructions on the dialog box. when it asks you for data range, click on the small icon at the end of the data range field, and you can highlight the data in your table (e.g., the Interest Rate column). hit ENTER.
then again just follow the instructions on the dialog box, fill in data there if you like (it will ask you, for example, if you want to label the chart).
you can also activate the Chart function by clicking on the Chart Wizard icon on your top navigation bar. it's the one that looks like a colorful 3D bar graph.
good luck!
|
|
|
Post by yukon on Jul 25, 2007 20:16:21 GMT 8
whoopinever knew you excelled in excel! That's excellent of you to help gadgets. Nelson, I think Roche is sending you subliminal messages through her exmaples...I'm trying to decipher it and it says please make a donation at my ff: personall accounts MetroBank Timog BPI Tomas Morato BDO West Ave. So OT and corny pero Roche thanks for sharing this...I'm so dumbo sa MS Excel. I'm only good at Microsoft Flight Simulator ;D ;D ;D Peace..very helpful indeed (serious na ako ha)
|
|
|
Post by whoopi on Jul 26, 2007 10:48:43 GMT 8
YUKON, you are wrong. my subliminal message to nelson is: hire me as your secretary! ;D i use Excel at work kase.
|
|
|
Post by yukon on Jul 27, 2007 5:49:06 GMT 8
Roche, your salary is too high. Not even MS Excel can do Nelson's computation on his worksheet for him to gauge your salary. Excel's gonna bonk out on your rate. WAPAKKKK! Hihihihihi
|
|
|
Post by gadgets88 on Jul 27, 2007 8:58:54 GMT 8
Thank you for the subliminal messages. The secretary position is not open, but the massage parlor on the 2nd floor is in need of several hot babes (it would be wonderful if knowledge in massage and excel is inclusive) ;D Thanks for the offer, Roche. I'll send the file once I know what I really want and once I get frustrated with my efforts. Don't hold your breath because I might spend some time working on it myself first, you know, trying hard muna. It is so amazing that I'm learning to bike and use EXCEL at my age. Oh well, better late than never. On 2nd thought, once I find somebody to mind the gadgets store, I'll retire and bike everyday with Fafa Rocky, Tito Arci and brad Arnel! Now that's life!
|
|
|
Post by Alphabolt on Jul 31, 2007 23:40:24 GMT 8
Thank you for the subliminal messages. The secretary position is not open, but the massage parlor on the 2nd floor is in need of several hot babes (it would be wonderful if knowledge in massage and excel is inclusive) ;D Thanks for the offer, Roche. I'll send the file once I know what I really want and once I get frustrated with my efforts. Don't hold your breath because I might spend some time working on it myself first, you know, trying hard muna. It is so amazing that I'm learning to bike and use EXCEL at my age. Oh well, better late than never. On 2nd thought, once I find somebody to mind the gadgets store, I'll retire and bike everyday with Fafa Rocky, Tito Arci and brad Arnel! Now that's life! yep i can bike with you everyday, brad Nelson just do this ...insert a row name "For A.Andal" then make a formula "=(your net income cell) * 10%" deposit that final amount to my Metrobank account every quarter...and Im all yours for biking ;D ;D ;D
|
|
|
Post by gadgets88 on Aug 1, 2007 10:50:49 GMT 8
Thanks for the company, Arnel!
All I need is a manager to make the store profitable and we're good to go!
NOW LOOKING FOR: Hot babes with massage skills, knowledge in EXCEL, and superior management and entrepreneurial skills!
|
|
|
Post by gadgets88 on Aug 7, 2007 8:32:25 GMT 8
A1 contains the salary rate of employee A, B1 contains the salary rate of employee B, C1 contains the salaray rate of employee C A2 contains the number of days attended by employee A on the 1st week A3 contains the number of days attended by employee A on the 2nd week A4 contains the number of days attended by employee A on the 3rd week A5 contains the number of days attended by employee A on the 4th week
B2, B3, B4, B5 contains data of employee B, C2, c3, c4, C5 contains data of employee C, etc.
Question: how do I make the formula in A12 to compute A12=A1*A2 such that if I drag the cell A12 downwards A13 will have =A1*A3 A14 will have A1*A4 and so forth?
In other words, how do I make a particular cell stationary while the other cell auto-numbers?
Thank you!
|
|
|
Post by warlock^_^ on Aug 7, 2007 9:18:27 GMT 8
Use "$" whenever you want to anchor a certain cell. $A$1 will anchor it to 1 row & A column
$A1 will anchor A column but if you drag the formula across the rows it'll become A12=$A1*A2 A13=$A2*A2 A14=$A3*A3
same condition apply if you anchor the rows A$12 and the formula you drag it across the columns.
|
|
|
Post by gadgets88 on Aug 7, 2007 9:34:56 GMT 8
Got it! Thank you! Wow. That was fast! Side Questions: How come we both have negative karma points? Good looking guys get all the attention, right?
|
|
leflea
Free Rider
...
Posts: 327
|
Post by leflea on Aug 7, 2007 14:30:50 GMT 8
excel is fun, years ago i did a basic payroll system in excel. complete with automatic computations for deductions (sss, tax), overtime and holiday pay. pretty powerful, but it really took some time to compete, and a lot of references to read. how about me? nobody's interested in me, got zero points!
|
|
|
Post by gadgets88 on Aug 8, 2007 10:27:34 GMT 8
Either they love you or hate you. Zero attention is bad. <tsk><tsk> Going back to the topic. Can you suggest any Excel books? Know any good online tutorial sites? Btw, the problem with the automatic formulas I'm making is that nothing in real life is automatic! Every week is a surprise! My employees have their own formulas, like borrowing money anytime of the week because they don't have money for transporation (pasahe) or because time to pay for electricity or medical bills or buy milk. Each time, I don't know the proper response. I want to ask my employees: what happened to saving for rainy days? Where did your SSS/pagibig loan go? What are your family plans? Family planning? Why is your wife/brother/cousin playing the guitar/tambay/at home instead of going out to find a job? Why your wife pregnant again? How does one become a Superman? ;D My friends tell me I should have an office policy of no loans. But all I have is this small store and these few employees are like family. So...
|
|
|
Post by arcireyes on Aug 8, 2007 10:48:48 GMT 8
gadgets brad., add columns for items that are changeable (i.e for bale/cash advance, etc.) since you say your employees are small i think that manually encoding this items is easy. for items that are constant, (i.e.: SSS, Pag-ibig and Medicare contributions and witholding tax/which is more or less constant) create a table in one sheet and link it to the bi-monthly (?) payroll sheet. i run a 15 employee factory (including me), if you want i can send you the accountant-designed excel file we're using for our payroll system. simple but functional.
|
|
leflea
Free Rider
...
Posts: 327
|
Post by leflea on Aug 8, 2007 14:54:06 GMT 8
hmm.. because excel is made by microsoft, it should be well documented. first thing to do is hit F1 and browse through the table of contents of excel help. it's a whole lot of browsing and reading. and knowing how microsoft writes technical docs, it should be boring i'll see later if i can find decent references i can recommend. excel is very powerful and extensible. if you know what you want, excel can do it for you.
|
|
leflea
Free Rider
...
Posts: 327
|
Post by leflea on Aug 8, 2007 15:05:34 GMT 8
... Btw, the problem with the automatic formulas I'm making is that nothing in real life is automatic! Every week is a surprise! My employees have their own formulas, like borrowing money anytime of the week because they don't have money for transporation (pasahe) or because time to pay for electricity or medical bills or buy milk. .... how about using index cards to keep track of small loans? you can have one index card per employee, five columns (1-date of transaction, 2-amount debit, 3-amount credit, 4-running balance, 5-description).
|
|
|
Post by gadgets88 on Aug 9, 2007 7:22:13 GMT 8
Another question. Employee A has a salary on cell A1. The SSS deduction is based on an SSS deduction table ranging from J4 to M28, 4 columns. If the SSS deduction table is located on another worksheet, how do I modify the VLOOKUP(A1,J4:M28,4) function? SSS Contribution Schedule Compensation Range SSS ER SSS EE J4 1.00 to 1249.99 70.70 33.30 J5 1250.00 to 1249.99 106.00 50.00 J6 1750.00 to 1749.99 141.30 66.70 J7 2250.00 to 2749.99 176.70 83.30 ... J28 Thanks again!!! Tito Arci, please do. Theflea, good suggestion. I'm using collection envelope with those columns in place. Don't enjoy browsing MS help files. Don't enjoy being taken for a ride.
|
|
leflea
Free Rider
...
Posts: 327
|
Post by leflea on Aug 9, 2007 11:19:09 GMT 8
here are some links to get you started: but then again, if you're using functions such as VLOOKUP and HLOOKUP, the above links are chicken feed to you. ... If the SSS deduction table is located on another worksheet, how do I modify the VLOOKUP(A1,J4:M28,4) function? ... it's been a long time since i've worked on excel... hmm... and i've already forgotten how SSS contributions are computed. try this: VLOOKUP(A1,SheetName!J4:M28,4,TRUE) add the third parameter 'TRUE' because you're comparing for a range and not a single value. hope it helps.
|
|
leflea
Free Rider
...
Posts: 327
|
Post by leflea on Aug 9, 2007 11:21:32 GMT 8
if it doesn't work, try: VLOOKUP(A1,ShetName!$J$4:$M$28,4,TRUE)
|
|
|
Post by gadgets88 on Aug 9, 2007 17:05:19 GMT 8
Thanks flea,
Was stubbornly using VLOOKUP(workshtname!A1, J4:M28,4) instead of VLOOKUP(A1,SheetName!J4:M28,4) and kept getting a wrong amount.
When you pointed out the formula, I got it.
It worked without the TRUE parameter.
TRUE or not TRUE, that is the question... hmm...
|
|
leflea
Free Rider
...
Posts: 327
|
Post by leflea on Aug 9, 2007 19:33:16 GMT 8
it must have been set to TRUE by default
|
|
|
Post by gadgets88 on Aug 14, 2007 17:23:42 GMT 8
I have 12 worksheets (Jan to Dec). I want to delete Line40 to Line45 in all worksheets. I want to Fill CELL B4 with color PINK in all worksheets.
Is there a shortcut? I don't enjoy doing something 12 times over. Thank you!
|
|
|
Post by whoopi on Aug 15, 2007 14:24:04 GMT 8
GADGETS, select the worksheets you want to work on by either pressing CTRL and then selecting one by one with the cursor, or pressing SHIFT and selecting the first and the last one with the cursor.
you will know the worksheet is selected because it will be white. unselected worksheets are gray.
whatever you do on any of the selected worksheets will happen to the other selected worksheets as long as the cells you update are identical. if for example you delete a row in Worksheet #1, and in Worksheet #3 that row has merged cells, then the action will not take effect on Worksheet #3 (you will get an error box informing you so), but will happen only on Worksheets #2, 4, 5, 6, 7, 8, 9 and 10.
to unselect, do the same as when you started selecting.
|
|
|
Post by gadgets88 on Aug 17, 2007 11:22:32 GMT 8
Thank you my dear!
Should have asked you first instead of working on the file for 3 days. It would have cost me 3 seconds instead of 3 days work!
That was easy... pala. ;D
|
|