Forum Discussion

BrittWestfall's avatar
BrittWestfall
Copper Contributor
Jun 16, 2021

How can I create this formula?!

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

 

excel
Formulas and Functions

3 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 16, 2021

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

     

    • BrittWestfall's avatar
      BrittWestfall
      Copper Contributor
      Jun 16, 2021
      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")

Resources