Solved How do I drag to right cells (result like dragging down)

November 28, 2016 at 06:01:54
Specs: Windows 64
Hi guys. I have an excel issue.

I've 50 data on each year since year 2000 to 2016 on sheet A.

I am trying to copy a formulated data on sheet A to a new sheet in Sheet B to make a neater table.

Example:
Sheet A
A2 : Data 1
B2 : 35
A3 : Data 2
B3 : 79
...

Sheet B:
I want the data from the whole of column B.
On Sheet B, A1 Cell, I inserted forumla ='SheetA'!B2. When i drag down, the formula will turn out to be ='SheetA'!B3. This is perfectly normal.

But when I drag right, it will show ='SheetA'!C2 instead. How do I get the formula to appear exactly how it should react when i drag down?


See More: How do I drag to right cells (result like dragging down)

Report •

✔ Best Answer
December 3, 2016 at 21:12:21
I don't know why you marked the thread as solved if you are still having problems. I have reset the Best Answer. Let's not call the thread solved until you are able to get the results you are looking for.

re: 1) How come all the cells on the right shows the same formula:
=INDIRECT("'Seasonal chart'!H" & COLUMN()) ? I thought there is supposed to be some incremental reference... 1 --> 2 --> 3 etc

Based on that question, it does not appear that you understand how the COLUMN() function works. I'll explain it again and if it still not clear, may I suggest that you do a Google search for the function and read some of the many sites that explain the function. It may be that I am not doing a good enough job explaining how it works.

Let's forget about everything else for now and just focus on the COLUMN() function.

In its simplest form =COLUMN() does nothing more than return the number of the column in which it is used.

Column A is Column 1. If you put =COLUMN() in any row in Column A, a 1 will be displayed in the cell.

Column B is Column 2. If you put =COLUMN() in any row in Column B, a 2 will be displayed in the cell.

Column Z is Column 26. If you put =COLUMN() in any row in Column Z, a 26 will be displayed in the cell.

If you put =COLUMN() in any row in Column A and drag the formula to the right, you will see the results in the cells increment: 1, 2, 3, 4, etc. because the function is returning the number of the Column in which it is used. You won't see the formula change because it is not changing. Only the result changes.

Now, let's complicate it just a bit.

Let's say you put =COLUMN() + 2 in Column A. The cell will display 3 because the COLUMN() function will return 1 which will be added to the 2. If you drag that formula to the right, you will get 4, 5, 6, etc. because the COLUMN() function will increment internally returning the number of each column and then adding 2 to it. The final result of that math will be displayed in the cell.

You should test the COLUMN() function in various ways until you understand why you get the results you do.

OK, next step:

If you put this in Column A, what do think you will get?

="H" & COLUMN()

If you drag it to the right what do you think you will get?

Before you read anymore, try it and see if you understand why you get the result you do. Really, try it. The rest of this explanation requires that you understand how the COLUMN() function works.

The point of those examples is to show you that you can perform mathematical operations with the COLUMN() function (e.g. "+2") and that you can append the result to a text string (e.g. "H"). Both of those things are required for you to get the results you want.

OK, now let's take a look at a simple version of your formula. We won't use the sheet name, just the Column letter to keep it simple.

=INDIRECT("H" & Column())

As I explained in an earlier post, the INDIRECT function accepts a text string and converts it to a cell reference. We've already shown that ="H"&COLUMN() is going to return H followed by the number of the Column in which the formula resides. In Column A, it will return H1. In Column B, it will return H2, etc.

However, ="H"&COLUMN() is returning a text string. i.e. it is not using H1 as cell reference. For that to happen, we need the INDIRECT function. If this formula was placed in Column A, this is how Excel will evaluate the formula:

=INDIRECT("H" & Column()) ---> =INDIRECT("H" & 1) ---> 
=INDIRECT("H1") ---> =H1

That will return the value from H1 because the INDIRECT function turned the text sting H1 into a cell reference.

If you put the same formula in Column B, you would get this:

=INDIRECT("H" & Column()) ---> =INDIRECT("H" & 2) ---> 
=INDIRECT("H2") ---> =H2

That will return the value from H2 because the INDIRECT function turned the text sting H2 into a cell reference.

OK, now let's say you want to return the value from H1 but you want to put the formula in Column D. Now you need to perform some math in orer to get the 1:

=INDIRECT("H" & Column() - 3) ---> =INDIRECT("H" & 4 - 3) ---> 
=INDIRECT("H1") ---> =H1

