The $ is referred to as an "Anchor". For example: Let's say that you have a list of numbers in column "A" that represent "Hours" A row1: "HOURS" row2: 1 row3: 2 row4: 3 row5: 4
And in row 1, (columns B through D) you place "hourly rates" B C D 5 10 15 Now, next to each of the "hours" you want to multiply the hours with the rate at the top of the column. If you had LOTS of time and can type PERFECTLY with NO mistakes, you could manually type the formula: =A2*B1 in Cell B2 C2 would have =A2*C1 D2 would have =A2*D1 so you have: row2: 1 | =A2*B1 | =A2*C1 | =A2*D1 but if you had a BUNCH of columns, you may say to yourself: "It would be easier to copy the cells, thus reducing the possibility of ruining my record of absolute perfection!" so instead you put =A2*B1 in cell B2 and copy it to C2 and D2.. but (GASP!) it didn't do what you wanted!!! You ended up with: row2: 1 | =A2*B1 | =B2*C1 | =C2*D1 So.. after canceling your membership to Obsessive-Compulsive Anonymous (O-CA), you find that the reason is that the cell addresses are "relative" to the current cell. Meaning that when you were in cell B2 and entered "=A2", Excel determined that "relative to the current cell", A2 is one cell to the LEFT. so when you copied the formula, Excel continued to refer to the cell "one cell to the Left" What you want to do is to tell Excel that you wish to use the ABSOLUTE position instead of the RELATIVE position. to do that, you use the $ to ANCHOR the COLUMN. like this: =$A2*B1 (you don't want to put the $ in front of "B" because you WANT it to be "relative") Now when you copy it, you get: row2: 1 | =$A2*B1 | =$A2*C1 | =$A2*D1 Wonderful! Now all we need to do is copy it down and we'll finish in time to be EARLY to my O-CA meeting (don't know why I bother, they spend all evening straightening the chairs!) But: OH NO!!! You end up with: row1:Hrs| 5 | 10 | 15 row2: 1 | =$A2*B1 | =$A2*C1 | =$A2*D1 row3: 2 | =$A3*B2 | =$A3*C2 | =$A3*D2 row4: 3 | =$A4*B3 | =$A4*C3 | =$A4*D3 row5: 4 | =$A5*B4 | =$A5*C4 | =$A5*D4 That's because you failed to tell Excel to ALWAYS use row 1 for the rate(s)! (you didn't "anchor" the row!) So, back to cell B2, change it to read: =$A2*B$1 Now copy it across, then copy the row down (it won't let you drag it across AND down in the same action) and you get: (drumroll please) row1:Hrs| 5 | 10 | 15 row2: 1| =$A2*B$1 | =$A2*C$1 | =$A2*D$1 row3: 2| =$A3*B$1 | =$A3*C$1 | =$A3*D$1 row4: 3| =$A4*B$1 | =$A4*C$1 | =$A4*D$1 row5: 4| =$A5*B$1 | =$A5*C$1 | =$A5*D$1 and the crowd goes wild! the boss showers you with praise (sadly, no money) And there you have it... a tale guaranteed to control unwanted pests and frighten small children... thank you for allowing me to release this pent-up sarcasm before I burst!!!! Hope it helps.. Paul ________________________________ From: vinay keni <kenivi...@gmail.com> To: excel-macros@googlegroups.com Sent: Thursday, June 18, 2009 10:26:51 AM Subject: $$Excel-Macros$$ use of $ hi friends as i am new 2 excel i just want 2 know Wat's the intention of using "$" symbol while giving the formulas ......will that b used for only some particular formulas .............pls guide m -- Regards, Vinay Keni --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
