Remember Me?

#1
June 17th 05, 06:52 PM
 SteveC Posts: n/a
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

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
June 17th 05, 08:46 PM
 JE McGimpsey Posts: n/a

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

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
June 17th 05, 11:17 PM
 SteveC Posts: n/a

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

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
June 17th 05, 11:29 PM
 SteveC Posts: n/a

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
June 18th 05, 12:22 AM
 JE McGimpsey Posts: n/a

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))

#6
June 18th 05, 01:01 AM
 SteveC Posts: n/a

Works great! Thanks...

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Sam via OfficeKB.com Excel Worksheet Functions 0 May 15th 05 08:14 PM Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM Snaggle22 Excel Worksheet Functions 1 April 12th 05 11:36 PM Dave Peterson Excel Discussion (Misc queries) 6 January 27th 05 11:35 PM GMed Excel Discussion (Misc queries) 1 January 21st 05 07:15 PM

All times are GMT +1. The time now is 12:10 AM.