re: D6 has my very manual formula inserted.

It appears that you want to return the value from H56 in D6. We know that =INDIRECT("H" will get you the H, but now you need the 56.

What Column is the formula in? D
What number will the COLUMN() function return when used in Column D? 4
What do you need to do to turn 4 into 56?

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



#1
November 28, 2016 at 06:57:59
Try this:

=INDIRECT("SheetA!B" & COLUMN()+1)

The INDIRECT function converts a text string into a formula. Note the double quotes around SheetA!B. That tells Excel that it's a text string.

The COLUMN() function returns the Column Number for the column in which it is used.
e.g. If you use =COLUMN() in Column A, it will return 1. If you add 1 to that, it will return 2.

Therefore, the formula becomes:

=INDIRECT("SheetA!B"&2) which resolves to =SheetA!B2.

As you drag it across, the COLUMN() function increments by 1 each time, so you get:

=SheetA!B2
=SheetA!B3
=SheetA!B4

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

message edited by DerbyDad03


Report •

#2
November 29, 2016 at 07:11:41
Thank you!

I am not familiar with this code.

Heres my exact code: =INDIRECT("'+'Seasonal chart'!H4" & COLUMN()+1)

The result shows "#REF!". Any idea what did i input wrongly in the code?


Report •

#3
November 29, 2016 at 08:18:39
What is the '+ for? If I remove those symbols, your formula works fine.

=INDIRECT("'Seasonal chart'!H4" & COLUMN()+1)

I also noticed that you used H4. That means that Excel will start at H4 and append the results of the COLUMN()+1 portion to that cell reference.

In other words, if you place your formula (without the '+) in Column A, it will return the value from Seasonal chart!H42

Seasonal chart!H4 & 1+1 ----> Seasonal chart!H4 & 2 ----> Seasonal chart!H42

Is that what you want?

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


Report •

Related Solutions

#4
November 30, 2016 at 05:09:43
Perfect! This works for me. Thank you!

I'm curious how this thing works though. My formula is this: =INDIRECT("'Seasonal chart'!H0" & COLUMN())

The result of that is copying of H4's data. But how did it do it? Why is H4 in the seasonal chart represented by H0 in the formula (in another sheet)?


Report •

#5
November 30, 2016 at 15:58:55
I don't know why you added the 0 (zero) after the H, but Excel simply ignores it when the INDIRECT function converts the text to a cell reference.

Enter this in A1 and press enter: =H04

Now click in A1 and see what it says in the formula bar. I'll bet it says =H4

The 0 is a leading zero and does nothing.

Why does it copy the data from H4? First, I have to assume that you put the formula in Column D. In Column D the COLUMN() function will return 4 because Column D is the 4th column.

If =INDIRECT("H"&COLUMN()) is placed in any column, the number of that column will be appended to the H and that cell will be used as the reference. Again, the 0 is not needed and is ignored.

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

message edited by DerbyDad03


Report •

#6
December 3, 2016 at 17:40:24
http://www12.zippyshare.com/v/skOPu...

You are right, having 0 seems like an additional input that makes no sense. Must have been defaulted there, didnt notice initially.

I was able to use the formula: =INDIRECT("'Seasonal chart'!H" & COLUMN()) for cell D5 under the weekly tabs. Then i dragged the cells to the right and it works.

A couple of question:

1) How come all the cells on the right shows the same formula:=INDIRECT("'Seasonal chart'!H" & COLUMN()) ? I thought there is supposed to be some incremental reference... 1 --> 2 --> 3 etc

2) D6 has my very manual formula inserted. The same for the whole row, thus my question in the very first place which you suggested the =indirect formula. How do I get this formula to work for D6 and its row though?

When I tried the formula, =INDIRECT("'Seasonal chart'!H1" & COLUMN()) , i realised it is copying the data of H14, while formula : =INDIRECT("'Seasonal chart'!H2" & COLUMN()) copies the data from H24. Can do i select a data to copy in between these 2 numbers?

Thank you for your time. I'm working on more charts, and this will save me alot of manual effort.


Report •

#7
December 3, 2016 at 21:12:21
✔ Best Answer
I don't know why you marked the thread as solved if you are still having problems. I have reset the Best Answer. Let's not call the thread solved until you are able to get the results you are looking for.

re: 1) How come all the cells on the right shows the same formula:
=INDIRECT("'Seasonal chart'!H" & COLUMN()) ? I thought there is supposed to be some incremental reference... 1 --> 2 --> 3 etc

