Embarrassing stupid question regarding absolute rows.

[claybwagner]

I have a data set where the sales in the 1st qtr I am tracking in in row 3 and go from Column B to Column P. I am trying to create a formula where on a separate part of the workbook I can lists those sales. I am entering the following formula. "=B$3" the next row down should read "=C$3" and so on. When I copy the formula, it stays at B$3 all the way down. What am I doing wrong?

Thanks in advance.

Clay Wagner
Nashville, TN

 

[Greg Truby]

You can't quite do it that way because you are wanting to have your reference move to the right as you copy the formula down. What you can do is to select whereever it is that you want to enter the =B3 | =C3 | =D3... formulae. Select all of them at once and then type =Transpose(B3:P3) and instead of hitting enter, hit Ctrl+Shift+Enter to create an array formula.

Also, if you tell me what version of Excel you're running, I can give you a tip from the great Bob Umlas, which shows another way to skin this cat.

 

[Glove_Man]

Ooops

 

[claybwagner]

Excel 2008 for Mac. Thanks for your help. I will try that.

 

[claybwagner]

Tried the "control+shift+enter" but it looks like it only accepted the "enter." Will keep trying or maybe habe to retype the data set. Ugh! Thanks again.
Clay

 

[Greg Truby]

OH! Um, don't know nuffink 'bout Excel fer the Mac. Write back if you can't get the transpose formula to work for you.

 

[Greg Truby]

Tried the "control+shift+enter" but it looks like it only accepted the "enter." Will keep trying or maybe habe to retype the data set. Ugh! Thanks again.
Clay

HEY! Did I say "give up"?

Do you know how to flip the R1C1 notation on?

 

[Greg Truby]

OK - I need to turn this thing off and get to bed -- so I'm going to give you the instructions on Bob's tip and hope you can figure out how to do this on a Mac...

1. Where it is that you need to have B3, select the cell and type in R3C2 (note that there is NO equals sign yet).
2. Now drag this down as far as you need to go, for B:P, that's fifteen columns, so drag down 15 rows, you should end up with something like R3C17 or something close
3. Now (here's what I don't know how to do on a Mac) change the formula display from A1 notation to R1C1 notation (hopefully HELP on a Mac is better than on a PC).
4. While in R1C1 mode, select the cells you just filled and do a FIND and REPLACE and
   • FIND r
   • REPLACE WITH =r
5. This should work immediately
6. You may now flip back to A1 notation

This is all taken from Bob's excellent little book This isn't Excel, it's Magic!

 

[claybwagner]

Got it! Thanks. Saved hours of typing. Waiting on drivers so I can install Windows 7 on my mac so I can install Excel 2007. Not a huge fan of office 2008 for the mac.