Changing column when copying downwards in excel

January 10, 2017 at 01:21:19
Specs: Windows 8
 Hello,I am working in excel and using this simple function.=MATCH("x";Sheet1!J\$6:J\$13;0) When dragging downwards i want the column to change, but not the numbers. Like this=MATCH("x";Sheet1!J\$6:J\$13;0)=MATCH("x";Sheet1!K\$6:K\$13;0)Is there anyway to make this automatic while dragging down?

See More: Changing column when copying downwards in excel

#1
January 10, 2017 at 05:42:26
 First: Your example formula contains semi-colons. Excel uses commas, so that is what I am going to use.As for your question, you should be able to accomplish your goal by using the ROW() and OFFSET() functions.The ROW() function will return the number of the Row in which the function resides. e.g used anywhere in Row 1, =ROW() will return 1. Used anywhere in Row 473, =ROW() will return 473. That number can be used as the cols argument for the OFFSET function.For example, if your first MATCH function is in Row 1, then these 2 formulas are equivalent:=MATCH("x";Sheet1!J\$6:J\$13;0)=MATCH("x",OFFSET(J\$6:J\$13,0,ROW()-1),0)By using 0 as the rows argument for OFFSET, there will never be a Row offset. By using ROW()-1 as the cols argument, there will be no Column offset because ROW() will return 1 and 1-1 = 0. Again, that is based on the fact that the formula is in Row 1. (More on that later)When you drag the formula down into Row 2, the Column offset will be 1 because ROW() will return 2 and 2-1 = 1. Therefore the MATCH function will now look at Column K, which is 1 Column offset from J.Here is the "More on that later" part: If your MATCH function is not in Row 1, you will need to adjust the cols argument so that ROW()-? will equal 0 based on which Row the first MATCH function is in.Please note that the formula itself will not change when you drag it down. It will look the same in all cells, but it will referencing a different column because the value returned by the ROW() function will increment each time you drag it down, incrementing the Column offset value. If you want to see the Column reference change, choose any formula in your range and use the Evaluate Formula feature to single-step through the formula. Once the OFFSET portion is evaluated, the new Column reference will be shown.message edited by DerbyDad03

#2
January 10, 2017 at 08:23:31
 Not wanting to be nitpicky but, in the USA, China and current and former members of the British Commonwealth the point is used as decimal separator. In almost all the rest of the world, the comma is used as decimal separator. Hence, in Excel (and other similar programs), the semi-colon actually is used in formulas in most of the world.NigelWind slow

#3
January 10, 2017 at 10:21:45
 I'll rephrase:First: Your example formula contains semi-colons. My Excel uses commas, so that is what I am going to use.;-)