# How to move worksheet reference dynamically in a COUNTIF formula

#### blackcat

##### New Member
Hi,

Can you help me out on this question please?

I'm trying to drag a formula horizontally where only the worksheet reference should change dynamically, while the rest stays locked.

Example here:
=COUNTIF('9FTD'!A:A,A2)
=COUNTIF('10FTD'!A:A,A2)
=COUNTIF('11FTD'!A:A,A2)

Worksheet names are 9FTD, 10FTD and 11FTD.

When I drag the formula horizontally. I would like to tweak this formula =COUNTIF('9FTD'!A:A,A2) into something that locks the part of "A:A,A2" but changes 9FTD into 10FTD, 11FTD, 12FTD and so on.

Thank you for helping.

#### bosco_yip

##### Excel Ninja
Try this drag across right formula :

=COUNTIF(INDIRECT("'"&COLUMN(I\$1)&"FTD'!A:A"),\$A2)

Regards
Bosco

#### blackcat

##### New Member
Thanks Bosco. Can you explain me what column (I\$1) means? Does I1 column reflect something particular?

#### bistroman

##### New Member
that means I1, J1, K1, L1.

1 will be constant so we put \$ before 1.

#### bosco_yip

##### Excel Ninja
Thanks Bosco. Can you explain me what column (I\$1) means? Does I1 column reflect something particular?
=COLUMN(I\$1) return 9, when your drag it across right it will produce 9,10,11,12........ and so on

Regards
Bosco

Last edited: