tag:blogger.com,1999:blog-1869924468172210809.post6189169221455801116..comments2017-01-19T07:55:33.644+00:00Comments on Not Just Numbers: EXCEL TIP: The dollar sign ($) in a formula - Fixing cell referencesGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger156125tag:blogger.com,1999:blog-1869924468172210809.post-53333554114731369982017-01-17T10:01:27.352+00:002017-01-17T10:01:27.352+00:00Just put the $ in front of the row rather than th...Just put the $ in front of the row rather than the column, i.e.<br /><br />E$1Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-1731982476731561412017-01-17T09:54:31.494+00:002017-01-17T09:54:31.494+00:00Hello Sir,
$ fix Column E1, E2, E3
If i want fix r...Hello Sir,<br />$ fix Column E1, E2, E3<br />If i want fix row E1, F1, G1.<br />Thank you. Amgad Abadirhttp://www.blogger.com/profile/09535009642437070227noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-27342768636102519842017-01-11T09:25:47.802+00:002017-01-11T09:25:47.802+00:00yeah, thanks for your post. I think i found out my...yeah, thanks for your post. I think i found out my problem, I have fixed and everything is ok now,andro dumpperhttp://appforpc1.com/androdumpper-wps-connect-download-for-pcwindow/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-31585754568462852762017-01-05T01:27:12.489+00:002017-01-05T01:27:12.489+00:00thanks a lot for your support. I will try itthanks a lot for your support. I will try itRichard Nicolehttp://www.jiotvapp.com/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-22837749861408856042017-01-04T06:32:31.916+00:002017-01-04T06:32:31.916+00:00You should be able to do what you are after using ...You should be able to do what you are after using a dynamic range. Open the Name Manager on the Formula ribbon, and add a new range (let's call it dropdown), and enter your formula as the range area. Dollar rules apply, so if you don't add the dollars, the reference will be relative to the currently active cell - for example if you have cell B2 selected and you enter =A2 as your range definition, your range will always be the cell to the left of the active cell. If you enter =$A2, the range will always be the cell in column A of the same row as the active cell, etc.<br />Once you have created the range, you just enter its name preceded by the equals sign as the list in your data validation, e.g. =dropdown.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-28468093340458228452017-01-04T04:01:44.276+00:002017-01-04T04:01:44.276+00:00Hi,many thanks for your post. I got one question:
...Hi,many thanks for your post. I got one question:<br /> for Data Validation -> List, as source, instead of 23 in '=$K$6:$K23', i want to give a formula, for example, I want to give something like '=$K$6:$K(VLOOKUP(C3,N2:P9,2))', is it possible? If so, what is the right format? thanks for any helpRichard Nicolehttp://www.jiotvapp.com/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-63724158612106545522016-11-25T06:03:05.460+00:002016-11-25T06:03:05.460+00:00thnqthnqAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-87188224603363705802016-11-15T20:21:12.933+00:002016-11-15T20:21:12.933+00:00You can achieve the same thing with R1C1 referenci...You can achieve the same thing with R1C1 referencing by using the actual row and column numbers (no square brackets) rather than the relative square brackets you've used in your example. e.g.<br /><br />=R2C4<br /><br />is the same as<br /><br />=$B$4Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-30074962054145990372016-11-15T10:26:40.525+00:002016-11-15T10:26:40.525+00:00Does the dollar sign for fixing the collumn/row al...Does the dollar sign for fixing the collumn/row also work in row/collumn numericall reference style sheet ? (E. g. =R[-1]C+RC[-2]; newest version of office) I am afraid not...Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-35511756948161464812016-10-04T06:49:04.396+01:002016-10-04T06:49:04.396+01:00If I've understood your problem correctly, you...If I've understood your problem correctly, you can't do it just with dollars as the you are wanting the column to increase with the row. The dollar would only allow you to specify whether the row increased with the row or not.<br /><br />I think OFFSET would be your simplest solution:<br /><br />Try<br /><br />=SUMPRODUCT(Sheet1!$B$2:$B$21,OFFSET(Sheet1!$M$2,0,ROW(A2)-2,20,1))<br /><br />We are basically using the row number of the current cell , ROW(A2)-2, to determine how many columns we should offset from M2.<br /><br />The OFFSET function is explained here:<br />http://www.notjustnumbers.co.uk/2010/02/using-offset-function-in-microsoft.html<br /><br />I hope that helps.<br />Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-11273915408174533982016-10-03T20:13:56.148+01:002016-10-03T20:13:56.148+01:00I have a problem and not sure how to solve it in e...I have a problem and not sure how to solve it in excel.<br />I want each cell along vertical column lets say column A to refer a calculation on two columns on a separate sheet and replace the second column moving towards right keeping first column fixed on each consecutive cell of the resulting sheet in the same column.<br />For example,<br /><br />cell A2 of sheet 2 = sumproduct(all of col b2:b21 of sheet 1, all of col m2:m21 of sheet 1)<br />I want cell A3 of sheet 2 = sumproduct( all of col b2;b21 of sheet 1, all of column n2:n21 of sheet 1)<br /><br />what would be the $ usage in the case above? Please suggest. Arieswonderhttp://www.blogger.com/profile/17007723596655414856noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-4917229211009434482016-08-31T14:24:04.573+01:002016-08-31T14:24:04.573+01:00Any idea how to use cell references in C#? Actuall...Any idea how to use cell references in C#? Actually there is no cell in C# i just need to know if there is a code equivalent.<br /><br />tyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-17225171147091017452016-06-07T12:46:50.523+01:002016-06-07T12:46:50.523+01:00Thank you sir. With your help finished the job as ...Thank you sir. With your help finished the job as planned.<br /><br />Thank youChengayihttp://www.blogger.com/profile/06854907671854354249noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-2555398107131331802016-06-02T07:31:45.319+01:002016-06-02T07:31:45.319+01:00thanks for the info..was helpful
thanks for the info..was helpful<br />PRASOON KUMAR Phttp://www.blogger.com/profile/08767105339210970645noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-49795998362528406552016-04-12T21:44:54.461+01:002016-04-12T21:44:54.461+01:00Thanks for asking the question, and thanks for the...Thanks for asking the question, and thanks for the answer, Glen.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-27750491731943974802016-03-07T18:54:51.842+00:002016-03-07T18:54:51.842+00:00How to have just a certain number that change when...How to have just a certain number that change when dragged down? e.g: 01/Q4/2015. How to have just 01 that change to 02, 03 and so on but Q4 and 2015 sill fix?Mega Herminhttp://www.blogger.com/profile/10443755978487815264noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-19002202451253918172016-02-26T14:51:31.915+00:002016-02-26T14:51:31.915+00:00Suresh - I'm not quite sure what it is that yo...Suresh - I'm not quite sure what it is that you want me to explain, but I will assume that it is the different use of the dollar signs in the two examples (as this is the subject of the blog post).<br /><br />The first one looks at the range from E2 to E1048576 on sheet IST, whereas the second one looks at the range G4 to G335 on sheet DBR.<br /><br />In the first one the $ is on both the E and the 2 (as well as the E and the 1048576), fixing both the column and row in both cases. This means that if the formula was copied elsewhere it would still look at exactly the same range.<br /><br />In the second one, the dollar is only on the row numbers (4 and 335), meaning that the column references will move relatively if the formula is copied elsewhere. e.g. if the formula was copied one cell to the right, it would now refer to DBR!H$4:H$335.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-53166552152104831462016-02-26T06:09:50.622+00:002016-02-26T06:09:50.622+00:00Explain IST!$E$2:$E$1048576 and DBR!G$4:G$335??
...Explain IST!$E$2:$E$1048576 and DBR!G$4:G$335??<br /><br /> Suresh Mutyalahttp://www.blogger.com/profile/13210957523705995635noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-5529859797429887662016-02-26T06:08:54.450+00:002016-02-26T06:08:54.450+00:00=SUMIF(DBR!$E$4:$E$335,CSM!$C4,DBR!G$4:G$335)
=+S...=SUMIF(DBR!$E$4:$E$335,CSM!$C4,DBR!G$4:G$335)<br /><br />=+SUMIF(DIST!$C$2:$C$1048576,CSM!B4,DIST!$E$2:$E$1048576)<br /><br />What is the Difference Between above Two formulas??<br /> Suresh Mutyalahttp://www.blogger.com/profile/13210957523705995635noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-59916617270550061352016-02-20T10:35:48.407+00:002016-02-20T10:35:48.407+00:00+1. $ symbol has baffled me for months. This artic...+1. $ symbol has baffled me for months. This article finally ended my confusion. wolverinehttp://www.apkmonk.com/app/com.microsoft.office.excel/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-71897418016011731962016-02-10T07:56:51.440+00:002016-02-10T07:56:51.440+00:00In the first instance you have fixed the column of...In the first instance you have fixed the column of (B) the first reference and the row (1) of the second. In the second example you have done the opposite.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-85499394314176454352016-02-10T07:56:23.229+00:002016-02-10T07:56:23.229+00:00In the first instance you have fixed the column of...In the first instance you have fixed the column of (B) the first reference and the row (1) of the second. In the second example you have done the opposite.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-12916630368342668862016-02-09T22:34:38.615+00:002016-02-09T22:34:38.615+00:00Generally we fix like $B1*A$1, why not B$1*$A1??
...Generally we fix like $B1*A$1, why not B$1*$A1??<br /> If not then why ???Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-80388145006063477382016-02-09T12:57:34.063+00:002016-02-09T12:57:34.063+00:00If you don't want negative figures, then use t...If you don't want negative figures, then use the IF formula e.g., =IF(A1>15000,A1*0.05,0)<br />Hope this helpsGuido Kuwashttp://www.blogger.com/profile/04742111860372249672noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-68479462819435616622016-01-08T06:29:37.008+00:002016-01-08T06:29:37.008+00:00One of the best explanation for the concepts of do...One of the best explanation for the concepts of dollar. ThanksGaurav Khuranahttp://www.blogger.com/profile/10953219489651469548noreply@blogger.com