Use mixed references to show row data as columns in another book
 

In a spreadsheet I am working on, the master data is contained in a
spreadsheet with data reported in rows. A client has asked us for various
reports, but wants the data reported in columns. In order to create a link
to the master spreadsheet, I used a mixed reference, to have the absolute
row, but a relative column. However, when I try to copy this formula
forward, it only copies the same mixed address, rather than advancing the
column as I thought it would.

Use mixed references to show row data as columns in another book
 

Toolsoptionscalculation, set to automatic, that will increase/decrease
relative references when copied meaning

=A$2 copied to the right will change to =B$2


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com


"HNK" wrote in message
...
In a spreadsheet I am working on, the master data is contained in a
spreadsheet with data reported in rows. A client has asked us for various
reports, but wants the data reported in columns. In order to create a
link
to the master spreadsheet, I used a mixed reference, to have the absolute
row, but a relative column. However, when I try to copy this formula
forward, it only copies the same mixed address, rather than advancing the
column as I thought it would.

Use mixed references to show row data as columns in another bo
 

Appreciate teh reply, but my settings were already set as suggested. Even
switching through the various options under tools and options gives no
different results.

"Peo Sjoblom" wrote:

Toolsoptionscalculation, set to automatic, that will increase/decrease
relative references when copied meaning

=A$2 copied to the right will change to =B$2


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com


"HNK" wrote in message
...
In a spreadsheet I am working on, the master data is contained in a
spreadsheet with data reported in rows. A client has asked us for various
reports, but wants the data reported in columns. In order to create a
link
to the master spreadsheet, I used a mixed reference, to have the absolute
row, but a relative column. However, when I try to copy this formula
forward, it only copies the same mixed address, rather than advancing the
column as I thought it would.

Use mixed references to show row data as columns in another bo
 

Post the formula. Also do you see the result of the formula or the formula
itself, if the latter you have text format

Another thing to try, do an editreplace and replace = with =


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"HNK" wrote in message
...
Appreciate teh reply, but my settings were already set as suggested. Even
switching through the various options under tools and options gives no
different results.

"Peo Sjoblom" wrote:

Toolsoptionscalculation, set to automatic, that will increase/decrease
relative references when copied meaning

=A$2 copied to the right will change to =B$2


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com


"HNK" wrote in message
...
In a spreadsheet I am working on, the master data is contained in a
spreadsheet with data reported in rows. A client has asked us for
various
reports, but wants the data reported in columns. In order to create a
link
to the master spreadsheet, I used a mixed reference, to have the
absolute
row, but a relative column. However, when I try to copy this formula
forward, it only copies the same mixed address, rather than advancing
the
column as I thought it would.

Use mixed references to show row data as columns in another book
 

You cannot copy row-oriented formulas down a column just by changing from
relative to absolute.

Would CopyPaste SpecialTranspose work for you?



Enter this in A1 of new sheet and copy down column A to get row 1 from Master
sheet.

=INDEX(Master!$1:$1,ROWS(Master!$1:1))

If you prefer I could post a macro that will Transpose all your rows to columns
and preserve any formulas you may have in the range.


Gord Dibben MS Excel MVP


On Mon, 24 Jul 2006 13:36:02 -0700, HNK wrote:

In a spreadsheet I am working on, the master data is contained in a
spreadsheet with data reported in rows. A client has asked us for various
reports, but wants the data reported in columns. In order to create a link
to the master spreadsheet, I used a mixed reference, to have the absolute
row, but a relative column. However, when I try to copy this formula
forward, it only copies the same mixed address, rather than advancing the
column as I thought it would.

Use mixed references to show row data as columns in another bo
 

='[US Detail.xls]data'!EI$32

that's the formula I'm using. When I copy it down to the next row, in the
formula bar I get the same thing, but in the cells I am getting the result
(the contents of EI32 of the data tab on the US Detail book.

"Peo Sjoblom" wrote:

Post the formula. Also do you see the result of the formula or the formula
itself, if the latter you have text format

Another thing to try, do an editreplace and replace = with =


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"HNK" wrote in message
...
Appreciate teh reply, but my settings were already set as suggested. Even
switching through the various options under tools and options gives no
different results.

"Peo Sjoblom" wrote:

Toolsoptionscalculation, set to automatic, that will increase/decrease
relative references when copied meaning

=A$2 copied to the right will change to =B$2


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com


"HNK" wrote in message
...
In a spreadsheet I am working on, the master data is contained in a
spreadsheet with data reported in rows. A client has asked us for
various
reports, but wants the data reported in columns. In order to create a
link
to the master spreadsheet, I used a mixed reference, to have the
absolute
row, but a relative column. However, when I try to copy this formula
forward, it only copies the same mixed address, rather than advancing
the
column as I thought it would.

Use mixed references to show row data as columns in another bo
 

If you copy down you should get the same result since you are "locking" the
row, remove the dollar sign in front of 32 and put it in front of EI if you
want to lock the column and increase the row numbers when copying down. If
not you can use index and rows to get what you want or offset and rows



--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com




"HNK" wrote in message
...
='[US Detail.xls]data'!EI$32

that's the formula I'm using. When I copy it down to the next row, in the
formula bar I get the same thing, but in the cells I am getting the result
(the contents of EI32 of the data tab on the US Detail book.

"Peo Sjoblom" wrote:

Post the formula. Also do you see the result of the formula or the
formula
itself, if the latter you have text format

Another thing to try, do an editreplace and replace = with =


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"HNK" wrote in message
...
Appreciate teh reply, but my settings were already set as suggested.
Even
switching through the various options under tools and options gives no
different results.

"Peo Sjoblom" wrote:

Toolsoptionscalculation, set to automatic, that will
increase/decrease
relative references when copied meaning

=A$2 copied to the right will change to =B$2


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com


"HNK" wrote in message
...
In a spreadsheet I am working on, the master data is contained in a
spreadsheet with data reported in rows. A client has asked us for
various
reports, but wants the data reported in columns. In order to create
a
link
to the master spreadsheet, I used a mixed reference, to have the
absolute
row, but a relative column. However, when I try to copy this
formula
forward, it only copies the same mixed address, rather than
advancing
the
column as I thought it would.