Based on that question, it does not appear that you understand how the COLUMN() function works. I'll explain it again and if it still not clear, may I suggest that you do a Google search for the function and read some of the many sites that explain the function. It may be that I am not doing a good enough job explaining how it works.

Let's forget about everything else for now and just focus on the COLUMN() function.

In its simplest form =COLUMN() does nothing more than return the number of the column in which it is used.

Column A is Column 1. If you put =COLUMN() in any row in Column A, a 1 will be displayed in the cell.

Column B is Column 2. If you put =COLUMN() in any row in Column B, a 2 will be displayed in the cell.

Column Z is Column 26. If you put =COLUMN() in any row in Column Z, a 26 will be displayed in the cell.

If you put =COLUMN() in any row in Column A and drag the formula to the right, you will see the results in the cells increment: 1, 2, 3, 4, etc. because the function is returning the number of the Column in which it is used. You won't see the formula change because it is not changing. Only the result changes.

Now, let's complicate it just a bit.

Let's say you put =COLUMN() + 2 in Column A. The cell will display 3 because the COLUMN() function will return 1 which will be added to the 2. If you drag that formula to the right, you will get 4, 5, 6, etc. because the COLUMN() function will increment internally returning the number of each column and then adding 2 to it. The final result of that math will be displayed in the cell.

You should test the COLUMN() function in various ways until you understand why you get the results you do.

OK, next step:

If you put this in Column A, what do think you will get?

="H" & COLUMN()

If you drag it to the right what do you think you will get?

Before you read anymore, try it and see if you understand why you get the result you do. Really, try it. The rest of this explanation requires that you understand how the COLUMN() function works.

The point of those examples is to show you that you can perform mathematical operations with the COLUMN() function (e.g. "+2") and that you can append the result to a text string (e.g. "H"). Both of those things are required for you to get the results you want.

OK, now let's take a look at a simple version of your formula. We won't use the sheet name, just the Column letter to keep it simple.

=INDIRECT("H" & Column())

As I explained in an earlier post, the INDIRECT function accepts a text string and converts it to a cell reference. We've already shown that ="H"&COLUMN() is going to return H followed by the number of the Column in which the formula resides. In Column A, it will return H1. In Column B, it will return H2, etc.

However, ="H"&COLUMN() is returning a text string. i.e. it is not using H1 as cell reference. For that to happen, we need the INDIRECT function. If this formula was placed in Column A, this is how Excel will evaluate the formula:

=INDIRECT("H" & Column()) ---> =INDIRECT("H" & 1) ---> 
=INDIRECT("H1") ---> =H1

That will return the value from H1 because the INDIRECT function turned the text sting H1 into a cell reference.

If you put the same formula in Column B, you would get this:

=INDIRECT("H" & Column()) ---> =INDIRECT("H" & 2) ---> 
=INDIRECT("H2") ---> =H2

That will return the value from H2 because the INDIRECT function turned the text sting H2 into a cell reference.

OK, now let's say you want to return the value from H1 but you want to put the formula in Column D. Now you need to perform some math in orer to get the 1:

=INDIRECT("H" & Column() - 3) ---> =INDIRECT("H" & 4 - 3) ---> 
=INDIRECT("H1") ---> =H1

re: D6 has my very manual formula inserted.

It appears that you want to return the value from H56 in D6. We know that =INDIRECT("H" will get you the H, but now you need the 56.

What Column is the formula in? D
What number will the COLUMN() function return when used in Column D? 4
What do you need to do to turn 4 into 56?

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


Report •

#8
December 4, 2016 at 05:22:17
I managed to get the first row of data based on your advice earlier, and it worked.

Appreciate you taking the time to explain this in depth! Reading back, I misread/misunderstood the concept along the way. I found the reason why and have now completed my excel.

What do you need to do to turn 4 into 56? This is the answer I got:
=INDIRECT("'Seasonal chart'!H" & COLUMN()+52)

The first thought process should be which column i am keying the formula in and not the end result's cell reference. From there, the understanding on the =column() formula takes over. I kept thinking in that direction and your example enlightened me.

Thank you!


Report •

#9
December 4, 2016 at 05:32:12
I'm glad you got it worked out.

BTW...there's a ROW() function also.

It works the same way by returning the Row number.

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


Report •

#10
December 4, 2016 at 05:35:44
Always good to know. Thank you 😊

Report •

Ask Question