How can I create this formula?!

[BrittWestfall]

Hi There, Trying to create a formula for this instead of manually typing it every time. If I use the "fill" option, it increases the wrong number. I need the first number to increase and the second number to stay constant. Does anyone know how to do this?!

='CO 1'!C17

='CO 2'!C17

='CO 3'!C17

='CO 4'!C17

='CO 5'!C17

[#2]

@BrittWestfall 

You can use the INDIRECT function for this.

Let's say that the first formula is in a cell in row 1. Change it to

=INDIRECT("'CO "&ROW()&"'C17")

And if the formula is in (for example) a cell in row 4, it becomes

=INDIRECT("'CO "&ROW()-3&"'C17")

[#3]

Hi Hans, thank you for your response. I am still having trouble using your suggested formula. Am I typing something wrong? Maybe I should mention that the "CO 1" , "CO 2" and so on are different sheets within this file.

=INDIRECT("'CO 1"&ROW()&"'C17")
=INDIRECT("'CO 2"&ROW()-1&"'C17")
=INDIRECT("'CO 3"&ROW()-2&"'C17")
=INDIRECT("'CO 4"&ROW()-3&"'C17")

[#4]

@BrittWestfall 

Please look at my previous reply again. The "1", "2", "3" etc. are NOT part of the formula that I posted.