Embarrassing stupid question regarding absolute rows.

claybwagner

New Member
Joined
Mar 15, 2009
Messages
5
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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.
 

claybwagner

New Member
Joined
Mar 15, 2009
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

New Member
Joined
Mar 15, 2009
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,488
Messages
5,601,974
Members
414,487
Latest member
inxlsplot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top