Forum Discussion

aidanppp's avatar
aidanppp
Copper Contributor
Nov 12, 2022

Im struggling

Hi,

Im trying to drag my mouse across while having my sheet number ascend but the cell number remain constant.

example of what i am trying to achieve

 ='Day(28)'!H3 

='Day(29)'!H3

='Day(30)'!H3

 

 

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 12, 2022

    aidanppp 

    Let's say the first formula ='Day(28)'!H3 is in a cell in row 2, for example in B2.

    Change it to

    =INDIRECT("'Day(" & ROW()+26 & ")'!H3")

    The +26 is used to return 28 from the row number 2. If you start in another row or if you want to begin with Day(1), change the +26 accordingly.

    • aidanppp's avatar
      aidanppp
      Copper Contributor
      Nov 13, 2022

      HansVogelaar 

      hi Hans,

      i tried what you suggested and it does represent the cell value but it doesnt go up in the sheet numbers as i drag to the right.

       

      Here is an example of what I have manually typed in (I have 40000 lines of data to enter, manually typing will take too long)

       

      =indirect(“‘day(“&row()-2&”)’!h3”)

      =indirect(“‘day(“&row()-1&”)’!h3”)

      =indirect(“‘day(“&row()-0&”)’!h3”)

      why will it not automatically do this as I drag to the right??  
      how do i make the -2,-1,+0,+1,+2,+3 change but nothing else?

       

       

       

       

       

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 13, 2022

        aidanppp 

        I thought you wanted to fill or copy downwards.

        If you want to fill or copy to the right, use COLUMN() instead of ROW(), for example

        =INDIRECT("'day(" & COLUMN()-2 & "'!H3")

        A worksheet has "only" 16384 columns, so you won't be able to fill 40000 columns.

Resources