Home 
Search 
Today's Posts 
#1




Transpose Column With Gaps to Row With No Gaps?
How could I convert, by click and dragging a formula, the following?
It's additionally tricky because the Horizontal format includes an end of year lable (2004, 2005, etc), but the vertical format does not Label Cell Reference 4Q04 R17 1Q05 R20 2Q05 R23 3Q05 R26 4Q05 R29 1Q06 R32 Label Cell Reference 4Q04 AG8 2004 AH8 1Q05 AI8 2Q05 AJ8 3Q05 AK8 4Q05 AL8 2005 AM8 1Q06 AN8 Thanks very much in advance. This will become a forecasting tool for debt, reported interest expense, accrued interest expense, capitalized interest expense, and cash interest expense. Regards, SteveC http://www.mavericksocietyinvestmentresearch.com 
#2




This seems to work:
=IF(LEFT(AH8,1)="4","20" & RIGHT(AH8,2), INDEX($R:$R, (COLUMN()  35  COUNTIF($AH$8:AH$8, "20*")) * 3 + 20)) In article , SteveC wrote: How could I convert, by click and dragging a formula, the following? It's additionally tricky because the Horizontal format includes an end of year lable (2004, 2005, etc), but the vertical format does not Label Cell Reference 4Q04 R17 1Q05 R20 2Q05 R23 3Q05 R26 4Q05 R29 1Q06 R32 Label Cell Reference 4Q04 AG8 2004 AH8 1Q05 AI8 2Q05 AJ8 3Q05 AK8 4Q05 AL8 2005 AM8 1Q06 AN8 Thanks very much in advance. This will become a forecasting tool for debt, reported interest expense, accrued interest expense, capitalized interest expense, and cash interest expense. Regards, SteveC http://www.mavericksocietyinvestmentresearch.com 
#3




Thanks JE
When I paste in cell AG8, I get a value of zero, but it should show "4Q04." When I drag to the right one space it it should show (I dont' know), but I get a circular reasoning error. When I drag it to the right two spaces, it should show"1Q05" but I get a circular reasoning error. Any suggestions? Perhaps to clarify, I'm trying to figure out how to transpose: R17 to AG8 R20 to AI8 (skipping AH8) R23 to AI8 R26 to AJ8 R29 to AK8, R32 to AM8 (skipping AL8) and so on... continuing to skip the last of five cells dragged to the right so that the horizontal row, dragged out, looks like this: 4Q04 skipped cell 1Q05 2Q05 3Q05 4Q05 skipped cell 1Q06... Once I've completed dragging the formula over... I suppose I should manually enter the skipped cells with "2004," "2005" and so on. Thanks... I really appreciate it your help Regards, SteveC I've obviously copied and pasted it wrong... I've tried deleting the spacing between operation signs and keeping them as is. Either way I get the same problem. Thanks so much. "JE McGimpsey" wrote: This seems to work: =IF(LEFT(AH8,1)="4","20" & RIGHT(AH8,2), INDEX($R:$R, (COLUMN()  35  COUNTIF($AH$8:AH$8, "20*")) * 3 + 20)) In article , SteveC wrote: How could I convert, by click and dragging a formula, the following? It's additionally tricky because the Horizontal format includes an end of year lable (2004, 2005, etc), but the vertical format does not Label Cell Reference 4Q04 R17 1Q05 R20 2Q05 R23 3Q05 R26 4Q05 R29 1Q06 R32 Label Cell Reference 4Q04 AG8 2004 AH8 1Q05 AI8 2Q05 AJ8 3Q05 AK8 4Q05 AL8 2005 AM8 1Q06 AN8 Thanks very much in advance. This will become a forecasting tool for debt, reported interest expense, accrued interest expense, capitalized interest expense, and cash interest expense. Regards, SteveC http://www.mavericksocietyinvestmentresearch.com 
#4




Actually, I think I have an easier way to explain
how do I drag across so that it automatically fills in cells, as I drag from left to right: Cell1 Cell2 Cell3 =G29, =G32, =G35 
#5




Sorry  I skipped a step:
AH8: =R17 AI8: =IF(LEFT(AH8,1)="4","20" & RIGHT(AH8,2), INDEX($R:$R, (COLUMN()  35  COUNTIF($AH$8:AH$8, "20*")) * 3 + 20)) Drag AI8 across... In article , SteveC wrote: When I paste in cell AG8, I get a value of zero, but it should show "4Q04." When I drag to the right one space it it should show (I dont' know), but I get a circular reasoning error. When I drag it to the right two spaces, it should show"1Q05" but I get a circular reasoning error. Any suggestions? Perhaps to clarify, I'm trying to figure out how to transpose: R17 to AG8 R20 to AI8 (skipping AH8) R23 to AI8 R26 to AJ8 R29 to AK8, R32 to AM8 (skipping AL8) and so on... continuing to skip the last of five cells dragged to the right so that the horizontal row, dragged out, looks like this: 4Q04 skipped cell 1Q05 2Q05 3Q05 4Q05 skipped cell 1Q06... Once I've completed dragging the formula over... I suppose I should manually enter the skipped cells with "2004," "2005" and so on. Thanks... I really appreciate it your help Regards, SteveC I've obviously copied and pasted it wrong... I've tried deleting the spacing between operation signs and keeping them as is. Either way I get the same problem. Thanks so much. "JE McGimpsey" wrote: This seems to work: =IF(LEFT(AH8,1)="4","20" & RIGHT(AH8,2), INDEX($R:$R, (COLUMN()  35  COUNTIF($AH$8:AH$8, "20*")) * 3 + 20)) 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Count Position of Filtered TEXT cells in a column  Excel Worksheet Functions  
Return Count for LAST NonBlank Cell in each Row  Excel Worksheet Functions  
Lookup values in a column and display them in order with no gaps  Excel Worksheet Functions  
Auto Skipping and protected cells  Excel Discussion (Misc queries)  
transpose a column into many rows  Excel Discussion (Misc queries) 