Absolute Rows Question

[Clay_Wagner]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel 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". When I copy the formula, it stays at B$3 all the way down. What am I doing wrong.

Thanks in advance!

 

[macropod]

Hi Clay,

When copying down, only the row reference can change, and then only if the row reference is relative; column references remain
unchanged. The converse is true when copying across.

For what you're after, you could use an OFFSET function. For example, if your data are in B2-P2 on Sheet 1, you could put the
following formula into any cell on row 3 of Sheet 2:
=OFFSET(Sheet1!$B$2,,ROW()-3)
and copy down to row 17, to get the Sheet 1 B2-P2 data transposed into R3-R17 of Sheet2.

 

[JE McGimpsey]

Hi Clay,

When copying down, only the row reference can change, and then only if the
row reference is relative; column references remain
unchanged. The converse is true when copying across.

For what you're after, you could use an OFFSET function. For example, if your
data are in B2-P2 on Sheet 1, you could put the
following formula into any cell on row 3 of Sheet 2:
=OFFSET(Sheet1!$B$2,,ROW()-3)
and copy down to row 17, to get the Sheet 1 B2-P2 data transposed into R3-R17
of Sheet2.

Just as an alternative to this solution, one could use a non-volatile
INDEX() function:

A3: =INDEX(Sheet1!$B$3:$P$3,ROW()-2)