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

Reply ↓  Report •


#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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#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.

Nigel

Wind slow


Reply ↓  Report •

#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.

;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
January 10, 2017 at 11:04:55
If only the "rest" of the world had stayed in the Empire instead of leaving at various times - starting in the mid-18th century across the pond..., if not earlier when those irritating people opposite Dover left in a huff around the time of Henry the whatever in the mid-15th century - these confusions and disparities wouldn't exist...

And of course everyone would also drive their cars on the left like normal people do in the UK and one or two other places...


Reply ↓  Report •

Ask Question