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

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

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

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

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

Works great! Thanks...