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