Drag-down columns in formula

**jasontht** · 07-13-2011, 03:18 PM

Hey guys,

This is a tiny problem that I believe can be solved easily, but I just couldn't find an answer:

For a Datastream request table, I need to set up a Data Destination in a blank worksheet. Instead of rows, I need columns with headers. For the rows I can drag-down the cells, but Excel doesn't seem to get the row drag-down:

For rows:

Sheet1'!$A$1
Sheet1'!$A$2

"select & drag-down"

Sheet1'!$A$3
Sheet1'!$A$4
Sheet1'!$A$5
etc.

But if I want to use columns I get this:

Sheet1'!$A$1
Sheet1'!$B$1

"drag-down"

Sheet1'!$A$2
Sheet1'!$B$2

instead of
Sheet1'!$C$1
Sheet1'!$D$1
Sheet1'!$E$1
etc.

It doesn't get that I want to use columns, not rows again.

I've searched through the forum search AND the google custom search for an answer, so forgive me if this was posted before

. Thanks!

**NBVC** · 07-13-2011, 03:30 PM

Try:

=INDEX(Sheet1!$1:$1,1,ROWS($A$1:$A1))

copied down

**Paul** · 07-13-2011, 03:33 PM

Hi Jasontht, welcome to the forum.

When you use static cell references ($A$1, $B$2, etc.) neither the column or row reference changes when you fill down or across. Leave the $'s off for those references to change.

However, by default you cannot fill down and increment the column letter. Excel just doesn't work that way. You could try the following, though:

Let's say in A1 on Sheet1 you wanted to reference A1 on Sheet2. In A2 on Sheet1 you want to reference B1 on Sheet2. A3 on Sheet1 should reference C1 on Sheet2, etc.

In A1 on Sheet1 put this formula and fill down:

=OFFSET(Sheet2!$A$1,,ROW()-1)

**jasontht** · 07-14-2011, 08:50 AM

Hi Paul and NBVC, thanks for your quick answer. Unfortunately it did not work, probably because it is not a standard file but a VBA drenched file linked to Datastream. Paul, you were spot on with your example, that's what I want. I found a website that explains how to use a formula for rows:

http://finabase.blogspot.com/2011/06...lculating.html

I think you get the idea without having the file. Now, the only thing I need is how to that formula work with columns instead of the rows. And to have 1 or 2 cells of space between the next data destination. I hope this might explain the problem a bit better. And to clarify the need for a drag-down or formula, clicking the data destination cell 300 times (my sample is 300) is very tedious

...

**Paul** · 07-14-2011, 12:31 PM

Now, the only thing I need is how to that formula work with columns instead of the rows. And to have 1 or 2 cells of space between the next data destination.

My formula "works with columns", unless you and I differ on what that means. If you place my formula in a cell it will return the value from Sheet2 cell A1. If you drag it down one row the second cell will return the value from Sheet2 cell B1. Down another row and you get Sheet2!C1, etc.

So as you drag the formula down, it references one additional column to the right. As for adding spaces, that's going to get a bit more complex. Is there logic to determine when you want to skip, and how many? (It likely would need to be a repetitive pattern.)

Also to be clear, you're not using an Excel worksheet for these functions?

**TraceCadet** · 05-07-2012, 02:00 PM

I have a question related to this. I need a column of formulas like this:

=INDIRECT("R3")-A3
=INDIRECT("R4")-A4
=INDIRECT("R5")-A5

and I want it to continue down the spreadsheet for a couple hundred rows, e.g. end up with =INDIRECT("R253")-A253. I had to write each of the three formulas above individually because when I tried to use the "fill" feature, the part inside the quotation mark doesn't advance accordingly. Is there any way to use "fill" to get sequential row values inside the quotation marks? I even tried to fill without the quotation marks first and then use "find and replace" to insert the quotation marks, but I got a scolding from the formula error popup window.

Thanks, Tracy