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

[HNK]

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.

 

[Peo Sjoblom]

Tools>options>calculation, 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]

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]

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 edit>replace and replace = with =


--


Regards,

Peo Sjoblom

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

 

[Gord Dibben]

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

Would Copy>Paste Special>Transpose 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

 

[HNK]

='[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]

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

='[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.

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 edit>replace and replace = with =


--


Regards,

Peo Sjoblom

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