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 AA2 : Data 1B2 : 35A3 : Data 2B3 : 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) December 3, 2016 at 21:12:21

#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!B4message 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!H42Seasonal chart!H4 & 1+1 ----> Seasonal chart!H4 & 2 ----> Seasonal chart!H42Is that what you want?

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: =H04Now click in A1 and see what it says in the formula bar. I'll bet it says =H4The 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.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 etc2) 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

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.

Report •

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

Report • 