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.

      • aidanppp's avatar
        aidanppp
        Copper Contributor
        Nov 14, 2022
        Hi Hans,
        Thank you for taking the time to help me with the problem, im pretty sure u you have a black belt in excel, your a legend, have a good one!!

Resources