• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Re automatic copying of formulas

J

jenwren

New Member
Hello,


When dragging formulas into adjacent cells the formula automatically 'counts up' as you drag horizontally across, does anyone know how to get this to work when dragging a formula vertically down please? An answer to this would save me so much time as I am manully changing formulas on occassions!


Thank you.
 
Hi Jenwren,


I think you need to freeze (using "F4") the values in formula before dragging formulas.


if you provide the formula that would be help full to anser your question.


regards,

Satish.
 
As Satish said, it would help if you posted the formula. I'm guessing you need to swap a absolute reference with a relative one.
 
I know I may be asking for something that cannot be done here! I am reading information from a set of cells in one spreadsheet into another to create a table and pie chart from a summary sheet, I am using ='Feedback sheet summary'!E$2 to 'read' the first cell from the orginal sheet, into a table, which is then being used to create a pie chart and provides the information for the data labels in the chart. If I drag the formula horizontally across it will then read F$2, G$2, H$2, etc which picks up the information I want. But I really want to be able to drag the forumula vertically down and have it do the same thing. I've tried paste special and transpose which doesn't work.


I know this may not be doable, thank you for at least having a look.


P.S. I know I can just change the formatting and have the table reading horizontally across instead of vertically but just really wanted to know if the above can be done?
 
Jenwren


Try using:

Code: 
=OFFSET('Feedback sheet summary'!$E$2,,ROW('Feedback sheet summary'!E2)-ROW('Feedback sheet summary'!$E$2))


Then drag down

It will retrieve the values from E2, F2, G2 ...
 
If you want to understand how the Offset() function works, have a read here:

http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
 
You must log in or register to reply here.
Back
Top