| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to Copy and Paste Several Columns of Data into Excel... ? | Excel Discussion (Misc queries) | |||
| combining multiple rows of data into one single row of data | Excel Worksheet Functions | |||
| Macro to Synchronize data frm svrl workbooks & columns to 1 workbo | Excel Discussion (Misc queries) | |||
| Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
| How do i copy columns of data in notepad into microsoft excel? | New Users to Excel | |||
Linear Mode
