Quick excel question

jflail2

Hey guys:

Does anyone know how to automate cell references from a row to a column?

So I have a row of data (cells J:29-AA:29) that I'm trying to reference in column B.

I'd like to figure out a way to NOT have to type =J29, =K29, etc into cells B7, B8, etc as I have 64 data points to reference.

Anyone know how I can quickly cell reference in the above format?

AsSiMiLaTeD

You should just be able to create your formula in one cell, then just drag that formula across column B and it will autopopulate for you.

If you need to, play with the placement of the $ in the formular to get the result you want, so $J8 gives you a different result than J$8, the $ locks the reference to that row or column

I haven't played with that in a while, but that should get you enough info to figure out what you need

jflail2

Yea, I've tried locking rows and columns to no avail. I guess I'm looking for a "transposed cell reference."

I'm comfortable with lookups, index/match formulas, pivot tables, etc. I'm just stumped with what appears to be a basic function...

maximillian

As AsSiMiLaTeD mentioned, the "$" make the cell reference absolute. Without it the reference is relative. For example the cell a7 could have a formula that references a1. If you copy this cell to b7 then the reference would then be b1. If you copy a7 to b8 then the reference would be to b8. If you want to keep either the row or column absolute then you need to place the "$" in front. So to keep the row constant then change the reference to "$a1". For constant column make the reference "a$1". To keep the cell absolute regardless of where it is copied then make it "$a$1".

Hope this helps.

jflail2

Nope, that's not working.

Here's a screenshot attached. I'm trying to get cell F7, F8, etc populated with the data in cells O29, O30, etc.

If I lock the row I'm referencing, my formula in cell F7 is =O$29. If I copy this down to cells F8, F9, etc it stays as O$29.

Note that I simply did a paste-special-transpose to get the hard coded data in there. Still, I'd like this to be automated going forward.

edbert

Move the dollar sign.

F7 is =$O29

Type it in the first box and then drag it down. Worked for me.

jflail2

That would lock the column Edbert, which is what I don't want.

After trying a million formulas, it looks like Offset wins the prize!

edbert

Ah, I see what you are doing. Completely misunderstood the first time. I still would not have come up with Offset, but glad you figured it out. Am I understanding that this will allow you to display info in one column that is originally spread out vertically in one row?

Big Dawg

First create the references in a row with the row reference locked. then use the copy paste special transpose formulas. should work. Try it.

jflail2

edbert wrote: »

Ah, I see what you are doing. Completely misunderstood the first time. I still would not have come up with Offset, but glad you figured it out. Am I understanding that this will allow you to display info in one column that is originally spread out vertically in one row?

Thanks, me too. Yup, this is the only way I can think of to get a bunch of data from a row to a column (or vice versa) without using index/match formulas. I couldn't use those b/c the chart headers on the left don't match the data labels in each row, so as far as I can tell offset is the only solution.

Thanks everyone for the suggestions!

jflail2

Big Dawg:

That's even better actually. I'm going to use that instead of offset, as the end user isn't overly excel savvy.

Hats off to you; this is great!